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
Feedback
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
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:
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
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:
|
|
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
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:
The contents of the f_.............._Data table don't appear to be at all crucial compared to some other system tables.
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.
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:
Similarly, the deep hidden tables managing column history in memo/long text fields itemise the data systematically in a normalised table.
For example:
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 2 of 3
1
2
3