Does your Recordset return -1 for its RecordCount property? If yes, this article will be able to help you and explain the cause and fixes.

If you have used Microsoft ADO in your VBA project, you must have used Recordset object. Recordset is just a temporary table in memory that contains rows and columns of data. You can navigate through the recordset and do whatever processing you are legit to do.

Recordset object has a RecordCount property that indicates how many records in the Recordset. When I first used ADO in 1999, it had puzzled me for over a year why the RecordCount always returned -1. After many tests and digs into the Help document, I realized the culprit was the type of cursor I used for the record set.

By default, ADO implicitly creates a forward only cursor for a recordset and returns -1 in the RecordCount property when one of the following methods is used.

1. When opening the recordset object by using the Open method of the Recordset object if no cursor type is specified.
2. When obtaining the Recordset object by using the Execute method of the Connection object. This method does not give us the option to specify cursor type.

The first two code examples below demonstrate how to create recordset with forward only cursor. Then two more examples are given to show how to create recordsets that return the valid record count.

To get the code on this page to work, you need to add a reference to Microsoft ActiveX Data Objects Library (any version from 2.0 to 2.8) in Northwind database.

In VBA editor, click Tools -> References...

How to create Recordset that returns -1 on the RecordCount property

The following two code snippets implicitly create forward only cursor and will return -1 on the RecordCount property.

To see how the code below works, in Access Northwind database, create a form with two buttons and name them as cmdCnnExecute and cmdRstOpen. Copy the code into their relevant On Click event procedure.

(1) Use Execute method of the Connection object - RecordCount returns -1.

The Execute method returns a recordset with forward only cursor type.

Private Sub cmdCnnExecute_Click()
    On Error Goto Catch

    Dim strSql As String
    Dim objCnn As ADODB.Connection
    Dim objRst As ADODB.Recordset
    
    '' Get all categories
    strSql = "select CategoryID, CategoryName from Categories"
    
    Set objCnn = CurrentProject.Connection
    Set objRst = New ADODB.Recordset
    
    '' This implicitly creates a recordset object with forward only cursor by default.
    Set objRst = objCnn.Execute(strSql)

    '' Display the number of records in Immediate window. Returns -1    
    Debug.Print "objRst (adOpenForwardOnly) record count: " & objRst.RecordCount
                
    '' Clean up
    Set objCnn = Nothing    
    objRst.Close
    Set objRst = Nothing
        
    Exit Sub
    
Catch:
    MsgBox "cmdRecordCount_Click(): " & vbCrLf & vbCrLf _
            & "Error#:  " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub

(2) Use Open method of the Recordset object with default cursor type - RecordCount returns -1.

In this example, the Open method creates a recordset of a forward only cursor type. This is because the cursor type argument is omitted in the method, so the default argument adOpenForwardOnly is used.

Private Sub cmdRstOpen_Click()
    On Error Goto Catch

    Dim strSql As String
    Dim objRst As ADODB.Recordset
    
    '' Get all categories
    strSql = "select CategoryID, CategoryName from Categories"
        
    Set objRst = New ADODB.Recordset
    
    '' This creates a recordset object with forward only cursor by default because
    '' we didn't specify the cursor type enum.
    objRst.Open strSql, CurrentProject.Connection
            
    '' Display the number of records in Immediate window. 
    '' Returns -1
    Debug.Print "objRst (adOpenForwardOnly) record count: " & objRst.RecordCount

    '' Clean up
    objRst.Close
    Set objRst = Nothing
        
    Exit Sub
    
Catch:
    MsgBox "cmdRecordCount_Click(): " & vbCrLf & vbCrLf _
           & "Error#:  " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub

The ways to obtain a valid count

Now we're going to look at how to get the correct count by using RecordCount property.

(1) Client-side cursor

When a recordset's cursor location is specified as client-side, it always returns the valid count for RecordCount property.

Client-side cursor is specified in VBA as below.

objRst.CursorLocation = adUseClient

When a recordset is client-side, after it's retrieved from the database server (eg. Oracle, MySQL database server, etc.), it's sent to the client tier (eg. your workstation PC for MS Access application, or web server for an internet application) and made disconnected from the database server, so it's static in nature. Any changes in the database after the retrieval won't affect the data in the recordset.

Please note that, for local Access database, the server and client are on the same machine because server is your Access Jet engine and client is your VBA engine.

(2) Server-side Static or Keyset cursor.

