Count records in local, linked or other DB tables
Sometimes it is useful to count the number of records in a local table, linked table, or in another Access DB (that may or may not be linked). Was putting some notes together to have all of them handy.
(DCount with *)lngRecordCount = DCount("*", "TableName")
(DCount with fieldname)lngRecordCount = DCount("ID", "TableName")
Named query lngRecordCount = CurrentDb.OpenRecordset("Query_with_Count")![CountOfID] ' A pre-made query with Count
Access local table lngRecordCount = CurrentDb.OpenRecordset("TableName").RecordCount (will not work on Linked Tables)
RecordCount for external lngRecordCount = OpenDatabase("C:\FolderName\DatabaseName.accdb").O penRecordset("TableName").RecordCount
SQL with * (wildcard)lngRecordCount = CurrentDb.OpenRecordset ("SELECT Count(*) AS [CountOfID] FROM TableName;"![CountOfID]
SQL with fieldname lngRecordCount = CurrentDb.OpenRecordset ("SELECT Count([ID]) AS [CountALL] FROM TableName;")![CountAll])
For a pure Access environment, the RecordCount is many times faster. However, if the database is ever split or split and upsized to SQL Server later, it will not work.
Other than that, in Access 2010 split (front-end and back-end), there was not any huge difference in time in my case.
Some of you that have larger tables and indexing might have some useful comments to add.
After I upsizing Access 2010 to SQL Server 2008, there may be some speed differences to add here.
Another method not shown here is to dim DB as Database, down to the table, move to the last record and get a record count.
Were you lucky enough to get an answer? Please mark your question as [SOLVED] The original poster can go to Thread Tools to mark it as Solved.
Quotation Thomas Jefferson: "Peace is that brief glorious moment in history when everybody stands around reloading."
There are 2 Kinds of Countries on this Planet
1. Those that use the Metric System
2. Those that had a man walk on the moon
Denver, Colorado - The "Mile High City" - non-metric!