Determine the RecordCount of a forward-only Recordset

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.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin


The Latest

microsoft careers

Top Careers at Microsoft

Microsoft has gained its position as one of the top companies in the world, and Microsoft careers are flourishing. This multinational company is efficiently developing popular software and computers with other consumer electronics. It is a dream come true for so many people to acquire a high paid, high-prestige job

your company's audio

4 Areas of Your Company Where Your Audio Really Matters

Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a

chrome os developer mode

How to Turn on Chrome OS Developer Mode

Google’s Chrome OS is a popular operating system that is widely used on Chromebooks and other devices. While it is designed to be simple and user-friendly, there are times when users may want to access additional features and functionality. One way to do this is by turning on Chrome OS