Determine the RecordCount of a forward-only Recordset

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:
Heading photo, Metadata.

What is Metadata?

What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular

XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several

data fivetran pricing

Fivetran Pricing Explained

One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of

kubernetes logging

Kubernetes Logging: What You Need to Know

Kubernetes from Google is one of the most popular open-source and free container management solutions made to make managing and deploying applications easier. It has a solid architecture that makes

ransomware cyber attack

Why Is Ransomware Such a Major Threat?

One of the most significant cyber threats faced by modern organizations is a ransomware attack. Ransomware attacks have grown in both sophistication and frequency over the past few years, forcing