CODEWITHSUNDEEP
excelms-access-2010vba
Kentot
Kentot

Reputation: 542

Recordset always returns -1 (excel VBA)

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?

Upvotes: 10

Views: 15159

Answers (4)

Marcin Własny
Marcin Własny

Reputation: 51

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

Upvotes: 4

gaurav5430
gaurav5430

Reputation: 13902

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

Upvotes: 2

Andrew
Andrew

Reputation: 2335

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

Upvotes: 1

Sai Avinash
Sai Avinash

Reputation: 4753

can you try adding this:

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

The Cursor position is important.

Hope this helps..

Upvotes: 1

Related Questions