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.