Last Updated 21 Feb 2022       2 additional system tables (TOTAL = 66), A365 also included                                 Difficulty level :   Moderate

Section Links: (this page)
          Introduction
          System Tables - Version Summary
          System Tables - Purposes
          Feedback


Introduction                                                                                                                                     Return To Top

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 main 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 66 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, several system 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

Most 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 system tables can be made visible by ticking Show System Objects in Navigation Options.
In some cases, you also need to tick the Show Hidden Objects option

However, several system tables in ACCDB files are what I call 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
NOTE: MDB files do NOT contain any deep hidden tables.

There is a somewhat obscure method that can be used to make those deep hidden tables visible.
I am deliberately NOT going to explain how that is done in this article. Several of my Security Challenge apps include deep hidden tables as part of the challenge.

The attached database contains details of 66 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
E:   discontinued tables (from earlier versions & no longer in use)



System Tables - Version Summary                                                                                                 Return To Top

NOTE: System tables for all versions of Access from 2010 onwards are IDENTICAL




System Tables - Purposes                                                                                                             Return To Top




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



Feedback

Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.

Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington           Mendip Data Systems                 Last Updated 21 Feb 2022



Return to Access Articles Page Return to Top Page 1 of 3 1 2 3