Purpose of System Tables (1 Viewer)

isladogs

MVP / VIP
Local time
Today, 10:10
Joined
Jan 14, 2017
Messages
18,186
Recently I've been researching the properties of deep hidden tables.
These are special tables that can NOT be viewed in the nav pane even by ticking Show Hidden Objects & Show System Objects

Several of the Access system tables fall into that category BUT not all.
It is also possible to create deep hidden tables yourself (if you know how)

It is also possible to make those tables visible again ....by a different method.
I'm deliberately not going to cover that topic here as my sample database A Security Challenge includes a deep hidden table as part of the challenge

Investigating the properties of these deep hidden tables has rekindled my interest in knowing as much as possible about the Access system tables.

There are at least 29 of these tables in ACCDB files (fewer in MDBs).
Many are created automatically when a new database is created.
Three of those have only recently appeared in my ACCDB files - those starting with MSysWSDP ....
Others are added dependant on certain actions e.g. import/export specifications

Almost all system tables begin with MSys but there are exceptions:
a) USysRibbons - details of user created ribbons
b) f_9E8203D96A754B0890DAF9414007C362_Data (or similar name)

With a few exceptions, there is very little information available online about system tables.

The MSysObjects table is well known & very useful for experienced developers.
I know what some of the others do ... but not all

It is important to stress the main reason for the lack of information
The purpose of system tables is 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
Incorrectly editing system tables may corrupt your database or prevent you opening it


Anyway, having made that point, I'll continue:

The attached database contains details of 29 system tables which I have divided into 4 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 by any method I'm aware of

The purpose of this thread is to try & obtain additional information about the more obscure system tables. Inevitably I'm particularly interested in those in group D.
Particularly by the mysterious f_...._Data table partly due to its very odd name (possibly a Microsoft programmers' in - joke?) and also because its unique in that Access claims it 'doesn't exist'.

I weould also be grateful for details of any further system tables which I haven't seen or therefore listed (as I'm unaware of them)
 

Attachments

  • SystemTableInfo.zip
    30.1 KB · Views: 289

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:10
Joined
Feb 28, 2001
Messages
27,001
Actually, while it is possible to edit a system table such as MSysObjects or one of the many other MSysxxx types, you don't want to directly edit them hardly ever. Directly query? Maybe, if you are rolling your own form of documenter.

However,you can INDIRECTLY edit them - by creating, altering, and deleting objects using the proper Access syntax for those objects. And if you do it that way, not only do you know you did it the right way (because you will not get an error code if it works), but you know that if something else was supposed to have been edited that was related to your change, you did THAT too. Making direct and unilateral structural changes is dangerous if what you are changing wasn't originally your structure.

Colin's emboldened paragraph that starts with "The purpose of system tables is to make databases function correctly" is crucial, particularly for beginners. The system tables work TOGETHER (not separately) to define how things fit together in your database. A very big "gotcha" for example is to dink around with MSysObjects and ignore the fact that the entries of the MSysRelationships table depend on the content of MSysObjects. So if you delete something or alter it (unwisely), you can screw two tables with one action. Two for the price of one!

Therefore, all readers should heed that highlighted advice very carefully.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:10
Joined
Feb 19, 2002
Messages
42,981
With the exception of the Import Specs and Ribbon tables, I would never under any conditions alter any system table, even if I could. You will quite likely render the database unusable if you make a mistake so make sure you have an excellent back up plan in place before even thinking about mucking around where you don't belong. The reason MS doesn't document these tables is because they don't want you changing them and they don't want you to depend on them since they can easily change from one version of Access to the next. Back in the days of A2.0 or maybe A97, I wrote a documentation utility that used the system tables that held querydefs. The utility broke with the next release of Access because the tables I used were removed.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:10
Joined
May 7, 2009
Messages
19,175
There are also hidden Functions and it is clear these ones have been discontinued and no longer work. Maybe those hidden table are also discontinued.
 

isladogs

MVP / VIP
Local time
Today, 10:10
Joined
Jan 14, 2017
Messages
18,186
This started as a short answer but I kept adding to it.

There are also hidden Functions and it is clear these ones have been discontinued and no longer work. Maybe those hidden table are also discontinued.

There are indeed a significant number of items in most VBA libraries that are normally hidden but can easily be made visible by clicking 'Show Hidden Members'
It is my understanding that in many cases these are hidden because they were meant for internal use by MS or never officially released. As a result, they are undocumented or documentation is sparse.

