Count All Records in Every MS Access Table 1

[eerich]

Hi Everyone,

I'm hoping someone can help me with a query design to count all records (rows) within each table of an Access database. For example, I have about 20 tables and I would like to list the table name (MSysObjects.Name)and the total count of records within each table (count(*) as TtlRecs). My code I attempted was:

SELECT MSysObjects.Name, count(*) as TtlRecs
FROM MSysObjects
WHERE (((MSysObjects.[Type])=1)
GROUP BY Name
ORDER BY 1;

The results produce each table name but the count is just showing 1. I'd appreciate any help on adjusting the query to count the actual records within each table.

 

[dhookom]

You do this with some code from faq701-4233. Add the Concatenate function to a blank module and then modify your query to:

SELECT MSysObjects.Name, Concatenate("SELECT COUNT(*) FROM [" & [Name] & "]") AS RecCount
FROM MSysObjects
WHERE MSysObjects.Type=1 AND [Name] Not like "Msys*"
ORDER BY 1;

Duane
Hook'D on Access
MS Access MVP

 

[eerich]

Duane - Thanks.

It is close to working but not sure why I receive an error stating:
"Runtime Error 3078; Microsoft Access database engine cannot find the input table or query "
Make sure it exists and that it is spelled correctly."

Once I click the END button it shows the tables and record counts. Any idea how to fix the error? I have no understanding of the module code, just SQL.

 

[dhookom]

Troubleshoot by limiting the tables to about half and see if the problem persists. Add or remove tables from the query until you find the problem table(s).

Duane
Hook'D on Access
MS Access MVP

 

[eerich]

One more thing to add - the error line item in the module is line 53:
Set rs = db.OpenRecordset(pstrSQL)

 

[PHV]

Why the Concatenate function ?

SELECT [Name], DCount("*","[" & [Name] & "]") AS RecCount
FROM MSysObjects
WHERE Type=1 AND [Name] Not Like "Msys*"
ORDER BY 1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

Good point PH. I believe I had used DCount() in the past. Just having a senior moment ;-)

Duane
Hook'D on Access
MS Access MVP

 

[eerich]

PHV and Duane,

That worked perfectly!

Many thanks!!!