How to display the number of rows in a table?

[Gordon Padwick]

I am developing an Access application using Access 2010 running under Windows 7. The database has some large tables containing many thousands of rows. I want to display the number of rows in these tables in a form.

I have tried a couple of methods to do this, both of which work fine except that they take several seconds to run. One method is to use DCount; the other method is to create a recordset and access its RecordCount property. I'd welcome any suggestions for a faster way to do this.

When I open a table interactively, the number of rows is immediately displayed. It seems that Access keeps track of the number of rows in a table and that there is probably a way to access that number without writing code to rediscover it. I've hunted, without success, to access this number.

Gordon

[Mariostg]

Maybe it would be faster if instead of using the RecordCount method, you include your record count in the sql statement such as SELECT COUNT(aField) as rowCount FROM BigTable. Then you retreive rowCount. I would certainly be curious to know which one is faster.

[NeoPa]

I think this may be a simple case of mis-memory Gordon. Access tables when opened, will show the number of records almost immediately only when this can be determined pretty quickly. There are a number of cases where a noticeable delay occurs with the value empty, before it is updated to reflect the actual count. Linked, and particularly remote, tables suffer from this much more than local ones generally, but there is no magically stored value associated with the table that can easily be used.

This question has come up before, and the recommendation is to access the last record before requesting the count. This has the effect that the buffer will be populated to a greater extent (not the full table necessarily), so this is a benefit if users intend to move around the recordset within the form. If that is not likely to be a benefit then a DCount() call may well suit your purposes as well.

NB. The RecordCount property will return a value (if called) even before the extent of the recordset is known, so beware of relying on such a value until after a call to go to the end (MoveLast).