[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2577
  • Last Modified:

MSysObjects table Info needed

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:

Tables=1
MSysDb=2
List of Object types=3 (Includes an object called a Script??)
Queries=5
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?
Forms=-32768
Reports=-32764
Macros=-32766
Modules=-32761
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?



0
Jeffrey Coachman
Asked:
Jeffrey Coachman
  • 3
  • 2
1 Solution
 
RyanDeMouginCommented:
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,
-Ryan
0
 
RyanDeMouginCommented:
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.
0
 
Jeffrey CoachmanAuthor Commented:
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:
Connect
Database
DateCreate
DateUpdate
Flags
ForeignName
ID (Primary Key)
Lv
LvExtra
LvModule
LvProp
Name
Owner
ParentID
RmtInfoLong
RmtInfoShort
Type

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

Thanks!
0
 
RyanDeMouginCommented:
boaq2000,

   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.

-R
0
 
Jeffrey CoachmanAuthor Commented:
RyanDeMougin,

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.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now