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.
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.
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.
Copyright © 2013 GeeksEngine.com. All Rights Reserved.
This website is hosted by LunarPages.
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.