Updated 21/10/2018 - updated table info / version summary / flags info / deep hidden tables solved
The purpose of this article is to summarise known information about Access system tables.
In addition, I hope to use this article to obtain additional information about some of the more obscure system tables from other access developers reading this article.
With a few exceptions, there is very little information available online about many of the system tables.
One of the exceptions is the MSysObjects table which is both well known & extremely useful for experienced developers.
It is important to stress one of the main reasons for the lack of information
System tables are used by Access to make databases function correctly
Some system tables can be viewed & a few can be edited
But that doesn't mean you should do so ....UNLESS YOU ARE ABSOLUTELY SURE WHAT YOU ARE DOING
Altering one table may have 'knock on' effects on other tables
Incorrectly editing system tables may corrupt your database or prevent you opening it
Anyway, having made that point, I'll continue…
I am now aware of 58 system tables that may be created in different versions of Access.
I have managed to deduce what many of the system tables do but a few remain a bit of a mystery.
As Access has developed over the years, some tables used in older MDB files no longer exist.
Many additional tables were added with the introduction of the ACCDB file format.
Almost all system tables are prefixed with 'MSys' but a small number use a 'f_' prefix.
Additional system tables may be created by users – these have a 'USys' prefix
Many system tables are created automatically when a new database is created.
Others are created dependant on certain actions e.g.
a) import/export specifications (MSysIMEX …)
b) cache settings in A2010 or later (MSysWSPD…)
c) custom groups in the navigation pane (MSysNavPane…)
d) use of attachment fields or multivalued fields
e) user created system tables e.g. USysRibbons / USysRegInfo
f) replication in MDB files etc
By default, ALL system tables are hidden from the navigation pane.
However, many tables can be made visible by ticking Show System Objects in Navigation Options
However, several system tables in ACCDB files are what I call deep hidden tables. NOTE: MDB files do NOT contain any deep hidden tables.
These are special tables that can NOT be viewed in the navigation pane even by ticking Show Hidden Objects & Show System Objects
It is possible to make those tables visible again ....by a different method.
I'm deliberately NOT going to cover that topic here as several of my Security Challenge apps include deep hidden tables as part of the challenge
The attached database contains details of 58 system tables which I have divided into 5 groups
A: can be made visible / can be edited (but see comment above)
B: can be made visible / read only (though with limited exceptions in some cases)
C: deep hidden but can be viewed using queries - can be edited (but see comment above)
D: deep hidden and cannot be viewed directly or using a query (UPDATE 21/10/2018 - see comment at end of article)
E: discontinued tables (from earlier versions & no longer in use)
System Tables - Purposes
System Tables - Version Summary
In the next section of this article, I will summarise the properties of each system file.
In addition, information is supplied about the Type & Flags values used by Access to identify the properties of all database objects.
Click this link to go to Page 2 or use the navigation buttons below
To provide feedback on this article, please enter your name, e-mail address and company (optional) below, add your comment and press the submit button. Thanks
Difficulty level : Advanced