Doesn't find existing symbol in table

[CarlRostron]

trtying to query the Symbol table to see if a recod exists with symbol code.

I am querying the tblSymbol table from Excelk vba and the Access DB is on my machine.

The code I am using is:

  Set rs = New ADODB.Recordset
  rs.Open "tblSymbol", cn, adOpenKeyset, adLockOptimistic, adCmdTable
  
  Set rs2 = New ADODB.Recordset
  sql = "SELECT * FROM [tblSymbol] WHERE [SymbolCode] = """ & someSymbol & """"
  rs2.Open sql, cn, adOpenDynamic, adLockOptimistic
  
  Exists = rs2.RecordCount
  MsgBox Exists
  MsgBox sql
  If (rs2.RecordCount >= 0) Then
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
  
    rs2.Close
    Set rs2 = Nothing
    Exit Sub
  End If

MsgBox Exists always returns -1 but i know the code exists in the table.

 

[namliam]

why not do a count(*) and return that?

In (DAO) recordsets you need to do MoveLast to be able to get an accurate Count of the recordset, not sure if the same is true of ADO

 

[CarlRostron]

Didn't realise I could do a Count(*) (I'm still learning) but yep I can code that instead makes more sense doesn't it!!

Thanks for your comment

BTW I did solve it before your reply by dong a Do While loop and counting the records instead but that's very convoluted method. Yours is a much more elegant approach.

Thanks again

 

[namliam]

Doing the loop is no different than the MoveLast and then doing the recordcount.

ANother alternative might be DCount (I Believe)

 

[CarlRostron]

Is there a way I can make the connection to the database and wrap it up into a database object similar to the way it is done for VBA in access? My thoughts were to somehow get to dB.openrecordset(......

Where
Dim dB as database
Set dB = currentdb

 

[namliam]

Yes however your suggestion is a DAO way instead of a ADO way of doing things.
Which set is activated depends on your version of access but you can (de)activate the references yourself if you like too.

No need to define a db variable though, you can also just do Currentdb.openrecordset