INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Count All Records in Every MS Access Table

Count All Records in Every MS Access Table

(OP)
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:

CODE

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.

RE: Count All Records in Every MS Access Table

(OP)
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.

RE: Count All Records in Every MS Access Table

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

RE: Count All Records in Every MS Access Table

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

RE: Count All Records in Every MS Access Table

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

Duane
Hook'D on Access
MS Access MVP

RE: Count All Records in Every MS Access Table

(OP)
PHV and Duane,

That worked perfectly!

Many thanks!!!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close