Custom Search
 


Three efficient ways to get the number of records by using VBA



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.

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



Other Recent Articles from the MS Access category:

1.Solved - the size of the Access query result is larger than the maximum size of a database (2 GB)
2.How to easily get a list of field names in MS Access
3.How to count distinct records in MS Access
4.How to do transaction based processing in MS Access
5.How to open a document (local/network file or web page) from MS Access
6.How to use ADOX to create unique composite index - the VBA approach
7.How to do cross-table update queries in MS Access - the right way
8.How to create a composite unique index (not as a primary key) in MS Access
9.Use VBA to get the correct number of records in a Recordset object
10.Disable Access Prompt when a record is changed, table deleted, or action queries run
11.How to hide and unhide a MS Access object
12.How to return multiple values from a VBA function (Part 3)
13.How to return multiple values from a VBA function (Part 2)
14.How to return multiple values from a VBA function (Part 1)
15.Three ways to programmatically duplicate a table in MS Access by VBA
16.Create a DLL by CSharp or VB.Net for VBA
17.How to correctly reference and call a DLL
18.How to register a C# or VB.Net DLL
19.Email address validation by Regular Expressions using VBA
20.Fix MS Access error: Query must have at least one destination field
21.How to unselect radio buttons in MS Access after it has been selected
22.How to Change Query Timeout Value for MS Access SQL Queries
23.What is Northwind Traders database

Copyright © 2017 GeeksEngine.com. All Rights Reserved.

This website is hosted by HostGator.

No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it.

 
Home | Feedback | Terms of Use | Privacy Policy