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.

Parse Multivalue Field To Normalized Structure.mdb

Parse Multivalue Field To Normalized Structure.mdb

by Roger Carlson

This sample illustrates how to write values from a multiple valued field (not the Access 2007 multi-value field) into a normalized Many-to-Many structure. The original table is called "ContactsDownloadMultivalue". The contact information is written into "tblContacts". The category information from the multiple valued field is written to "tblCategories".

Then a record is created in jtblContactCategory junction table, establishing the many-to-many relationship.

You can find the sample here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=449

.

What is the fastest way to return the Record Count of a table?

As with most things in Access, there are many ways to get the record count of a table. So which is the fastest? The answer is: it depends.

There are a many factors that determine performance:

• Whether the database is on the local drive or a network,
• Whether the table is native or linked,
• Whether the table is indexed or not,
• The number of records in the table.

There are other factors, of course, like processor speed and disk access speed, but we'll assume we're testing all of the proposed methods on the same machine, so the results will be proportional.

I don't propose to test all combinations of these performance variables. If there are only a few records, all the methods shown will return the record count so quickly that it doesn't really matter. Similarly, if the database is on a local drive (C:), there is no perceptible difference. On the other end, if the table is not indexed, some of the methods are so slow, they are impractical.

So I've chosen a table with 7125822 indexed records (mostly because I had a table with 7125822 records available). I will test both native and linked tables, so I need two databases, which I will access across a standard 100mbps network.

Methods
There are three basic methods to find the number of records in a table:

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

Each of these methods has several variations as well. For instance, both the DCount and SQL methods can count on * or on a specific field. This may affect performance. The SQL method can use a saved query or an embedded SQL statement, which also may affect its performance. Lastly, the RecordCount method will ONLY work on a native table, not on a linked table. However, I can use the OpenDatabase method to open a linked table as a native table.
So here are the basic methods I tested.

Method 1a (DCount with *)

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

Method 1b (DCount with fieldname)

lntCount = DCount("ID", "MyTable")

Method 2a (embedded SQL with *)

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

Method 2b (embedded SQL with fieldname)

lntCount = CurrentDb.OpenRecordset _
("SELECT Count([ID]) AS [CountALL] FROM MyTable;") _
![CountALL])

Method 3 (saved query)

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

Method 4a (RecordCount Native table)

lntCount = CurrentDb. _
OpenRecordset("Mytable").RecordCount

As I said, the RecordCount property will only return the correct record count if the table is a LOCAL table. If it is a linked table or a query, it will always return 1 as the record count. However, I can explicitly open the table with the OpenDatabase method.

Method 4b (RecordCount Linked table)

lntCount = OpenDatabase _
("c:\My Documents\LinkedDatabase.mdb"). _
OpenRecordset("Mytable").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 time. The Figure 1 below shows the outcome.

Figure 1: Comparison of Record Count Methods

The results of whether to use a * or a fieldname in either the DCount or SQL statement appears inconclusive. It also doesn't seem to matter much if the table is native or linked. However, I can say that that the RecordCount property of a TableType Recordset object is the fastest method by at least an order of magnitude.

.

How Do I Decompile a Database?

Decompiling An Access Database

In order for any program code to be run by a computer, it must be converted to machine-readable code. This code is called Object Code. The text version of this program that you and I can read is called Source Code.

In Access, the process of producing Object Code from Source Code is called "compilation". Whenever code is run for the first time in Access, the code is first compiled. (You can also compile it yourself by pushing the compile button). The only place you can make changes to code is in the Source Code, which must again be compiled into Object Code.

Occasionally, code can be deleted from the Source Code, but for some reason is never removed from the Object Code. This code is never again seen on the screen as text, but is still sitting there somewhere in the database file. This can, at times, interfere with the normal operation of the program.

To remove these stray bits of code, you can "Decompile" your database and then re-compile it. This process removes all of the compiled Object Code, then when you re-compile it, you only get Object Code that reflects the current Source Code.

To Decompile your Access database, do the following:

1. Click the Start button on the task bar and choose Run…
2. In the Run dialog box, type the following:
   "C:\Program Files\Microsoft Office\Office\Msaccess.exe" /decompile
   where the first part (in quotes) is the complete path to your Access program. If you have the default installation, it is likely that it is just as listed here. Click OK.
   Note: if you have Windows Vista, just type the command line in the Start Search box of the Start menu.
3. This will open Access and allow you to choose which database to open. Whichever database you open will be decompiled.
4. Choose a database and open it. Access 97 used to give you a dialog which told you the database had been decompiled, but newer versions do not. Nevertheless, the database has been decompiled.
5. Open any Module in design view, or any Form or Report in design view and choose View > Code from the menu. In the next screen, choose Debug > Compile from the menu bar.
   Note: In Access 2007, you can also choose Database Tools on the ribbon, then select Visual Basic. Then choose Debug > Compile.
6. Your database has now been Decompiled and Re-compiled.

Creating a Shortcut:

I use decompile quite frequently, so instead of typing the command line into the Run box, I've created a shortcut on my desktop. There are several ways to create a shortcut, the easiest is to use the Shortcut Wizard.

Right-click anywhere on your desktop and select New > Shortcut. The wizard will give you a dialog box allowing you to browse to the file you want. It should be the same as above in Step 2. Once you've browsed there, add the "/decompile" switch to the end as shown below. Be sure to separate the decompile switch from the file path with a space. It will look as follows:

Click Next.

It will ask to name your shortcut. Choose something descriptive like Decompile Access 2003. And click Finish.

The finished shortcut will look like this:

Now when I want to decompile a database, I just click the shortcut and start with Step 3 above.

.