A question about clarity in the relation window

[magnusstefan]

Hi!

First I must explain that I know what relations are, and that I can design and implement them in Access even if I really do not know everything about them. OK?

The scenario, and then the question:
Suppose someone wants you to look at an Access database and check that the relations are sound, especially when it concerns the primary and foreign keys. You open the relation window and look at it. You identity all the primary keys from the little key icon in the beginning of each tables primary key field and then, I suppose, you identify the foreign keys by looking at the relation lines going from the primary key in any table to a field in another table. And that field is the foreign key field. There is no sign otherwise of the field being a foreign key. There is, for instance, not an icon of a smaller key in the beginning of the foreign key field.

Also, if you open the table with the foreign key in design mode, there is nothing there that can tell you that a certain field has the role of foreign key.

Now the question:
Have I understood this correctly: There are no other way to identify the actual foreign keys in an Access database than using the "follow the line from the primary key"-method in the relation window?

[ranman256]

I wouldn't go overboard picking out EVERY possible relation. The ONLY needed ones are parent-child.
like client-to-payments,
or
order-to-[items ordered]

linking up tables to say, State list...":my client lives in a state,so I must link the states table"...don't bother. I find these non-vital links just cause more work, and a pain if I have to change it later.
Stick with the parent-child relates only.

[ItsMe]

...Have I understood this correctly: There are no other way to identify the actual foreign keys in an Access database than using the "follow the line from the primary key"-method in the relation window?

I suppose the simple answer is, yes. This is how it is in Access.

In a table, you will not have more than one PK (Although you can create multiple PK's in Access, they do not behave as Primary Keys. Primary Keys have a specific definition and there can only be one within a given table). In that same table, you may have several FK's or no FK's (FK's will not, typically, have a Key Icon that defines them. They are simply another field and the data type will match the PK in the parent table. This is usually Long Integer to match an Autonumber type).

If you are drawing a line between one table and another, the first field listed within the table should be the PK and subsequent key fields will be another type of 'Key'. So, a line would not connect the first field listed in each of the two tables. Having said that, this is not something Access controls for you. You need to place your fields in a particular order while in Design View of your tables. For Access, my preference is to name Key fields that are either PK or FK with a convention that ends with Key. So I will start with the table name and end with Key (tblOrders would have a PK of OrderKey and then several FK fields with something like CustomerKey, ProductKey, etc.). I used to use a convention like CustomerPK for the parent and then CustomerFK in the child tables but it becomes difficult to manage when writing complex SQL.

[magnusstefan]

Absolutely, I agree. I mostly use the parent-child relation, and I think everyone knowing the game a bit does. (I normally think of it as one-to-many, but I know that it is called parent-child in Access, even if I do not know exactly why.) As far as I know the typical example is Order-to-Orderdetails and I suppose that Order-to/[Items ordered] is just another description of it.

But my question was not about which relations to use, but rather how to be able to observe the nature of the relations already in place in a database created by someone else! And more specifically: How do I observe what keys are used, and in which tables, and in which fields?

[magnusstefan]

I suppose the simple answer is, yes. This is how it is in Access.
Actually, you are a mind reader. I am always happy if I get the simple answer first!

In a table, you will not have more than one PK (Although you can create multiple PK's in Access, they do not behave as Primary Keys. Primary Keys have a specific definition and there can only be one within a given table). In that same table, you may have several FK's or no FK's (FK's will not, typically, have a Key Icon that defines them. They are simply another field and the data type will match the PK in the parent table. This is usually Long Integer to match an Autonumber type).

Yes, I actually did know most of that, but some repetition of important facts are of course often a good thing! The new thing for me was that you should preferably use Long Integer in a FK to match a PK with Autonumber type. I did found that very interesting! Can you please explain this a little further? I mean, why should we do it that way, and what happens if we not do it?

If you are drawing a line between one table and another, the first field listed within the table should be the PK and subsequent key fields will be another type of 'Key'. So, a line would not connect the first field listed in each of the two tables. Having said that, this is not something Access controls for you. You need to place your fields in a particular order while in Design View of your tables. For Access, my preference is to name Key fields that are either PK or FK with a convention that ends with Key. So I will start with the table name and end with Key (tblOrders would have a PK of OrderKey and then several FK fields with something like CustomerKey, ProductKey, etc.). I used to use a convention like CustomerPK for the parent and then CustomerFK in the child tables but it becomes difficult to manage when writing complex SQL.

I already place the fields in the way you describe, so that is also clear. But your suggestion for naming Key-fields are new to me, and I find it excellent, and I will start to use it at once! (Never heard the suggestion before, but when I see it it at once strikes me as really elegant and efficient.)

[orange]

magnusstephan,

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 function to print relationship info to the immediate window.

'---------------------------------------------------------------------------------------
' 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

