In my VBA projects, I occasionally need to know the number of records in the recordset that I have obtained from either the local Access database or remote database server such as MySQL, Oracle, or SAS data mart on the network.

When I look back at how I did it in the past few years, I can sum it up into three ways about how to get the number of records in an Access ADO recordset object.

1. Using RecordCount property of Recordset object.
2. Using UBound function for the array returned by GetRows method of a Recordset object.
3. Using SQL select statement to query the number of records.

If you are familiar with Microsoft ADO, you should be able to easily browse through and understand the code examples in this article. If not, I also give brief explanations. The best way I reckon is copy the code into your Northwind Access database and see how they work.

To test the code in your Access database, create a new Form and add three buttons. In the OnClick event of each button, copy each code example into its sub-routine and run it. Record count will be displayed in Immediate Window.

Don't forget to add a reference to ADO library. In VBA editor, click Tools -> References... to add the reference to Microsoft ActiveX Data Objects Library (any version from 2.0 to 2.8).

1. Using RecordCount property of Recordset object.

Using RecordCount property is a common way to get the number of records from Recordset object, but it's tricky sometimes if you don't know how to use it the right way. There are a lot to be explained about it so I put it in a separate article. Click here to see that article.

2. Using UBound function for the array returned by GetRows method of a Recordset object.

This is another elegant way to get the number of records. Advantages of using GetRows method are that (1) Faster (2) Less resource intensive (3) Flexible because array can be easily reused (step through it again, add new element, etc.) at any time with no need to requery the recordset.

For details about GetRows Method, refer to Microsoft article here.

GetRows method turns a recordset into a two-dimensional array which is basically a temporary table in the memory of the calling client (which is either the same computer where the database resides or another computer), so it is static in nature. That is, if you retrieve data from a database server on the network, the recordset is sent to your local computer and then is converted into a two-dimensional array by VBA engine. Note that, if you retrieve data from a local database, the server and client are on the same computer, but there is still a server and client relationship locally.

To fully understand how to use arrays, here are some good reference articles written by Microsoft.

• Understanding Arrays
• Creating Arrays
• UBound Function in VBA
• LBound Function in VBA
• Option Base Statement

Private Sub cmdGetRows_Click()
    On Error Goto Catch

    Dim strSql As String
    Dim objRst As ADODB.Recordset
    Dim lngCount As Long
    
    '' As Microsoft indicates, when defining an array, you do not have to 
    '' include the parentheses when you refer to an array variable, except
    '' when you declare it, resize it, or refer to an individual element. However, 
    '' you might want to include the parentheses everywhere to make it clear that 
    '' the variable is an array.
    
    '' This array will hold all the categories in a two-dimensional array
    Dim arr() As Variant
    
    '' Get all categories
    strSql = "select CategoryID, CategoryName from Categories"
    
    Set objRst = New ADODB.Recordset
    objRst.Open strSql, CurrentProject.Connection, adOpenForwardOnly
    
    '' Retrieve all categories into a two-dimensional array
    arr = objRst.GetRows()

    '' We can release the resources used by Recordset object 
    '' immediately after GetRows method is invoked.
    objRst.Close
    Set objRst = Nothing
    
    '' The default lower bound for any dimension is either 0 or 1, depending 
    '' on the setting of the Option Base statement.
    '' Because the default base is 0, the Option Base statement is never required.
    
    '' UBound here gives us the largest subscript for the second dimension of the array.
    '' Because, by default, our array's lower bound starts from 0 (0-based array) 
    '' so we need to add one to the upper bound to get its size (i.e. number of elements)
    '' in the array.
    lngCount = UBound(arr, 2) + 1
    
    Debug.Print "Record count: " & lngCount
        
    Exit Sub
    
Catch:
    MsgBox "cmdGetRows_Click() " & vbCrLf & vbCrLf & _
           "Error#:  " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub

3. Using SQL select statement to query the number of records.

If you need to know the number of records in order to decide whether you need to retrieve the actual data, use this method. If there aren't enough rows, you probably don't need to make the extra overhead of selecting data from the database. Alternatively, you can display a message to let users decide if they'd like to go ahead for further data retrieval.

Private Sub cmdSelectBySQL_Click()
    On Error Goto Catch

    Dim strSql As String
    Dim objRst As ADODB.Recordset
    Dim lngCount As Long
    
    '' Get all categories
    strSql = "select count(CategoryID) As cnt from Categories"
    
    Set objRst = New ADODB.Recordset
    objRst.Open strSql, CurrentProject.Connection, adOpenForwardOnly
    lngCount = objRst!cnt
    
    Debug.Print "Record count: " & lngCount
        
    '' Clean up
    objRst.Close
    Set objRst = Nothing
        
    Exit Sub
    
Catch:
    MsgBox "cmdSelectBySQL_Click()" & vbCrLf & vbCrLf & _
    	   "Error#:  " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub

Happy Coding!


Copyright© GeeksEngine.com