ADO - ADO Recordset RecordCount and Its Limitations

The RecordCount property in ADO (ActiveX Data Objects) is used to determine the number of records contained in a Recordset. It is particularly useful when an application needs to know how many rows were returned by a database query. The property is accessed through the Recordset object using rs.RecordCount, where rs represents the Recordset. However, RecordCount does not always return the actual number of records. Its behavior depends largely on the type of Recordset, the cursor type, and the capabilities of the underlying database provider.

1. What Is the RecordCount Property?

When a query retrieves multiple rows from a database, ADO stores the returned data in a Recordset. The RecordCount property tells the application how many records ADO currently recognizes in that Recordset.

A simple example is:

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

rs.Open "SELECT * FROM Employees", conn

MsgBox rs.RecordCount

If the Recordset contains 50 records and the cursor/provider supports accurate record counting, rs.RecordCount can return 50.

However, developers should not assume that RecordCount will always immediately provide the exact number of records.

2. Why RecordCount May Return -1

One of the most important limitations of RecordCount is that it can return -1.

For example:

If rs.RecordCount = -1 Then
    MsgBox "The number of records cannot currently be determined."
Else
    MsgBox "Number of records: " & rs.RecordCount
End If

A value of -1 generally means that ADO cannot determine the number of records available in the Recordset using the current cursor/provider configuration.

This does not necessarily mean that the query returned no records. It means that the exact number of records is not currently available through the RecordCount property.

For example:

Recordset contains records
        |
        v
Can the cursor/provider determine the count?
        |
   +----+----+
   |         |
  Yes        No
   |         |
   v         v
RecordCount  -1

This distinction is important because a developer might incorrectly interpret -1 as zero records.

3. Cursor Type Affects RecordCount

The behavior of RecordCount is strongly influenced by the cursor type used by the Recordset.

ADO supports different cursor types, including:

  • adOpenForwardOnly

  • adOpenKeyset

  • adOpenDynamic

  • adOpenStatic

A forward-only cursor is designed primarily for moving through records from beginning to end. Because it does not necessarily maintain enough information to determine the complete number of records, RecordCount may return -1.

For example:

rs.CursorType = adOpenForwardOnly

With this cursor type, an application should not automatically assume that:

rs.RecordCount

will return an accurate count.

A static cursor generally provides better support for determining the number of records because the cursor maintains a more complete representation of the result set.

4. Provider Support Is Important

ADO itself provides the RecordCount property, but the actual database provider has an important role in determining whether the count can be calculated.

Different providers may implement cursor functionality differently. Therefore, the same ADO code can behave differently when connected to different database systems or providers.

For example, an application might work with:

Application
     |
     v
ADO
     |
     v
OLE DB Provider
     |
     v
Database

If the provider does not support the required cursor functionality, ADO may be unable to provide an accurate RecordCount.

This means that developers should consider both the ADO configuration and the capabilities of the provider.

5. RecordCount Does Not Always Mean Database Row Count

Another important limitation is that RecordCount describes the records currently represented by the Recordset. It should not automatically be interpreted as the total number of rows in the underlying database table.

Suppose a table contains 10,000 employees:

SELECT * FROM Employees

The Recordset might represent all 10,000 rows, depending on the query and cursor.

But if the query contains a condition:

SELECT * FROM Employees
WHERE Department = 'Sales'

RecordCount represents the number of rows returned by that query, not the total number of employees in the table.

For example:

Employees table       10,000 rows
        |
        v
WHERE Department = Sales
        |
        v
Recordset              1,250 rows
        |
        v
RecordCount             1,250

Therefore, RecordCount should be understood as the count of records in the Recordset rather than a universal count of database records.

6. RecordCount and Empty Recordsets

When a query returns no records, developers need to distinguish an empty Recordset from a Recordset whose count is unknown.

A common check is:

If rs.EOF And rs.BOF Then
    MsgBox "No records found."
End If

This is often more reliable for determining whether a Recordset is empty than simply checking:

If rs.RecordCount = 0 Then

Why?

Because RecordCount might be -1 when the provider or cursor cannot determine the number of records.

For example:

RecordCount = 0

usually indicates that ADO knows the Recordset contains no records.

But:

RecordCount = -1

does not necessarily mean that the Recordset is empty. It can mean that the count cannot be determined.

7. Moving Through the Recordset May Affect the Count

With some cursor configurations, ADO may not know the complete number of records immediately. As the application processes the Recordset, more information may become available.

For example:

Do Until rs.EOF
    ' Process current record
    rs.MoveNext
Loop