Over the years, I have used a number of these hidden members including LoadFromText, SaveAsText, Wizhook and others. All of the ones I've tried are definitely functional and indeed useful.

There MAY be hidden items that no longer work but I doubt it. Normally such items are removed rather than hidden.

Similarly, ALL the hidden system tables are created automatically and appear to be in active use in ACCDB files. The fact that they are very deeply hidden is an indication of something but not that they are discontinued

For info, I got the list of system tables from the MSysObjects table by running this query:
Code:
SELECT MSysObjects.Name FROM MSysObjects
WHERE (((MSysObjects.Type)=1) AND ((MSysObjects.Flags)<>0));

If you have any hidden tables of your own, these will be included so additional filtering may be needed to exclude these

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

For a newly created Access 2010 database, I get 26 tables. I ran an import spec so the 2 IMEX tables were added giving a total of 28. There is no added ribbon, so no USys table.



The last three (WSDP) have only just started appearing in my A2010 databases. Interestingly, they do NOT appear in databases I create using A2016 (365)

Does anyone else get these in any version of Access ... or any other tables?

As for editing the MSysObjects table, I am pleased to see that both Pat & Doc Man have restated the caution about doing this that I put in bold type in my first post. Only edit system tables if you know exactly what you are doing - and I strongly recommend you do so using a BACKUP or a TEST database.

In fact MSysObjects can only be edited indirectly and AFAIK only to remove records that shouldn't still be there.
I described 3 examples where this can be useful in this thread: https://www.access-programmers.co.uk/forums/showthread.php?t=293579

For info, I don't think you can delete a record from MSYsObjects for an object that still exists without deleting the object itself first. I've tried and so far failed (like all my experimenting, done on a test database)
However, if it were somehow possible to do so without deleting the object itself, then I believe the record would be re-created on next opening the database...
 

Attachments

  • SysTables.PNG
    SysTables.PNG
    28.9 KB · Views: 1,504
Last edited:

isladogs

MVP / VIP
Local time
Today, 10:10
Joined
Jan 14, 2017
Messages
18,186
I've updated the attached app to include:
a) version info for each system table
b) updated info about MVFs & Attachment fields (additional F_ tables)
c) 3 additional system tables no longer in use
d) USysRegInfo table created by installable add-ins
e) additional info on certain tables

This brings the total of identified system tables to 35

In addition, I have included:
f) a table with some useful links
g) MSysObjectTypes table which allows you to identify the object type for each item in MSysObjects

As before, I would be grateful for any additional information / corrections that others can provide

Just to repeat the warnings from post #1
The purpose of system tables is to ensure databases can 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 YOU KNOW WHAT YOU ARE DOING
Incorrectly editing system tables may corrupt your database or prevent you opening it
 

Attachments

  • SystemTableInfo v2.zip
    66.7 KB · Views: 221
Last edited:

isladogs

MVP / VIP
Local time
Today, 10:10
Joined
Jan 14, 2017
Messages
18,186
I have done further research using different versions of Access from Access 2.0 though to Access 2016/2019/365.
By doing so, I have now found details / examples of 50 different system tables in the many versions of Access.

For anyone who may be interested I've just uploaded an extended / updated version of the information in this thread to my website:
See http://www.mendipdatasystems.co.uk/purpose-of-system-tables/4594445136

The research is ongoing as & when I can spare the time.
One future task is to look into all the many system tables associated with replication of MDB files - at its something I haven't done for many years, I am, shall we say, a bit rusty on this... :cool:

I would be very grateful for feedback on this article from experienced developers, particularly if you have knowledge of any system tables I have omitted or spot any errors :eek:
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:10
Joined
Feb 19, 2002
Messages
42,981
You've done an amazing job of documenting these tables but I would suggest not getting too excited about using them. Microsoft specifically does not document them because they reserve the right to change them from version to version and so they don't want application code to depend on them. I made a mistake way back in A 2.0 and built a documentation tool for myself that broke with A97. Once burned, twice shy.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 10:10
Joined
Sep 12, 2006
Messages
15,614
on a different tack, although some posters use system tables to retrieve data, I have never done so.

I have found it possible to obtain whatever I needed by interrogating the DAO structure, for tables/fields/indexes and so on.
 

isladogs

MVP / VIP
Local time
Today, 10:10
Joined
Jan 14, 2017
Messages
18,186
on a different tack, although some posters use system tables to retrieve data, I have never done so.

