Check if a record with a specific ID exists

[Phantom1]

I want to check if a record with ID 1 exists.

rs.Open "SELECT * FROM tblTransaction WHERE TransactionID = 1", DBConn

There are no records with ID 1. rs.Fields.Count returns 9. How can it?

[Doogle]

rs.Fields.Count will return the number of Columns in the Table.

rs.RecordCount may return the number of rows selected.

[si_the_geek]

Thread moved to the 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)

In addition to what Doogle said, if you just want to check if any records exist then rs.RecordCount is a bad idea (it is slow, and as implied above may not return the amount), instead you should check rs.EOF (if it is True there are record(s), if it is False there aren't).

[BobRodes]

Checking both .EOF and .BOF is a little more thorough, because there is never a situation in which that can arise except an empty recordset. So:

With rs
   .Open 'blah blah
   If .EOF And .BOF Then
       'It's empty
   Else
       'It isn't
   End If
End With

Also, .RecordCount isn't always accurate, depending on the CursorType you use.