(Solved) – Check if multiple records of a worksheet exist in a SQL table using Excel Vba

  • by

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


 row = row   1



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?

Leave a Reply

Your email address will not be published. Required fields are marked *