Page 1



Last Updated 7 Dec 2021       2 additional system tables (TOTAL = 66), A365 also included                                 Difficulty level :   Moderate

Section Links: (this page)
          System Tables - Properties Summary
          Type & Flags Values Explained
          Access File Sizes
          How was the information obtained?
          Deep hidden system tables
          Conclusions
          Downloads
          Related Articles & References


System Tables - Properties Summary                                                                                                 Return To Top

The chart below shows a summary of the properties for each system table
UPDATED 21 Feb 2022 - 1 more system table identified = 66 total

SysTableProperties1 SysTableProperties2 SysTableProperties3


What do the Type and Flags values mean?                                                                                         Return To Top

Each Access database object listed in the MSysObjects table has both Type and Flags values. Together these identify the properties of that object.

The Type value denotes the type of object. Local tables (both system & non-system) have Type = 1.
Linked ODBC tables (e.g. SQL Server) have Type = 4 and other linked tables e.g. Access/CSV/Excel/text have Type = 6.

The Flags value is more complex and is not fully documented by Microsoft.
For standard tables, Flags = 0 and for hidden tables, Flags = 8.

System tables have Flags = 2 but this can be combined with a range of other values, each indicating different properties.
For example, the MSysNameMap table has Flags = 10. It is both hidden & system, so the Flags values are combined: Flags = 2 + 8 = 10
Not all Flags values can be so easily explained!

Below is a summary of all local table Flags values and meanings:

TableFlags

The information below is from the Microsoft Office Developers website.
This shows the TableDefAttributeEnum Enumeration (DAO) which are used with the Attributes property to determine attributes of a TableDef object

TableDefEnums


Access file sizes                                                                                                                                     Return To Top

Newly created Access files are relatively large (up to 440 kB) compared to other Office files used in Excel/Word (15-20 kB).
The larger file size is due to the large number of system tables needed to make the database application work correctly.

As the Access feature set has developed, the number of system tables has increased leading to a similar increase in the file size on creation:

VersionFileSize VersionFileSizeChart



How was the list of System Tables obtained?                                                                                     Return To Top
The list of system tables were obtained by running a query on the MSysObjects table.
This was repeated using various Access applications created in versions from Access 2.0 through to 2021 (365):

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=1) AND ((MSysObjects.Flags)<>0));



Additional filtering was done where necessary to exclude non-system hidden tables (Flags=8):

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=1) AND ((MSysObjects.Flags)<>0 And (MSysObjects.Flags)<>8));



The contents of some tables can be viewed directly from the navigation pane though in MOST cases these CANNOT be edited.
Some of the deep hidden tables can also be viewed (but NEVER edited) using a QUERY. For example:

SELECT * FROM MSysWSDPCacheComplexColumnMapping;



However, several deep hidden tables cannot be viewed at all using queries. In such cases, running the query may result in:
a)   Reserved error -3087 - for all system tables starting with MSysComplexType_ . . .
b)   A message that the table "" cannot be found for all system tables starting with f_ . . .



Deep hidden system tables         UPDATE 21 Oct 2018                                                                       Return To Top

A passing comment by Chris Arnold, a fellow moderator at Access World Forums, led to an unexpected breakthrough.
I have finally discovered a method of viewing the contents of ALL the very deep hidden tables in group D.

The solution was in the end not that difficult - I will make the solution available on request.

The screenshot below shows the deep hidden tables visible in the navigation pane

DeepHiddenTableVisible
As is so often the case, the outcome was, to put it mildly, a bit of a let down.

ALL 10 MSysComplexTypes . . . tables were EMPTY in each of the databases I inspected. I am still unclear as to the purpose of those 10 tables.

The mysterious f_.............._Data table ALWAYS contains at least one record listing the Office theme in use.
In addition, the names of any image files added to the Image Gallery are also listed.

For example:

ImageGallery
The contents of the f_.............._Data table don't appear to be at all crucial compared to some other system tables.

SystemTablefData
Unbelievably, once it is made visible, the table is directly EDITABLE

In fact, this deep hidden table is both linked and almost identical to another system table MSysResources which is easily accessed and also directly editable.

MSysResources
The sole purpose of the f_.............._Data table is to manage the attachment field data in the MSysResources system table.

Yet again, Microsoft breaks all the rules - in this case regarding non-duplication of data!
If the MSysResources table stored the path to the theme and image gallery files as a text field, there would be absolutely no need for the additional f_.............._Data table

Likewise, the associated deep hidden tables managing attachment fields or multivalued fields in any user table itemise each item systematically in a normalised table.
For example, this is a deep hidden table (again starting with f_....) which manages multivalued field data:

SystemTableMVF
Similarly, the deep hidden tables managing column history in memo/long text fields itemise the data systematically in a normalised table.
For example:

MSysVHTable
Having finally managed to view these deep hidden system tables, it is difficult to understand why the Access development team decided to make these tables much harder to inspect than other system tables.

Clearly Access is an enigma wrapped up in a riddle



Conclusions                                                                                                                                             Return To Top

I hope the above information has been useful to other Access developers

As stated at the start of this article, I was hoping to obtain additional information about some of the more obscure system tables from anyone reading this article.

However, the aim of viewing the 'deep hidden' system tables in group D has now been realised.

It is unclear to me why Access makes these tables so difficult to view as their contents seem no more fundamental than other visible system tables

I would be grateful for details of any other system tables which I haven't seen and therefore not listed (as I'm unaware of them)
Similarly, please let me know if you are aware of any errors in the above article.

I am aware that this article does not cover these deprecated Access features - Data Access Pages (DAP), Access data projects (ADP) and web databases.

The attached database contains details of all the system tables listed above

In addition, I have included:
a) Table tblUsefulLinks with links to further reading on system tables
b) Table tblSysObjectTypes which allows you to identify the object type for each item in rhe system table MSysObjects



Downloads                                                                                                                                               Return To Top

Click to download:

      System Table Info v5.9                 (Approx 1.3 MB zipped)

      System Table Summary v5.9         (Excel file - approx 30 kB)



Other Related Articles on this website                                                                                                Return To Top

How Access Stores Queries

Multivalued Fields ...and why you shouldn't use them

Relationships and Referential Integrity

Remove Deleted Objects from the MSysObjects table

Use Column History in Memo fields



References used in researching this article

Data for multivalued fields

Access Storage

Access Add-ins

Replication - 1

Replication - 2

Replication - 3



Colin Riddington           Mendip Data Systems                 Last Updated 21 Feb 2022



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