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

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

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