Recordset always returns -1 (excel VBA)

Question

I want to count the number of rows returned by a query in a recordset, I tried recset.RecordCount function but it always return a value of -1.

How am I going to count the number of records or rows in recordset?

Answer 1

have you tried moving to the last before checking for count

recset.MoveLast

also see if this helps

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.

Check this question:

VB6 ADODB.Recordset RecordCount property always returns -1

Answer 2

If I remember correctly a recordset count isn't populated until you move to the end. I believe (dredging my memory here) that it is something like

MyRecordSet.MoveLast
MyRecordSet.MoveFirst

Then your count should be populated

Answer 3

can you try adding this:

objRS.CursorLocation = adUseClient
objRS.Open strSQL, objConn,,adLockReadOnly, adCmdText

The Cursor position is important.

Hope this helps..

Answer 4

It is important to specify parameter: CursorLocation = adUseClient in connection object.

dbName = "DbInjectorsCatalog"
dbFilePath = "C:\DbInjectorsCatalog.mdf"
 
connStr = "Driver={SQL Server native Client 11.0};" & _
          "Server=(LocalDB)\v11.0;" & _
          "AttachDBFileName=" & dbFilePath & ";" & _
          "Database=" & dbName & ";" & _
          "Trusted_Connection=Yes"
sqlStr = "Select * from Injectors"
 
Set conn = New ADODB.Connection
conn.ConnectionString = connStr

'>>> scpecify cursor location
conn.CursorLocation = adUseClient

conn.Open
Set rs = New ADODB.Recordset
rs.Open sqlStr, connStr, adOpenStatic, adLockBatchOptimistic 

Full working example is here: http://straightitsolutions.blogspot.com/2014/12/read-recordcount-from-adodbrecordset.html