Rx_
Nothing In Moderation
- Local time
- Today, 13:52
- 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.
(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.