You're browsing your tables in Microsoft Access when suddenly you notice
a large number of greyed-out tables with foreign names starting with "MSys..."
Fear not, these aren't the result of spyware or an add-in gone awry.
These are Microsoft Access system tables. Typically hidden in
the background, they are necessary for Access to run. So, what
are they exactly, what do they do, and what can be done about them?
Access System Tables are read-only. You won't be able to delete
them, or even add/change/delete records. If you just want to hide
them and worry about them no more, here's how:
- Access 2000, Access 2002, and Access 2003
- Select Tools >> Options from the File Menu.
- In the Options dialog, select the View tab.
- At the top right, make certain the “System objects” check
box is un-checked.
- Access 2007 and Access 2010
- Right-click the top of the Navigation Pane.
- Select Navigation Options.
- Make certain the “Show System objects” check box is un-checked.
- MSysIMEXColumns and MSysIMEXSpecs
- These two tables contain information about any Import/Export
Specifications you have created in Access. The MSysIMEXSpecs
contains general information about the specification, while MSysIMEXColumns
includes the column mapping for each specification.
- Ever table, query, form, report, macro in Access. The column
"Name" indicates the name of the object, and the column Type represents
what type of object:
||Linked Table (ODBC to SQL)
||Linked Table (Access/Excel)
- Query definitions are stored in this table. You'll
find that most of it makes no sense unless you feel like parsing
the columns. Each row relates to a specific query
(ObjectID column) and parts of the query itself.
- MSysRelationships stores the relationships between each
table in your database. The columns 'szObject' and
'szColumn' relate to a corresponding 'szRelatedObject' and
'szRelatedColumn.' When you make changes to relationships
(via the Relationships tool or DAO), entries will be
automatically added by Access in the table. We have
noticed instances where the same relationship is repeated with
the szObject and szRelatedObject columns populated in different
rows for the same table (i.e., the tables are related in two
rows, just flipped).
You can make use of Access System Tables in your own projects to get
fast information about the objects available without resorting to VB
or macros. Say you want to display a list of reports available
in your database to a user in one of your forms. Simply add a
combo-box to your form, then set the Row Source property as follows:
SELECT [Name] FROM [MSysObjects] WHERE [Type] = -32764
AND Left([Name],1) <> "~" ORDER BY [Name]
In the example
above, your user will see a list of reports, sorted by name.
Notice that we added a part with <>"~" ? That is to prevent
the user from seeing temporary objects Access stores in MSysObjects
that are never visible or usable.