ADODB Unexpected Results

[psycoperl]

I am getting SID passed to my subroutine. [According to Debug.Print TypeName(SID) it is datatype long]

In my test I am using SID values of 130 and 131
I know that there is data in the table that will return a single row.
I even tested the query in Access using and got the row.
I had even hard coded the value into the ts.Open statement and it does not show the row it executes the if ts.bof=ts.eof line which stops processing the subroutine.

In the table tsTestSessionID is an autonumber.

Can not figure out what is going on as every other test it works.

    Dim ts As ADODB.Recordset
    Set ts = New ADODB.Recordset
    ts.ActiveConnection = CurrentProject.Connection
    ts.LockType = adLockReadOnly
    ts.CursorType = adOpenDynamic
    ts.Open "Select * from tblTestSessions Where tsTestSessionID =" & SID
    If Not CK.BOF Then
     ts.MoveFirst
     End If
    If ts.BOF = ts.EOF Then
        MsgBox "Test Session Not Found, Processing Stop", vbCritical, "Error!"
        ts.Close
        Set ts = Nothing
        Exit Sub
     End If 
   ts.Close
   Set ts = Nothing

 

[boblarson]

Okay, first off -

When opening a new recordset. It is ALWAYS at the beginning so you don't need to movefirst. Second, use either of these instead of your current one:

If ts.BOF AND ts.EOF Then

or just use

If ts.RecordCount = 0 Then

The ADODB recordset's RecordCount will either be 0 if there aren't records returned or 1 if there are records.

 

[psycoperl]

Thanks Bob that seems to have worked.

Dont remember where I saw that you needed to make sure it was at the first record... Programming in VBA and with Access is a new skill set that I am developing. I have a stack of books to help.

I took a course in ultra basic VB6 back in college way back in 2001. Havent done much more than simple access querys since.

Thanks again for your help.

 

[boblarson]

You may be thinking of MoveLast where you have to move to the last record and back to the first to get the actual count of the records.