Go Back   Access World Forums > Microsoft Access Reference > Code Repository

Thread Tools Rating: Thread Rating: 4 votes, 5.00 average. Display Modes
Old 04-03-2012, 09:21 AM   #1
Nothing In Moderation
Rx_'s Avatar
Join Date: Oct 2009
Location: Denver, Colorado
Posts: 2,769
Thanks: 623
Thanked 331 Times in 303 Posts
Rx_ has a spectacular aura about Rx_ has a spectacular aura about Rx_ has a spectacular aura about
Post 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!
Rx_ is offline   Reply With Quote
The Following User Says Thank You to Rx_ For This Useful Post:
sneuberg (09-22-2016)
Old 12-21-2017, 07:22 AM   #2
Newly Registered User
Join Date: Dec 2017
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
cobto is on a distinguished road
Re: Count records in local, linked or other DB tables

I used the code for Record count for external tables. It worked like a charm and is much faster than what I was using.

cobto is offline   Reply With Quote

count records , linked database , local , table , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Make Linked Tables Local Newbie2001 Tables 4 12-26-2011 06:20 PM
Linked Tables, Local Tables and ODBC lcbateman3 Tables 2 01-23-2009 04:40 AM
tables changing from linked to local at runtime civmeup General 2 02-19-2008 08:52 PM
Linked Tables to Local Tables Problem jennilewis Tables 0 08-26-2005 01:47 AM
COUNT with linked tables ErikRP Reports 1 12-04-2001 07:14 AM

All times are GMT -8. The time now is 08:46 AM.

Microsoft Access Help
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit

Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World