Access System tables: exactly what do they store?

[CathyBG]

Does anyone know of a source of information about the Access system tables? What each one of them tracks and what the fields mean? I have found a couple with date stamps, but the other fields are not helpful. I also suspect there are more system tables than just the ones labeled "msys" that you can see. One in particular I am curious about may be called "AccessLayout" - it appeared in the MSysObject table with a date stamp and all the other names in that field were identifiable system objects like queries.

I have had some really ugly corrupted database problems the last few months with a pre-existing Access 2002 database for a client. "Somehow" the database gets corrupted and is not reparable and the only recourse ends up being to create an empty and import everything, check the references, recompile and go on your merry way. However, I find that it is often well nigh impossible to find out what caused the particular corruption. You can never get a useful answer to the quesiton "What did you do right before the database crashed?" I suspect the system tables could shed some light on what happened last in the database if I knew how to read them.

Any ideas? Thanks!
cathybg

 

[The_Doc_Man]

Well, unfortunately the msys tables don't tell you much about the history of things, so while it never hurts to know what is in these tables, it might not help your problem in corruption forensics.

The Msys tables hold a couple of useful things. Like

MsysObjects holds the name of EVERY collected object in the database. (By that I mean, if it exists in a collection, it appears in the msys table, pretty much. Fields, tables, document collections, etc.) Plus the properties of the database as a whole. Plus pointers to implied queries - such as the implied query sitting behind a combo box that gets its data lists from another table.

MsysACEs holds the Access Control Entries (protections) of every object.

MsysQueries holds the names of fields appearing in queries. Also the components of the WHERE clauses. Also JOIN info. (Only pre-defined queries, not dynamic ones.)

MsysRelationships holds the names and host tables for every field for which a relation has been declared, along with info about the nature of the relations. Like one-to-many, relational integrity, cascade delete, etc. In fact, the table holds the field names and host tables twice in different order, one record for each direction of the relationship. I.e. one record for the relationship as seen from the one side and a different record for the same relationship as seen from the many side.

MsysIMEXSpecs and MsysIMEXColumns hold stored import or export specifications, column by column and rule by rule, including skipped columns, delimiters, date formats, column formats, etc.

God alone (or maybe God and Pat Hartman, not necessarily in that order) knows what is in MsysAccessObjects. Mostly looks like huge binary objects that are totally incomprehensible when you open the table.

 

[The_Doc_Man]

Yeah, but you DO play bridge. Certainly a plus in my book!

For what it's worth, the N'Awlins area isn't exactly filling the classified ads with info.tech. jobs, either.