The relationships window in Access is a graphical interface to help users identify relationships between tables.
Behind the scenes Access is creating/modifying/deleting Constraints based on user actions. You can create relationships with SQL. Access names the relationships.
From MSoft:
A primary key is the field (or fields) that is used to uniquely identify each record in a table. There are three requirements for a primary key: It cannot be null, it must be unique, and there can be only one defined per table. You can define a primary key either by creating a primary key index after the table is created, or by using the CONSTRAINT clause in the table declaration, as shown in the examples later in this section. A constraint limits (or constrains) the values that are entered in a field.
A foreign key is a field (or fields) in one table that references the primary key in another table. The data in the fields from both tables is exactly the same, and the table with the primary key record (the primary table) must have existing records before the table with the foreign key record (the foreign table) has the matching or related records. Like primary keys, you can define foreign keys in the table declaration by using the CONSTRAINT clause.
Here is a routine to print relationship info to the immediate window.
Code:
'---------------------------------------------------------------------------------------
' Procedure : ShowRelations
' Author : mellon
' Date : 11/06/2015
' Purpose : To show the relations in the current database.
' WHERE:
' RelationName is the name assigned to the relation
' TableName is the From Table Name
' Foreign Table/To Table is the name of the To Table
' FieldName is the name of the field (PK in From Table)
' ForeignFieldName is the name of the field (FK in the To Table/Foreign Table)
'---------------------------------------------------------------------------------------
'
Function ShowRelations(Optional stablename As String = "")
Dim db As DAO.Database
Dim rel As DAO.Relation
Dim fld As DAO.Field
Dim errNoRelation As String
10 errNoRelation = " No relation exists for table (" & stablename & ")"
20 On Error GoTo ShowRelations_Error
30 Set db = CurrentDb()
40
50 If stablename & "" <> "" Then
60 For Each rel In db.Relations
70 If rel.Table = stablename Or rel.ForeignTable = stablename Then
80 errNoRelation = ""
90 Debug.Print vbCrLf & "RELATION NAME :" & rel.name & vbCrLf & vbTab & vbTab & "FROM TABLENAME: " & rel.Table & " TO TABLENAME: " & rel.ForeignTable
100 For Each fld In rel.Fields
110 Debug.Print vbTab & vbTab & vbTab & " FieldName: " & fld.name & " ForeignFieldName: " & fld.ForeignName
120 Next fld
130 End If
140 Next rel
150 Debug.Print errNoRelation
160 Else
'"No table name provided so list all relations including MSys System"
170 For Each rel In db.Relations
180 Debug.Print vbCrLf & "RELATION NAME :" & rel.name & vbCrLf & vbTab & vbTab & "FROM TABLENAME: " & rel.Table & " TO TABLENAME: " & rel.ForeignTable ', rel.Attributes"
190 For Each fld In rel.Fields
200 Debug.Print vbTab & vbTab & vbTab & " FieldName: " & fld.name & " ForeignFieldName: " & fld.ForeignName
210 Next fld
220 Next rel
230 End If
240 On Error GoTo 0
250 Exit Function
ShowRelations_Error:
260 MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ShowRelations "
End Function
In my database I have a number of tables and relationships.
Here are a few examples of how I call the function along with some output
Code:
Sub showRelationsforTable()
ShowRelations ("tblagency")
End Sub
Result:
Code:
RELATION NAME :tblAgencyContactstblAgency
FROM TABLENAME: tblAgencyContacts TO TABLENAME: tblAgency
FieldName: ContactID ForeignFieldName: PrimeContactFK
RELATION NAME :tblAgencytblAgencyContacts
FROM TABLENAME: tblAgency TO TABLENAME: tblAgencyContacts
FieldName: AgencyID ForeignFieldName: AgencyIDFK
Sample where the Table does not exist or there is no relationship involving the supplied table name
Code:
Sub showRelationsforTable()
ShowRelations ("thisTableDoesNotExist")
End Sub
Result:
Code:
No relation exists for table (thisTableDoesNotExist)
Example without a supplied tableName -by default this lists all relationships in the database
Code:
Sub showRelationsforTable()
ShowRelations ("")
End Sub
OR
Sub showRelationsforTable()
ShowRelations
End Sub
Result (partial to save space)
Code:
RELATION NAME :MSysNavPaneGroupCategoriesMSysNavPaneGroups
FROM TABLENAME: MSysNavPaneGroupCategories TO TABLENAME: MSysNavPaneGroups
FieldName: Id ForeignFieldName: GroupCategoryID
RELATION NAME :MSysNavPaneGroupsMSysNavPaneGroupToObjects
FROM TABLENAME: MSysNavPaneGroups TO TABLENAME: MSysNavPaneGroupToObjects
FieldName: Id ForeignFieldName: GroupID
RELATION NAME :tblAgencyContactstblAgency
FROM TABLENAME: tblAgencyContacts TO TABLENAME: tblAgency
FieldName: ContactID ForeignFieldName: PrimeContactFK
RELATION NAME :tblAgencytblAgencyContacts
FROM TABLENAME: tblAgency TO TABLENAME: tblAgencyContacts
FieldName: AgencyID ForeignFieldName: AgencyIDFK
RELATION NAME :tblEventXjnctblPatientEvent
FROM TABLENAME: tblEventX TO TABLENAME: jnctblPatientEvent
FieldName: EventID ForeignFieldName: EventId
RELATION NAME :tblFactorytblFactoryWidgetProduction
FROM TABLENAME: tblFactory TO TABLENAME: tblFactoryWidgetProduction
FieldName: FactoryID ForeignFieldName: FactoryIDFK
RELATION NAME :tblPatientXjnctblPatientEvent
FROM TABLENAME: tblPatientX TO TABLENAME: jnctblPatientEvent
FieldName: patientID ForeignFieldName: PatientID
.......