excel - 记录集始终返回-1(excel VBA)

Question

我想计算记录集中查询返回的行数，我尝试了 recset.RecordCount 函数，但它总是返回 -1 值。

如何计算记录集中的记录数或行数？

Answer 1

在连接对象中指定参数很重要:CursorLocation = adUseClient。

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 

完整的工作示例在这里: http://straightitsolutions.blogspot.com/2014/12/read-recordcount-from-adodbrecordset.html