With certain cursor types, the provider may progressively retrieve records. Consequently, developers should be careful about relying on RecordCount before the Recordset has been fully populated or when using forward-only retrieval.

This is particularly relevant when working with large result sets.

8. RecordCount and Large Result Sets

RecordCount can also have performance implications depending on how the provider obtains the information.

Suppose a query returns several million records. Determining the exact number of records may require the provider to process or maintain information about the complete result set.

Therefore, simply requesting a count through a Recordset is not always the most efficient solution.

Instead of:

rs.Open "SELECT * FROM Orders", conn

total = rs.RecordCount

an application that only needs the number of rows may be better served by a database-level aggregate query:

SELECT COUNT(*) FROM Orders

The database engine is specifically designed to perform aggregate operations efficiently, whereas opening a large Recordset merely to determine its RecordCount can involve unnecessary data retrieval.

9. RecordCount vs. SQL COUNT()

These two approaches serve different purposes.

Using:

rs.RecordCount

means:

Execute query
       |
       v
Create Recordset
       |
       v
Determine number of records

Using:

SELECT COUNT(*) FROM Employees

means:

Execute COUNT operation
       |
       v
Database calculates count
       |
       v
Return one value

If the application only needs the number of records, COUNT(*) is generally the more appropriate database operation.

For example:

SELECT COUNT(*) AS TotalEmployees
FROM Employees;

The result might be:

TotalEmployees
--------------
2500

This avoids transferring an entire collection of employee records when the application only needs a number.

10. RecordCount with Filtered Recordsets

ADO Recordsets can be filtered. When a filter is applied, the records visible through the Recordset can change.

For example:

rs.Filter = "Department = 'Sales'"

The application may then use RecordCount to determine the number of records currently matching the filter.

However, developers must understand that the resulting count relates to the Recordset's current view rather than necessarily representing the total rows in the original table.

This becomes important when applications dynamically change filters and then use RecordCount for calculations or display.

11. RecordCount Is Cursor-Dependent

A major lesson when using RecordCount is that there is no universal rule such as:

RecordCount always equals the number of rows.

A more accurate model is:

RecordCount result
       =
Cursor configuration
       +
Provider capabilities
       +
Recordset state
       +
Query/result set

Therefore, code that relies heavily on RecordCount should be designed with the specific ADO provider and cursor configuration in mind.

12. Example of Safer Usage

A practical approach is to check whether the Recordset is empty and then determine whether the count is available.

If rs.EOF And rs.BOF Then

    MsgBox "No records found."

ElseIf rs.RecordCount = -1 Then

    MsgBox "Records exist, but the total count is unavailable."

Else

    MsgBox "Total records: " & rs.RecordCount

End If

This approach avoids treating -1 as though it means zero.

13. Common Mistakes

A common mistake is writing:

If rs.RecordCount > 0 Then
    ' Records exist
End If

This can be problematic when RecordCount is -1, because the application has not actually established the total number of records.

Another mistake is:

If rs.RecordCount = 0 Then
    ' No records
End If

This assumes that ADO can always determine the count. With some cursor/provider combinations, the count may instead be -1.

A third mistake is using RecordCount to count rows in a database table when the Recordset query contains filters, joins, grouping, or other operations. In such cases, the RecordCount describes the resulting Recordset, not necessarily the underlying table.

14. Best Practices

When using RecordCount in ADO applications, developers should follow these practices:

  1. Do not assume RecordCount is always accurate.

  2. Treat -1 as an indication that the count is unavailable, not as zero.

  3. Understand the cursor type being used.

  4. Consider the capabilities of the database provider.

  5. Use EOF and BOF when checking whether a Recordset is empty.

  6. Use SQL COUNT(*) when the application only needs a database row count.

  7. Avoid opening very large Recordsets solely to determine their size.

  8. Remember that RecordCount applies to the current Recordset/result, including applicable filtering.

  9. Test RecordCount behavior with the actual provider used by the application.

  10. Do not build critical application logic around an assumption that RecordCount always returns a positive number.

Conclusion

The RecordCount property in ADO provides a convenient way to determine the number of records in a Recordset, but it has important limitations. Its result depends on the cursor type, provider capabilities, Recordset state, and query. In particular, a value of -1 indicates that ADO cannot currently determine the record count and should not be interpreted as an empty Recordset.

For simple applications and supported cursor configurations, RecordCount can be very useful. However, when an application needs an authoritative database-level count, especially for large datasets, using a SQL query such as SELECT COUNT(*) is generally a better approach. Understanding these limitations helps developers write ADO applications that are more reliable, efficient, and portable across different database providers.