Hidden Tables in Your Microsoft Access Database

Any sophisticated piece of software like Microsoft Access will have many hidden and internal files which are archived to maintain the application’s environment and user customisations. In Access, we are talking about System Objects; in particular the MSysObjects Table which is a hidden table that can be used to extract information about your Access database.

Microsoft Access System Objects consist of several tables that gives you important and useful information about your database application. In this post, I’m going to focus on  MSysObjects Table. It contains a list of all object types, when it was created, last updated and other related ID’s which link to other system tables.

A health warning should be included here. DO NOT attempt to delete or modify this table, its records (if you can)! It will potentially kill the system and corrupt a lot more than you may think.

First of all, you need to show the hidden system tables but ticking the ‘Show System Objects‘ option checkbox control which is found in different locations based on the version of Microsoft Access you use.

In Access 2003 (and earlier), you need to go to the ‘Options…‘ command via the ‘Tools‘ menu and click on the ‘View‘ tab.

In later versions, this feature is found from the ‘Navigation Options…of the Navigation Pane (by right mouse clicking the header).

Microsoft Access 2010 Navigation Pane - System ObjectsOne of the key fields in this table is the ‘Type‘ field which has a unique value for each type of object and can be used to interrogate and extract recordsets. An example may well be to list tables in the current database to populate a combo-box control on a form or to list queries in a list-box control for a form.

MSysObjects.Type field has the following values representing the following objects:

Tables 1
Linked Tables 6
Forms -32768
Queries 5
Reports -32764
Modules -32761
Macros -32766
Relationships 8

Don’t ask me why the values assigned are not obvious – just accept it!

You use system tables to create additionally functionality for your Access database and help manage user controlled environments that otherwise could be exposed to all users and make your database un-user-friendly.

For example, I want to view all forms in a combo-box so that the chosen item can be used as a parameter setting for the default loading of a form (very much like setting the ‘Start-Up’ of a selected form in Microsoft Access).

SELECT MSysObjects.Name FROM MSysObjects
WHERE (Left$([Name],1)<>"~") AND (MSysObjects.Type)=-32768
ORDER BY MSysObjects.Name;

Note the MSysObjects.Name is also set in the WHERE clause to exclude the ~ (tilde) as this refers to internal settings namely indexes and other non essential references for our purpose.

Copy the above SQL statement to either the combo-box control (or save it first as a new query) and then you can start to add functionality to your form.

There are other system objects which will also be visible. Don’t forget to hide these tables when done though in a polished database application you will probably have hidden the ‘backstage‘ view inclusing the Navigation Pane.

I welcome your comments in the form below. Keep up to date with Access Database Tutorial free weekly tips loated at the top (right) of this page.

This entry was posted in MS Access, Tables, Utilities and tagged , , , , . Bookmark the permalink.

2 Responses to Hidden Tables in Your Microsoft Access Database

  1. Ben Beitler says:

    Using hidden system object tables has been a very useful technique in maintaining ‘back-office’ functionality from populating drop-down controls to managing ODBC connections to external data files (some with passwords applied).

  2. sreekanth says:

    its really very nice & useful

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Confirm you are human.