SQL Record Count VBA ADODB connection

Question

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

This link might help you: stackoverflow.com/questions/2282661/… — cyboashu
– cyboashu, Commented Aug 26, 2014 at 15:34
is it the same when you remove the single quotes from around the client variable — user2140173
– user2140173, Commented Aug 26, 2014 at 15:38
In addition to cyboashus answer, see allenbrowne.com/ser-29.html (Trap 4). — MP24
– MP24, Commented Aug 26, 2014 at 15:40
The count method only works once you have looped through all the data. See the answer below from user3793865 about .bof and .eof that's the correct way to do it. — user1644564
– user1644564, Commented Aug 27, 2014 at 9:29
And your code is going to be very slow if you repeatedly keep querying a recordset inside that loop. Why don't you query the recordset before you loop through the cells and then search the recordset for the value inside the loop. — user1644564
– user1644564, Commented Aug 27, 2014 at 9:31

george · Accepted Answer · 2014-08-26 15:48:46Z

3

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

answered Aug 26, 2014 at 15:48

george

3,2415 gold badges37 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Record Count VBA ADODB connection

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related