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
Debug.Print CurrentDb.TableDefs.Count
For Each tdf In CurrentDb.TableDefs
If Not Left(tdf.Name, 4) = “MSys” Then
i = i + 1
End If
Next tdf
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

End Sub
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.