CODEWITHSUNDEEP
sqlvbaexcel
Jimjebus
Jimjebus

Reputation: 109

SQL Record Count VBA ADODB connection

Trying to work a RecordSet count in VBA Using ADODB recordset but it won't seem to get the count to work properly.

I've got 50 records in a worksheet with unique ID's, some of them are already in a database and some are not, the code itself is to loop through each cell and get the value of the cell and run that through an SQL select statement if theres a hit then say found if not then say nothing.

Unfortunately it seems to return the same result for everything, even the id's i know do not exist.

Code bellow 

Sub NO6_ChequeCheck()

Dim con As ADODB.Connection
Dim rec As ADODB.Recordset

Set con = New ADODB.Connection
Set rec = New ADODB.Recordset


Dim sql As String
Dim client As String

Dim myRange As Range
Dim myCell As Range

Set myRange = Range("A2:A52")

Dim i As Integer
i = 2

With con
    .Provider = "MSDASQL"
    .ConnectionString = "DSN=localhostTest"
    .Open
End With

For Each myCell In myRange

    client = myCell.text

    sql = "SELECT * FROM crm_client_cheques WHERE id = '" & client & "' "

    rec.Open sql, con

    If rec.RecordCount = 0 Then
        Cells(i, "H").Value = "Nothing"
    Else
        Cells(i, "H").Value = "Found"
    End If

    rec.Close

    i = i + 1

Next myCell

The main thing I've come across is that if i toggle that 0 to say 50 and mess with the = sign and change it to < or > then the results will change to either , which leads me to believe its not resetting the recordcount back so it just stacks each time so its always going up each loop and not going back to 0.

i have tried telling it to close and equal nothing and moved various bits of code around but nothing helps much.

All help appriciated

Upvotes: 0

Views: 9747

Answers (1)

george
george

Reputation: 3211

You need to check whether there are any records returned by your sql query. You can do that by

 If Not (rec.BOF And rec.EOF) Then 'There are no records
      Cells(i, "H").Value = "Nothing"
 else
      Cells(i, "H").Value = "Found"
 End if

Upvotes: 3

Related Questions