SQL for Query to list Relationships in Access database

Quick Jump

Download

Download zipped TXT file that you can copy from to get SQL to make queries showing relationship information between tables stored in Access locally (not linked): Query_SQL_Relationships_s4p__TXT.zip

Download zipped BAS file that you can import with a function to expand the relationships attribute into readable text: mod_GetRelationshipTypes_s4p__BAS.zip

If you have trouble with the downloads, you may need to unblock the ZIP file, aka remove Mark of the Web, before extracting the file. Here are steps to do that: https://msaccessgurus.com/MOTW_Unblock.htm

SQL

qRelationships_s4p

Here is SQL for a query to show relationship information from MSysRelationships -- needs VBA for GetRelationshipTypes. If you don't want to use any VBA, delete the RelTypes column that uses the GetRelationshipTypes function.

This SQL assumes ONE primary key field relating to a single foreign key field.

SELECT m.szReferencedObject AS Table_Parent
, m.szObject AS Table_Child
, m.szReferencedColumn AS FieldName_Parent
, m.szColumn AS FieldName_Child
, m.grbit AS RelType
, GetRelationshipTypes([grbit]) AS RelTypes
FROM MSysRelationships AS m
WHERE ( m.szReferencedObject Not Like "MSys*")
ORDER BY m.szReferencedObject
, m.szObject;

qRelationships_mutipleFields_s4p

However, if you've used natural keys and might have more than one field in a relationship, you can use this:

SELECT m.szReferencedObject AS Table_Parent
, m.szRelationship AS RelationName
, m.szObject AS Table_Child
, m.szReferencedColumn AS FieldName_Parent
, m.szColumn AS FieldName_Child
, m.icolumn AS ColNum
, m.grbit AS RelType
, GetRelationshipTypes([grbit]) AS RelTypes
FROM MSysRelationships AS m
WHERE ( m.szReferencedObject Not Like "MSys*")
ORDER BY m.szReferencedObject
, m.szRelationship
, m.icolumn;

Results

qRelationships_s4p

This is run on MyContacts, which is a free download from MsAccessGurus for keeping track of contact information

Steps to create a new query from SQL

1. Create ribbon
2. from Queries group, choose Query Design
3. right-click in top pane for sources and, from shortcut menu choose SQL View, or switch to SQL View using the Home or Query Design ribbon
4. copy the SQL and paste
   be sure not to keep any characters before the word SELECT, or after the terminating semi-colon ;
5. switch to Datasheet View to look at results
6. save the query if you want

VBA for function to list relationship attributes

'for query to show relationship type information
'from MSysRelationships

'*************** Code Start *****************************************************
' Purpose  : Get relationship types
'              from MSysRelationships.grbit
' Author   : crystal (strive4peace)
' Code List: msaccessgurus.com/code.htm
' This code: http://msaccessgurus.com/VBA/Query_SQL_ShowRelationships.htm
' LICENSE  :
'   You may freely use and share this code, but not sell it.
'   Keep attribution. Use at your own risk.
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'           GetRelationshipTypes
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'   Attribute                 Value
'   dbRelationUnique          1
'   dbRelationDontEnforce     2
'   dbRelationInherited       4
'   dbRelationUpdateCascade   256
'   dbRelationDeleteCascade   4096
'   dbRelationLeft            16777216
'   dbRelationRight           33554432
Public Function GetRelationshipTypes(pAttributes As Long) _ 
   As String 
'221015 strive4peace
   Dim vText As Variant 
   vText = Null 
   
   If pAttributes = 0 Then 
      GetRelationshipTypes =  "EnforceRI"
      Exit Function 
   End If 
   'Enforce Referential Integrity, or not
   If (pAttributes And dbRelationDontEnforce) = _ 
         dbRelationDontEnforce Then 
      vText =  "DontEnforceRI"
   Else 
      vText =  "EnforceRI"
   End If 
   'Right or Left Join
   If (pAttributes And dbRelationRight) = _ 
         dbRelationRight Then 
      vText = (vText +  ", ") &  "Right"
   Else 
      If (pAttributes And dbRelationLeft) = _ 
            dbRelationLeft Then 
         vText = (vText +  ", ") &  "Left"
      End If 
   End If 
   'Cascade Update
   If (pAttributes And dbRelationUpdateCascade) = _ 
         dbRelationUpdateCascade Then 
      vText = (vText +  ", ") &  "CascadeUpdate"
   End If 
   'Cascade Delete
   If (pAttributes And dbRelationDeleteCascade) = _ 
         dbRelationDeleteCascade Then 
      vText = (vText +  ", ") &  "CascadeDelete"
   End If 
   'Inherited
   If (pAttributes And dbRelationInherited) = _ 
         dbRelationInherited Then 
      vText = (vText +  ", ") &  "Inherited"
   End If 
   'Unique
   If (pAttributes And dbRelationUnique) = _ 
         dbRelationUnique Then 
      vText = (vText +  ", ") &  "Unique"
   End If 
   
   GetRelationshipTypes = vText 
End Function 
'*************** Code End ******************************************************

Reference

MyContacts

MyContacts

Microsoft Help

Help: And operator

Backstory

When you want to document relationships that Access knows about, this is handy SQL. If database is linked to a back-end, put this in your back-end.

If you like this page, please let me know, thank you. Donations are always appreciated

Share with others

Get Help with Access

Let's connect and team-develop your application together. I teach you how to do it yourself. My goal is to empower you.

While we build something great together, I'll pull in code and features from my vast libraries as needed, cutting out lots of development time. I'll give you lots of links to good resources.

Data structure is the most important thing to get right! With good structure and relationships, the rest is downhill. I'm happy to help. Email me at training@msAccessGurus.com

~ crystal

the simplest way is best, but usually the hardest to see