MS Access System Tables

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?

Hidding Access System Tables

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
  1. Select Tools >> Options from the File Menu.
  2. In the Options dialog, select the View tab.
  3. At the top right, make certain the “System objects” check box is un-checked.

Access 2007 and Access 2010
Access 2007 and Access 2010 Options Dialog
 
  1. Right-click the top of the Navigation Pane.
  2. Select Navigation Options.
  3. Make certain the “Show System objects” check box is un-checked.


What Does Each System Table Do?

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.
MSysObjects
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:
Type Column Object Type
-32761 Module
-32764 Report
-32766 Macro
-32768 Form
5 Query
1 Local Table
4 Linked Table (ODBC to SQL)
6 Linked Table (Access/Excel)
8 Access Reserved
2 Access Reserved
3 Access Reserved
MsysQueries
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
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).

Using System Tables in Your Databases

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.

Copyright © 2017 OpenGate Software Inc. All Rights Reserved.

MS Partner