Last Updated 25 Feb 2022                                                                                       Difficulty level :   Advanced

Section Links: (this page)
          The MSysRelationships table
          The grbit field
          Example Joins
          Downloads


1.   The MSysRelationships table                                                                                                           Return To Top

The first two parts of this article explored how relationships are used with particular reference to referential integrity.

The article has also shown how the relationships window can get overcrowded resulting in the need to hide tables:


However, if the tables are hidden, how can we still keep a check on the relationships in use?
The answer is to query the hidden system table MSysRelationships where the information is stored

By default, system tables do NOT appear in the navigation pane.
To make the table visible temporarily, tick Show Hidden Objects and Show System Objects in Navigation Options

The table contains 8 fields – two of these (ccolumn and icolumn) can be ignored


IMPORTANT
System tables are used by Access 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
Altering one table may have 'knock on' effects on other tables

Incorrectly editing system tables may corrupt your database or prevent you opening it

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

It is ALWAYS important to take great care when viewing system tables.
However, the MSysRelationships table is READ ONLY so no damage can be done.

Even so, it is better to create a query than view the system table directly.
The MSys tables themselves have been excluded from this query and aliases used to clarify the purpose of each field

SELECT szRelationship AS RelationshipName, szObject AS TableName, szColumn AS FieldName,
    szReferencedObject AS ParentTableName, szReferencedColumn AS ParentFieldName,
    grbit AS RelValue
FROM MSysRelationships
WHERE (((szObject) Not Like 'MSys*'));

The query shows the relationship name together with the table and field names being joined.



2.   The grbit field                                                                                                                                     Return To Top

However, the most important field here is a long integer field 'grbit' which has been given the alias RelValue
The values indicate the type of join used and whether referential integrity is being enforced.

The 'base value' = 0 is for an inner join with referential integrity using primary key field in one table and a foreign key in the other table (1 to many).


The grbit (RelValues) are cumulative. Some examples include:


NOTE:
A SELF join linking the primary key field to another field will be 1 to many
Hence an inner self join = 1 (using RI) or 3 (without RI)



3.   Example Joins                                                                                                                                   Return To Top

Here are some examples. The RelValue column should be understandable with reference to the above data

a)   Two Inner Joins - no RI



b)   2 inner joins with RI (1-many) but no cascade update / cascade delete



c)   2 inner joins – one with 1-many RI but not cascade update/cascade delete ; other Indeterminate



d)   Inner 1:1 join on PK field – no RI



e)   Inner 1:1 join on PK fields – with RI and Cascade Update / Cascade Delete



f)   3 left joins (one 1-1; two 1-many) ; all with RI and cascade update/delete



g)   Outer self join without RI


h)   2 inner joins on linked tables – RI cannot be enforced in the front end db



If you are interested, you can use the supplied query qryRelationships in your own databases to explore other relationship types not covered above

I hope the above article has added to your understanding of relationships including the purpose of referential integrity as well as explaining how Access stores this information in the MSysRelationships system table.



4.   Downloads                                                                                                                                         Return To Top

Click to download:

      This article as a PDF file:                                   Relationships Advice

      The example database used in this article         MSysRelationships           Approx 1.5 MB (zipped)


Please use the contact form below to provide any feedback on this article including details of any errors or omissions



Colin Riddington           Mendip Data Systems                 Last Updated 25 Feb 2022