When a recordset is opened, by default it's opened as a server-side, forward only cursor. If we change its cursor type to static or keyset cursor, it will return the valid count for RecordCount property.

The following code opens a static cursor.

objRst.CursorType = adOpenStatic

'' Or specify adOpenStatic in the Open method directly.
objRst.Open strSql, CurrentProject.Connection, adOpenStatic

The following code opens a keyset cursor.

objRst.CursorType = adOpenKeyset

'' Or specify adOpenKeyset in the Open method directly.
objRst.Open strSql, CurrentProject.Connection, adOpenKeyset

To understand more about the pros and cons for client-side and server-side cursor, refer to Microsoft article Client-Side Cursors Versus Server-Side Cursors.

Example of client-side cursor that returns actual count

Code below shows how a client-side cursor returns the actual record count. In Northwind database, on the form you are using for testing, create a new button named cmdRecordCountClientSide, then copy the code below in its On Click event.

Private Sub cmdRecordCountClientSide_Click()
    On Error Goto Catch

    Dim strSql As String
    Dim objRst As ADODB.Recordset
    
    '' Get all categories from local Northwind database.
    strSql = "select CategoryID, CategoryName from Categories"
    
    Set objRst = New ADODB.Recordset

    '' Client-side cursor
    objRst.CursorLocation = adUseClient
    
    objRst.Open strSql, CurrentProject.Connection
    
    '' Returns the actual count (8 records)
    Debug.Print "Client-side objRst record count: " & objRst.RecordCount

    '' Clean up
    objRst.Close
    Set objRst = Nothing
        
    Exit Sub
    
Catch:
    MsgBox "cmdRecordCount_Click()" & vbCrLf & vbCrLf _
           & "Error#:  " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub

Immediate Window shows the actual count for client-side cursor.

Example of all four server-side cursor types

Code below shows four recordsets with four different cursor types. Static and keyset cursor return the actual count. Forward only and dynamic cursor return -1. Because we didn't specify cursor location, it defaults to server-side. On the other hand, because the records are retrieved from local Access Northwind database, the client-side and server-side are both on the local computer.

In Northwind database, on the form you are using for testing, create a new button named cmdRecordCount, then copy the code below in its On Click event.

Private Sub cmdRecordCount_Click()
    On Error Goto Catch

    Dim strSql As String
    Dim objRst1 As ADODB.Recordset
    Dim objRst2 As ADODB.Recordset
    Dim objRst3 As ADODB.Recordset
    Dim objRst4 As ADODB.Recordset

    '' Get all categories from local Northwind database.
    strSql = "select CategoryID, CategoryName from Categories"
    
    '' In code below, because we didn't specify cursor location, it's server-side by default.
    
    '' Returns -1
    Set objRst1 = New ADODB.Recordset
    objRst1.Open strSql, CurrentProject.Connection, adOpenForwardOnly

    '' Returns -1
    Set objRst2 = New ADODB.Recordset
    objRst2.Open strSql, CurrentProject.Connection, adOpenDynamic

    '' Returns valid count
    Set objRst3 = New ADODB.Recordset
    objRst3.Open strSql, CurrentProject.Connection, adOpenKeyset

    '' Returns valid count
    Set objRst4 = New ADODB.Recordset
    objRst4.Open strSql, CurrentProject.Connection, adOpenStatic
        
    '' Display the number of records in Immediate window.
    Debug.Print "objRst1 (adOpenForwardOnly) record count: " & objRst1.RecordCount
    Debug.Print "objRst2 (adOpenDynamic) record count: " & objRst2.RecordCount
    Debug.Print "objRst3 (adOpenKeyset) record count: " & objRst3.RecordCount
    Debug.Print "objRst4 (adOpenStatic) record count: " & objRst4.RecordCount

    '' Clean up
    objRst1.Close
    objRst2.Close
    objRst3.Close
    objRst4.Close
    Set objRst1 = Nothing
    Set objRst2 = Nothing
    Set objRst3 = Nothing
    Set objRst4 = Nothing
        
    Exit Sub
    
Catch:
    MsgBox "cmdRecordCount_Click()" & vbCrLf & vbCrLf _
           & "Error#:  " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub

Immediate Window shows four values.

Please note:

(1) If you're (in rare situations) using a static cursor or keyset cursor but still get -1, specifying lock type as adLockOptimistic may fix your problem.

(2) If a dynamic cursor is specified, we may get either -1 or the actual count depending on the circumstances, but forward only cursor always returns -1.

Happy Coding!


Copyright© GeeksEngine.com