Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-08-2018, 06:24 AM   #1
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,921
Thanks: 92
Thanked 1,701 Times in 1,579 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Purpose of System Tables

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)
Attached Files
File Type: zip SystemTableInfo.zip (30.1 KB, 31 views)

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following 3 Users Say Thank You to isladogs For This Useful Post:
Gasman (07-08-2018), jdraw (07-08-2018), The_Doc_Man (07-08-2018)
Old 07-08-2018, 03:22 PM   #2
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,464
Thanks: 62
Thanked 1,174 Times in 1,074 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Purpose of System Tables

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is online now   Reply With Quote
Old 07-08-2018, 09:34 PM   #3
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,431
Thanks: 13
Thanked 1,419 Times in 1,353 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Purpose of System Tables

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.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 07-08-2018, 10:04 PM   #4
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,847
Thanks: 55
Thanked 2,183 Times in 2,094 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Purpose of System Tables

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.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 07-09-2018, 12:12 PM   #5
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,921
Thanks: 92
Thanked 1,701 Times in 1,579 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: Purpose of System Tables

This started as a short answer but I kept adding to it.

Quote:
Originally Posted by arnelgp View Post
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...d.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...
Attached Images
File Type: png SysTables.PNG (28.9 KB, 307 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 07-09-2018 at 10:29 PM.
isladogs is offline   Reply With Quote
Old 07-29-2018, 07:50 AM   #6
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,921
Thanks: 92
Thanked 1,701 Times in 1,579 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: Purpose of System Tables

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
Attached Files
File Type: zip SystemTableInfo v2.zip (66.7 KB, 13 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 07-29-2018 at 02:44 PM.
isladogs is offline   Reply With Quote
Old 10-14-2018, 06:21 AM   #7
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,921
Thanks: 92
Thanked 1,701 Times in 1,579 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: Purpose of System Tables

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/p...les/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...

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

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 10-14-2018, 07:41 PM   #8
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,431
Thanks: 13
Thanked 1,419 Times in 1,353 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Purpose of System Tables

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 10-15-2018, 03:12 AM   #9
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,462
Thanks: 51
Thanked 949 Times in 918 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Purpose of System Tables

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.
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the top right of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 10-15-2018, 05:59 AM   #10
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,921
Thanks: 92
Thanked 1,701 Times in 1,579 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: Purpose of System Tables

Quote:
Originally Posted by gemma-the-husky View Post
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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 10-15-2018 at 06:59 AM.
isladogs is offline   Reply With Quote
Old 10-16-2018, 01:29 AM   #11
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,921
Thanks: 92
Thanked 1,701 Times in 1,579 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: Purpose of System Tables

Quote:
Originally Posted by Pat Hartman View Post
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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 10-18-2018, 07:58 PM   #12
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,431
Thanks: 13
Thanked 1,419 Times in 1,353 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Purpose of System Tables

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
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 10-19-2018, 12:41 AM   #13
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,921
Thanks: 92
Thanked 1,701 Times in 1,579 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: Purpose of System Tables

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...d.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/for...d.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.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 10-19-2018, 07:09 AM   #14
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,462
Thanks: 51
Thanked 949 Times in 918 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Purpose of System Tables

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
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the top right of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 10-19-2018, 07:35 AM   #15
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,437
Thanks: 40
Thanked 3,368 Times in 3,263 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Purpose of System Tables

the values do vary across systems - even dao and ado

for dao https://docs.microsoft.com/en-us/off...numeration-dao

for ado
https://www.w3schools.com/asp/ado_datatypes.asp

Boolean in dao is 1, in ado 11
Byte in DAO is 2, in ado 17 (and called adUnsignedTinyInt)


some are not an exact match

the dao Integer (3) and Long (4) are all adInteger (3) in ado

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Reply

Tags
purpose , system tables

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Booking system restaurant tables sammy204 General 4 03-08-2018 04:36 AM
Purpose of allowing relationships with Linked Tables? ions Tables 11 01-01-2011 01:55 PM
System tables SpookyDescendan Tables 5 10-25-2006 06:00 PM
Tables help needed, new system! Nitrous Tables 4 02-25-2004 04:11 AM
[SOLVED] Using "system tables" Knutjo Forms 3 01-24-2002 06:28 AM




All times are GMT -8. The time now is 07:06 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World