Count all the objects in your database

The other day our company was tasked to convert a lot of Access databases to multiple SQL Server backends. To get a sense for the tasks involved, we needed a way to count all the tables, queries, forms and macros in each database.

I did a quick Internet search but did not like any of the code snippets, so I created my own little program that will print out the stats to the immediate window:

SQL Server Hosting

A couple of observations:

  • In order to not count the system tables I iterate through the TableDef collection and ignore any table name that starts with MSys
  • I could not use the Form collection to count the forms since its members consist of only open forms.




About the Author:

Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He's a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.

One Comment

  1. Maxim Ivashkov February 24, 2016 at 7:29 pm - Reply

    Hi Juan

    for a backend migration i would consider only Tables. The rest stays in the front end.

    While migratie I experience some issues with yes/no field. Azure does not allow Null for this field automatically, so I had to change all yes/no fields to allow Null. If you know any script for that , would be great.

    Other issues could be slow performance for some Access queries. Some of the could be migrated as wel. However Access allows to put fields with the same name in a query, while sql server does not allow it in views. This issues plays only if you have a dirty designed Access.

    One more thing, import data via Management Studio does not migrate indexes

Leave A Comment


Contact Us
close slider
  • This field is for validation purposes and should be left unchanged.