Last Updated 21 Feb 2022 2 additional system tables (TOTAL = 66), A365 also included Difficulty level : Moderate
Section Links: (this page)
System Tables - Version Summary
System Tables - Purposes
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
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