You were close. The fastest way to get the record count of a table is:
CurrentDb.OpenRecordset("Mytable").RecordCount
The thing is, here it HAS to be a LOCAL table to return the correct record
count. If it is a linked table or a query, it will always return 1 as the
record count.
The fastest way to get the record count of a linked table is this:
Dim db As DAO.Database
Set db = OpenDatabase("c:\My Documents\LinkedDatabase.mdb")
db.OpenRecordset("Mytable").RecordCount
I suppose you could put it all on one like like this:
OpenDatabase("c:\My
Documents\LinkedDatabase.mdb").OpenRecordset("Mytable").RecordCount
On a table with over 7 million records, both of these returned the record
count in 1 second or less. Every other method mentioned will be an order of
magnitude slower. (Disclaimer: I didn't check ADO methods.)
--
--Roger Carlson
MS Access MVP
Access Database Samples:
www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L