I am new to Vba and trying to pull data records from SQL table if it matches to the cell value in Spreadsheet. The record should be printed in another tab if it’s matched.
I have wrote the below code, it only prints the last row of matched record but not the whole .
Dim oConn As ADODB.Connection Dim rs As ADODB.Recordset Dim fld As ADODB.Field Sub CheckRecords() Set rs = New ADODB.Recordset Set oConn = New ADODB.Connection oConn.ConnectionString = "Provider=SQLOLEDB;" & _ "Server=Myserver;" & _ "authenticateduser = True; database=Db1;" & _ "User ID=Userid;Password=password;" oConn.Open ConnectionString Dim rowtable As Long 'number of row in the worksheet Dim strSQL As String 'SQL string Dim stringa As String 'the record in the worksheet that I want to check Dim row As Integer Dim Col As Integer Dim height As Integer Dim ws As ThisWorkbook Set ws = ThisWorkbook Application.ScreenUpdating = False 'count the number of rows with records rowtable = Cells(Rows.Count, "B").End(xlUp).row For height = 3 To rowtable 'variable stringa = the record of my worksheet that I want to check into DB table stringa = Sheet4.Cells(height, 2).Value strSQL = "SELECT member, code, list, update_date from dbtable where member = '" & stringa & "'" rs.Open strSQL, oConn row = 2 Do While Not rs.EOF Col = 1 For Each fld In rs.Fields Sheet13.Cells(row, Col).Value = fld Col = Col 1 Next row = row 1 rs.MoveNext Loop rs.Close Next oConn.Close End Sub
I am missing something in loop, as it prints 1 record that has matched but just cannot figure out. Can somebody help me out?