Codes in mSysObjects

An Access database has a number of hidden tables that it uses for its own purposes. They are undocumented but have remained stable as long as I can remember and can be quite useful. The mSysObjects table holds a list of all the objects in the database and I use it as a starting point when working on an unfamiliar system.

Showing the mSysObjects table

These tables are hidden by default so the first move has to be to make them visible. Right-click on a header bar in the Navigation Pane and select Navigation Options... from the menu. This will open the Navigation Options dialog:

[Showing the system tables in Access 2007]

Click the Show System Objects checkbox. There is no need to check the Show System Objects checkbox. This just controls the visibility of the objects that we have hidden.

Understanding the mSysObjects table

The two immediately-useful fields in the mSysObjects table are Name and Type. Name holds the name of each object, Type holds a code that identifies the type of this object. These codes are:

Type of object Code
Form -32768
Macro -32766
Report -32764
Code module -32761
Local table 1
Query 5
Attached table 6

Knowing these codes it's easy to build a filter query that will list the names of a particular type of object as a starting point for documentation or a group query that will count the objects as a first measure of the size of the system.