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 65 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.

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. 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 64 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)