Count All Records in Every MS Access Table

There is a Mobile Optimized version of this page (AMP). Open Mobile Version.

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!

Author: Dean Williams

I'm a Web Developer, Graphics Designer and Gamer, this is my personal site which provides PHP programming advice, hints and tips

Post Tags: