MSysObjects table Info needed

Posted on 2005-04-14
Last Modified: 2007-12-19
I run a query on the MSysObjects table in my main database to do my own searching.

From this query I generate a report of all objects matching my criteria.

Here is what I determined so far about the "Type" field in the MSysObjects table:

List of Object types=3 (Includes an object called a Script??)
Linked tables=6
Type "8" appears to have something to do with relationships. I have 2 objects in my database that are type 2
"CategoriesProducts" and "SuppliersProducts". these are relationships between the ONE tables: Suppliers and Categories, and the MANY table "Products". Yet there are similar relationships like this in my database, yet only these two are listed?
Data Access Pages=-32756

Which leads me to my question:
Does anyone know where I can get more information on the  MSysObjects table?
I have heard that it is undocumented. But some ones gotta know what Type 8 really means. And what is a "Script"? Scrips are not listed in the database window?

Question by:Jeffrey Coachman
    LVL 2

    Accepted Solution

    From what I can tell from Looking at the MSysObjects table in one of my databases, type 8 is a relationship.  All the type 8 records in my table have the 'Relationships' object listed as the parent.  
    What I don't understand though is, is that in the MSysRelationship table, these relationship are listed again and I can find no linkage between the two tables.  The field grbit appears to be a bitfield representing different options for the relationship such as cascade updates, one-to-many, one-to-one, etc.  

    Perhaps each relationship is recorded twice for some unknown purpose.  It doesn't look like there are many specifics about the relationship stored in the MSysObject table.

    Hope this helps,
    LVL 2

    Expert Comment

    Here's what I'm 99% sure the field grbit represent in MSysRelationship

    DoNotEnforceReferentialIntegrity = 2^1
    JoinType2 = 2^24
    JoinType3 = 2^25
    CascadeUpdateRelatedFields = 2^8
    CascadeDeleteRelatedFields = 2^12

    I would say grbit is of type long (32 bits).  So, we've got 5 of the 32 bits defined.  What the other 27 are or if they are unused or not, I don't know.

    I scanned all of the other system tables for occurances of the UID of the relationships listed in MSysObjects but found none.  Perhaps they are just listed in MSysObjects so that the system knows they are there, without knowing anything about them without checking the realtionship  table.  

    If you open MSysObjects table, and open the relationships form, then delete or otherwise change a relationship, the record in MSysObjects representing the relationship will be deleted and replaced with a new record.  You can see it get marked #deleted.  This also happens in the relationships table.

    Basically, what you just got where my observations.  Hope this helps.
    LVL 74

    Author Comment

    by:Jeffrey Coachman
    Thanks for the info.

    One thing though, I am running Access 2000 (9.0.2720) and I do not have a "grbit" field in my MSysObjects table?

    Here are my Fields:
    ID (Primary Key)

    Perhaps the "grbit" field is one of the above, don't worry though you have given me lots of info so far

    LVL 2

    Expert Comment


       The grbit field is in the MSysRelationships system table.  Which as far as I can tell is where the complete definition of a relationship is.  I beleive that in MSysObjects, relationships are only listed as 'being in the database', nothing much more.

      Are you planning on documenting and publishing this info on Access system tables?  Please keep me updated on how this goes.  I can appreciate this type of endeavor.

    LVL 74

    Author Comment

    by:Jeffrey Coachman

    I was, at first, only interested in this project because I needed the info for my own database. But since then I have become more curious because of the lack of information out there.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now