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.
- Open up your MS Access Database.
- Go to "Create" > "Query Design" and close the popup.
- Click "SQL View".
- Copy this SQL Query:
SELECT [Name], DCount("*","[" & [Name] & "]") AS RecCount FROM MSysObjects WHERE Type=1 AND [Name] Not Like "Msys*" ORDER BY 1
- Paste it in the SQL Window and click "Run".
- Every table in your MS Access DB should now show with total record counts next to them! Enjoy!