I have found it possible to obtain whatever I needed by interrogating the DAO structure, for tables/fields/indexes and so on.

You can certainly obtain the vast majority of such information BUT ...
1. Can you tell me any method of getting the names / properties of deep hidden tables without using system tables? For example: MSysComplexType_Text

2, Better still, can you solve my security challenges without making any use of them? Amongst other security features, these challenges all include deep hidden tables which you need to 'unhide' and then view their contents
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 10:10
Joined
Jan 14, 2017
Messages
18,186
You've done an amazing job of documenting these tables but I would suggest not getting too excited about using them. Microsoft specifically does not document them because they reserve the right to change them from version to version and so they don't want application code to depend on them. I made a mistake way back in A 2.0 and built a documentation tool for myself that broke with A97. Once burned, twice shy.

Hi Pat
Thanks for your comments and the wise note of caution re using system tables.
Having said that, the structure has become much more settled since the ACCDB format was introduced with A2007.
Since then, just 3 more tables have been added (A2010 - MSysWSPD... related to cache settings) and no tables have been removed

I've just uploaded a further update to the article:
a) 8 more tables added including 7 additional replication tables I'd forgotten
Latest total = 58!
b) added a summary table listing system tables & Access versions
c) explanation of MSysObjects Type & Flags fields related to tables
d) comparison of file sizes for new databases in each Access version - inevitably these have grown as additional system tables were added
d) corrected some errors

The updated article is available here

I am aware that I haven't tested Access Data Projects or web databases (both now deprecated).
If anyone has working examples of either, please let me know of any additional system tables these contain

I'd also forgotten the original attempt to make Access usable in a browser (Data Access pages) and the reasons why they were scrapped
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:10
Joined
Feb 19, 2002
Messages
42,981
Here are some tables I made to help with using some of the systems tables. This one I used when I got frustrated with SSMA and built my own upsizing wizard by generating SQL Server DDL from the ACE tables.
Code:
TypeCode	TypeDesc	AltTypeDesc	AccessTypeDesc	SQLTypeDesc
1	Boolean	bit	Yes/No	Bit
2	Byte	bit	Byte	TinyInt
3	Integer	numeric(9,0)	Integer	SmallInt
4	Long	numeric(9,0)	Long	Int
5	Currency	money	Currency	Money
6	Single	money	Single	Real
7	Double		Double	Float
8	Date/Time	datetime	Date	DateTime
9	Binary		Binary	
10	Text	varchar	Short Text	varchar()
11	LongBinary		OLE Object	Image
12	Memo		Long Text	varchar(max)
15	GUID		GUID	UNIQUEIDENTIFIER
16	BigInt		BigInt	
17	VarBinary		VarBinary	
18	Char		Char	
19	Numeric		Numeric	
20	Decimal		Decimal	Decimal()
21	Float		Float	
22	Time		Time	
23	TimeStamp		TimeStamp
This one identifies query types.
Code:
Flag	FlagDesc
0	Select
16	Crosstab
32	Delete
48	Update
64	Append
80	Make Table
112	Pass-Through
128	Union
144	DDL
This one identifies object types in MSysObjects. You might have some items not listed here. I made this quite a while ago and haven't looked for new object types recently.
Code:
Type	TypeDesc
-32768	Form
-32766	Macro
-32764	Reports
-32761	Module
-32758	Users
-32757	Database Document
-32756	Data Access Pages
1	Table - Local Access Tables
2	Access Object - Database
3	Access Object - Containers
4	Table - Linked ODBC Tables
5	Queries
6	Table - Linked Tables or Files
8	Relationships
9	Constraints
 

isladogs

MVP / VIP
Local time
Today, 10:10
Joined
Jan 14, 2017
Messages
18,186
Hi Pat
Thanks for the info. I will add the datatype codes to my list though of course SQL Server has lots more these days.

I did my own list of MSysObjects items including both Type and Flags field.
See https://www.access-programmers.co.uk/forums/showthread.php?t=293579
I may have added a couple more records to that list since then.

One item I noticed you missed - data definition query with flags=96

I also used the info in this utility https://access-programmers.co.uk/forums/showthread.php?t=295597

