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:
Public Sub CountObjects()
Dim qdf As DAO.QueryDef
Dim obj As Object
Dim tdf As DAO.TableDef
Dim i As Long
i = 0
For Each tdf In CurrentDb.TableDefs
If Not Left(tdf.Name, 4) = “MSys” Then
i = i + 1
Debug.Print “Number of tables: ” & i
‘Determine number of queries
Debug.Print “Number of Queries: ” & CurrentDb.QueryDefs.Count
‘Determine number of forms
Debug.Print “Number of Forms: ” & CurrentProject.AllForms.Count
‘Determine number of Macros
Debug.Print “Number of Macros: ” & CurrentProject.AllMacros.Count
‘Determine number of reports
Debug.Print “Number of Reports: ” & CurrentProject.AllReports.Count
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.
how to count recent objects opened?
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