Count records in local, linked or other DB tables (1 Viewer)

Status
Not open for further replies.

Rx_

Nothing In Moderation
Local time
Today, 08:59
Joined
Oct 22, 2009
Messages
2,803
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").OpenRecordset("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.
 

cobto

New member
Local time
Today, 09:59
Joined
Dec 21, 2017
Messages
2
I used the code for Record count for external tables. It worked like a charm and is much faster than what I was using.

Thanks,
Tony
 
Status
Not open for further replies.

Users who are viewing this thread

Top Bottom