For info, hidden objects add 8 to the Flags value.
Since I wrote each of the above articles, I discovered the 'deep hidden' property which I've deliberately not documented to keep them that way. Having said that its not at all difficult to identify how those objects are identified by Access.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 10:10
Joined
Sep 12, 2006
Messages
15,614
Most of the things Pat mentioned are available without going near system tables. I haven't checked for the third table, but I expect it's there as well.

eg.

PAT's table 1:
field type is an integer corresponding to Pat's table 1, all of which have constants, such as

dbBoolean = 1
dtByte = 2
dbText = 10 etc

in dao, the field type is
tabledefs(tblname).fields(fldname).type

PAT's table 2:
in dao, the query type
querydefs(qryname).type

Again there are constants for the query types, such as
dbQSelect = 0
dbQCrossTab = 16
dbQDelete = 32
dbQAction = 240 (sum of bits 4 to 7 - 16+32+64+128)

so
if querydefs(qryname).type and dbQAction > 0 then it's an action query


I am not sure about these deeply hidden tables.
I eexpect they are not actually hidden to DAO. Try this and see if it brings then all up. You could seach the tdf,.name for "usys" or "msys" if you wanted to find those.

there are attribute values in the tables collection for things like
dbHiddenObject
dbSystemObject
dbAttachedTable is the constant for a linked table
dbAttachedODBC is the constant for a non-access table.

Maybe testing for those would identify the tables you are talking about.

code to show all tables
Code:
dim s as string
dim tdf as tabledef
s=""
for each tdf in currentdb.tabledefs
 s = s & tdf.name & vbcrlf
next
msgbox s
 

CJ_London

Super Moderator
Staff member
Local time
Today, 10:10
Joined
Feb 19, 2013
Messages
16,553

isladogs

MVP / VIP
Local time
Today, 10:10
Joined
Jan 14, 2017
Messages
18,186
Hi Dave

Of course you are correct that each of these values can be obtained from MS Dev Center (and much more besides) but that isn't quite the point here.

You can use each of the type & flags values in MSysObjects to do many things, not least get a list of all database objects and what each object actually is.

Knowing something is an action query isn't as useful as knowing what type of action query it is.

As for 'deep hidden' tables (my name for them), these are not the same as 'standard' hidden objects (Flags=8) in that it is impossible to view them in the navigation pane.

Thanks BUT I already know how several ways of getting a list of these deep hidden tables. I was deliberately not explaining how its done.
In fact I manipulated their properties in my security challenges in the example databases area.

In some cases, they can be viewed using a query and made into standard visible tables
However others cannot be viewed at all - - very deep hidden you could say!
e.g. the f_......(long string here) ...._Data table

Your list of attributes is on the right lines though there are other such attributes as well.

Attached is a fairly simple example ACCDE file I uploaded a couple of months ago.
It has a form & 2 deep hidden tables one of which can be made visible using the form. Its not difficult to find out what the other is called - more importantly can you read its contents?

And if you find that easy, please do try my 3 security challenges.
I'll follow up by PM or email in the next day or so
 

Attachments

  • DeepHideTablesExample.zip
    28.5 KB · Views: 159
Last edited:

NauticalGent

Ignore List Poster Boy
Local time
Today, 06:10
Joined
Apr 27, 2015
Messages
6,286
At the risk of sounding like a total idiot, it is posts like this one that has prompted me to dub Colin/Ridders/isladogs the Tom Riddle of AWF.

Don’t judge me too harshly, it is Friday and I am celebrating the wife’s promotion with copious amounts of the local wine.

Now, back to our regularary scheduled program...
 

isladogs

MVP / VIP
Local time
Today, 10:10
Joined
Jan 14, 2017
Messages
18,186
Ah but Access is all just black magic. :)

I believe he was a nice magician when he was still called Tom Riddle.
But then he sold his soul to the devil and turned into Voldemort.
Or are my memories of Harry Potter getting muddled?

Ahhh my precious .... Oops sorry that's Gollum

Congrats to Melissa on her promotion
 
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 10:10
Joined
Jul 9, 2003
Messages
16,245
Ah but Access is all just black magic. :)

I've referred to something happening in MS Access is being "magic" the other day but someone objected!



Sent from my SM-G925F using Tapatalk
 

isladogs

MVP / VIP
Local time
Today, 10:10
Joined
Jan 14, 2017
Messages
18,186
Ok then.
Access is just an enigma wrapped up in a (tom) riddle


Sent from my iPhone using Tapatalk
 
Last edited:

Users who are viewing this thread

Top Bottom