What is the fastest way to return the Record Count from an Access Query?

In my earlier post (What is the fastest way to return the Record Count from an Access table?), I found the fastest way to return the record count of an Access table. It turns out the RecordCount property of the OpenRecordset method is at least ten times faster than any other method.

Unfortunately, the RecordCount property does not work the in the same way for a query as it does for a table. For a query, it will return a record count of 1, regardless of the number of records in the query. The reason for this has to do with the three recordset types that OpenRecordset returns.

If I open a table-type recordset,

OpenRecordset("MyTable", dbOpenTable)

The RecordCount property returns the number of records in the table. When the object I'm opening is a table, it defaults to dbOpenTable, so I don't really need to specify it. What makes this so fast is that the record count is actually stored with the table. It doesn't need to calculate the record count at all.

However, if I open a Dynaset recordset,

OpenRecordset("MyTable", dbOpenDynaset)

the recordset property returns only the number of records accessed, which is 1 when the recordset is first opened. In order to find the actual number of records, I have to use the MoveLast method to move to the last record. Then the RecordCount will return the accurate number.

This should be much slower, since the MoveLast method is not particularly fast. But is this true? I propose to find out.

I tested both native and linked tables, so I had two databases on a standard 100mbps network.

Methods

Again, there are three basic methods to find the number of records in a query:

1. DCount domain aggregate function.
   
2. SQL Count aggregate query.
   
3. Recordcount property of the OpenRecordset method.
   

Each of these methods has several variations, but as I showed in the last post, the difference isn't enough to worry about. So this time, I only tested one variation. I used a query which returned 7125822 records from indexed native and linked tables across a standard 100mbps network.

So here are the basic methods I tested.

Method 1 (DCount)

lntCount = DCount("*", "MyQuery")

Method 2 (embedded SQL)

lntCount = CurrentDb.OpenRecordset _
("SELECT Count(*) AS [CountAll] FROM MyQuery;") _
![ CountAll]

Method 3 (saved query- same query as Method 2)

lntCount = CurrentDb.OpenRecordset("CountQuery") _
![CountOfID]

Method 4 (RecordCount method)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("MyQuery", dbOpenDynaset)
rs.MoveLast
lntCount = rs.RecordCount

Testing the Methods

To minimize any differences in network traffic and processor usage, I ran each of the methods 10 times and averaged the elapsed times. The Figure 1 below shows the outcome.

Figure 1: Comparison of Record Count Methods

	Native Table	Linked Table
Method 1	10.7 seconds	10.1 seconds
Method 2	10.6 seconds	10.1 seconds
Method 3	10.3 seconds	10.3 seconds
Method 4	10.4 seconds	10.5 seconds

To be honest, I expected that the RecordCount method would be significantly slower than the other methods. I assumed that while DCount and SQL would make use of the index while the MoveLast method would have to do a full table scan. That should be slower, but that doesn't appear to be the case.

As far as I can determine, there isn't any significant difference between the various methods. I hate being wrong.