Binaryworld - Determine the RecordCount of a forward-only Recordset ... [ VB -> ADO ]

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:

Click here to copy the following block

Dim rs As New ADODB.Recordset
Dim arr() As Variant
Dim reccount As Long

rs.Open "SELECT * FROM Publishers", "DSN=pubs", , , adCmdText
' get all the rows in one shot
arr() = rs.GetRows()
' now you can determine the number of recordset
reccount = 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:

Click here to copy the following block

Dim cn As New ADODB.Connection, rs As New ADODB.Recordset
Dim reccount As Long

cn.Open "DSN=pubs"
' first, retrieve the number of records
rs.Open "SELECT COUNT(*) FROM publishers", cn, , , adCmdText
' the returned Recordset has one row with one field
reccount = rs(0)
rs.Close
' then retrieve the actual rows
rs.Open "SELECT * FROM Publishers", cn, , , adCmdText
' ....

If '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: you
Dim rs As New ADODB.Recordset
Dim reccount As Long, sql As String

sql = "SELECT COUNT(*) FROM publishers;" & "SELECT * FROM publishers"
rs.Open sql, "DNS=pubs", , , adCmdText
' the first returned Recordset contains the COUNT(*) value
reccount = rs(0)
' the second Recordset contains the actual rows
Set rs = rs.NextRecordset
' ...

This version is faster because it requires only one trip to the server.