Sub showRelationsforTable()
    ShowRelations ("tblagency")
End Sub

Result:

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

Sub showRelationsforTable()
    ShowRelations ("thisTableDoesNotExist")
End Sub

Result:

 No relation exists for table (thisTableDoesNotExist)

Example without a supplied tableName -by default this lists all relationships in the database

Sub showRelationsforTable()
    ShowRelations ("")
End Sub

OR

Sub showRelationsforTable()
    ShowRelations  
End Sub

Result (partial to save space)

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

[ItsMe]

...The new thing for me was that you should preferably use Long Integer in a FK to match a PK with Autonumber type. I did found that very interesting! Can you please explain this a little further? I mean, why should we do it that way, and what happens if we not do it?...

I don't know that I have a perfect answer. I do not remember why I use Long Integer other than I believe this is the best data type to match the Autonumber. In other words I do not remember where I learned to do it this way.

As for what will happen if you use another option? Well, if you use an Integer type, you will limit yourself to just over 32,000 records. Or, maybe less if your first PK is 2356. Now that I think about it, I have used Integer for some tables. For instance, a table that lists states, or gender selection would not require a larger data type. This may help with efficiency of your DB.

Beyond that, there is not a need for double data type or larger than double because you are not dealing with fractions.

[magnusstefan]

This is just beautiful!

The first complex code example I only understand partially. But I understand the rest, and I really this is most interesting!
(My intention is to learn the basics oF VBA, and then come back with an answer. Even if I succeed, that will naturally take some time, so we can postpone this very discussion until that moment...)

And thank you for your kind interest!

[magnusstefan]

OK!

Duly noted, and thank you for the clarification!

[CJ_London]

That information can also be determined by interrogating the MSysRelationships table.

You can also determine the type of join (enforced integrity or not, cascade options and inner/left/right) - Which I expect are also available in the relation properties

[magnusstefan]

That information can also be determined by interrogating the MSysRelationships table.

You can also determine the type of join (enforced integrity or not, cascade options and inner/left/right) - Which I expect are also available in the relation properties

Yes, that's interesting!

I looked at the table and tried to understand. For the columns "ccolumn", "grbit" and "icolumn" I couldn't come to any conclusion at the moment. But other columns seems more understandable: SzreferencedObject = the table with the PK. SzreferencedColumn = the field with the PK. SzObject = the table with FK, Szcolumn = the field with the FK.

I could not figure out anything about the integrity, cascade and joins at this moment. I also tried to search the net for a detailed explanation of this table, but did not succeed...

[CJ_London]

I could not figure out anything about the integrity, cascade and joins at this moment

that is column grbit

the value has a form of binary progression - to check the values, in the relationship window create a relationship between two tables. Then look ar grbit - if it is a simple 'JOIN' the value will be 0. Now change the join to Enforce Referential Integrity - the value will change. Now modify join to cascade update related fields and grBit will change again. It will have a different value for each possible permutation.

Not sure about ccolumn and and icolumn - I've not played around with it much. Perhaps something to do with linked/odbc tables....

[orange]

For more details, and some code to use as your starting point

see:
http://allenbrowne.com/AppRelReport.html and
http://allenbrowne.com/AppRelReportCode.html

More info from M$oft re RelationAttributeEnum

RelationAttributeEnum Enumeration (DAO)

                               Office 2010              
                                                                Other Versions                              
                             
       
            
   
                                             Used with the Attributes property to determine attributes of a Relation object. 
         
                                 
                     Name
                     Value
                     Description

                                            dbRelationDeleteCascade
                     4096
                      Deletions cascade

                                            dbRelationDontEnforce
                     2
                     Relationship not enforced (no referential integrity)

                                            dbRelationInherited
                     4
                     Relationship exists in the database containing the two linked tables

                                            dbRelationLeft
                     16777216
                     Microsoft Access only. In Design view, display a LEFT JOIN as the default join type.

                                            dbRelationRight
                     33554432
                     Microsoft Access only. In Design view, display a RIGHT JOIN as the default join type.

                                            dbRelationUnique
                     1
                     One-to-one relationship

                                            dbRelationUpdateCascade
                     256
                     Updates cascade

My code in post #6 was trying to show some relationship info without depending on the relationships window.
FieldName and ForeignFieldName, as mentioned in the comments, was to show PK/FK.

The naming and lack of documentation on MSysRelationships makes the table very difficult for most Access users/developers.

If you do decide to create some materials to shed more light on Relationships, please post same so we all can learn.
Good luck with your project.

[magnusstefan]

"If you do decide to create some materials to shed more light on Relationships, please post same so we all can learn.
Good luck with your Project."

Thanks' for the clarification! Unfortunately I must leave this subject for now, but I will of course came back and publish any findings of mine if there is time for it later!