When working with a forward-only, read-only Recordset – that is, the default ADO Recordset – the RecordCount property always returns -1. (This can also happen with other types of server-side cursors, depending on the specific OLEDB provider.) In general, you can determine how many records were returned only after visiting the entire Recordset, but often you need this information before processing the Recordset.
Depending on the approximate number of expected records, you can use the GetRows method to retrieve the Recordset’s entire contents, and then use the UBound() function to determine the number of returned rows:
Dim rs As New ADODB.RecordsetDim arr() As VariantDim reccount As Longrs.Open "SELECT * FROM Publishers", "DSN=pubs", , , adCmdText' get all the rows in one shotarr() = rs.GetRows()' now you can determine the number of recordsetreccount = UBound(arr, 2) + 1' continue to process the values, now in arr()' ...In some cases, however, you can’t use this approach. For example, the number of returned rows might be too high (and the arr() array would therefore take too much memory). Or you might be using an updateable cursor (e.g. a dynamic cursor), and you don’t want to read and process all the values twice, once in the GetRows method and once using a MoveNext loop.
In all these cases you should submit two distinct SQL queries to the database, one to determine the number of rows in the Recordset, and the next one to retreive the actual rows:
Dim cn As New ADODB.Connection, rs As New ADODB.RecordsetDim reccount As Longcn.Open "DSN=pubs"' first, retrieve the number of recordsrs.Open "SELECT COUNT(*) FROM publishers", cn, , , adCmdText' the returned Recordset has one row with one fieldreccount = rs(0)rs.Close' then retrieve the actual rowsrs.Open "SELECT * FROM Publishers", cn, , , adCmdText' ....If you’re working with SQL Server or another database engine that supports multiple SQL statements in a query, you can optimize your code by submitting one single query, as in:
Dim rs As New ADODB.RecordsetDim reccount As Long, sql As Stringsql = "SELECT COUNT(*) FROM publishers;" & "SELECT * FROM publishers"rs.Open sql, "DNS=pubs", , , adCmdText' the first returned Recordset contains the COUNT(*) valuereccount = rs(0)' the second Recordset contains the actual rowsSet rs = rs.NextRecordset' ...This version is faster because it requires only one trip to the server.
