Count All Records in Every MS Access Table

Recently I worked with a client that wanted to export a MS Access DB into MySQL, the conversion was done by a third-party but I was asked to confirm that all the data from the tables was present in PHPMyAdmin. The easiest solution to this was to compare how many records were in each table, however Microsoft Access does not give you record counts next to the list of tables and there was no easy way to do this, even in reports.

I found out how to do this with a simple SQL query below, I have provided full steps on how to do this in MS Access below with screen shots.

  1. Open up your MS Access Database.
  2. Go to "Create" > "Query Design" and close the popup.
  3. Click "SQL View".
  4. Copy this SQL Query:
    SELECT [Name], DCount("*","[" & [Name] & "]") AS RecCount
    FROM MSysObjects
    WHERE Type=1 AND [Name] Not Like "Msys*"
    ORDER BY 1
  5. Paste it in the SQL Window and click "Run".
  6. Every table in your MS Access DB should now show with total record counts next to them! Enjoy!
Facebooktwitterredditpinterestlinkedinmail
Author: Dean WilliamsI'm a Web Developer, Graphics Designer and Gamer, this is my personal site which provides PHP programming advice, hints and tips

5 6 votes
Article Rating
Subscribe
Notify of
3 Comments
Inline Feedbacks
View all comments

Excelent!!! Just the thing I needed. It works like a charm!

In a database with more than 600 tables I was looking for tables with many records, for my purpose. This query did the job.

Thanks man!

Sir, you rule! Thanks a million. Greetings from Rio de Janeiro

Thanks! This is great.