The rowset does not support fetching backwards

[MightyCat]

I've found the same answer all over the place but I can't implement the suggestions and have my code work. Can anyone see what I'm doing wrong? I really just want to get the recordcount of the recordset to display on my form.

Public Sub CallSP()
    Dim cnn As ADODB.Connection
    Dim rsSQL As ADODB.Recordset
    Dim CmdSQL As ADODB.Command
    
    Dim rsACCESS As ADODB.Recordset
    
    Set cnn = New ADODB.Connection
    
    Dim tName As String
    
    tName = "tTABLE"
    
    
    cnn.ConnectionString = gSQLConnection
      
    cnn.Open
    Set CmdSQL = New ADODB.Command
    CmdSQL.ActiveConnection = cnn
    
    With CmdSQL
        
    .CommandText = "[SP_STOREDPROC]"
    .CommandType = adCmdStoredProc
    .Parameters("@inCompany").Value = "PARAM1"
    .Parameters("@CNStatus").Value = "PARAM2"
    
    
   End With
    
    
    Set rsSQL = New ADODB.Recordset
    
    rsSQL.Open CmdSQL, , adOpenKeyset, adLockOptimistic
    rsSQL.MoveLast
 
    rsSQL.Close
    Set rsSQL = Nothing
    cnn.Close
    Set cnn = Nothing

What am I missing????

Thanks All!

 

[jdraw]

You aren't asking for a record count.
Maybe debug.print rsSQL.RecordCount

from M$oft
The cursor type of the Recordset object affects whether the number of records can be determined. The RecordCount property will return -1 for a forward-only cursor; the actual count for a static or keyset cursor; and either -1 or the actual count for a dynamic cursor, depending on the data source.

 

[MightyCat]

That doesn't work. I get a -1 which got me searching for an answer.

I found that the cursor is defaulted to forward only therefore, I needed to change my cursor type. But, it still doesn't work.

 

[jdraw]

Not sure why your recordset would be defaulted -- you specified.
rsSQL.Open CmdSQL, , adOpenKeyset, adLockOptimistic

Did you try the debug.print?
Did you try adOpenDynamic as cursor type?

 

[MightyCat]

I actually did. I tried a whole bunch of variations.

It's really weird...I can't figure it out

 

[jdraw]

Is there something special with your stored procedure that would prevent any movement??

What exactly is the recordset vs the tName/ttable?

Have you tried to iterate the recordset and do a physical count?

Do while not rsSQL.eof
i = i +1
rsSQL.movenext
Loop

 

[jdraw]

Try this
Set rsSQL = New ADODB.Recordset

rsSQL.Open CmdSQL, , adOpenStatic, adLockOptimistic
MsgBox rsSQL.RecordCount

Taken From
http://www.ozgrid.com/forum/showthread.php?t=82309

 

[MightyCat]

Thank you for the suggestions.

I already set the recordset to New.ADODB.

I can iterate through the recordset b/c I write it out to a local table. But, I added the loop code provided which it did fine, but when I then tried to rst.MoveFirst, I got 'rowset position cannot be restarted'.

I'm so stumped. I used the below:

Set rsSQL = New ADODB.Recordset

rsSQL.Open CmdSQL, , adOpenKeyset, adLockOptimistic

Do While Not rst.EOF
TotalRec = TotalRec + 1
rst.MoveNext
Loop

rst.MoveFirst