Results 1 to 2 of 2

vba to show Relationships --Primary Key and Foreign Key

  1. #1
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    13,808

    vba to show Relationships --Primary Key and Foreign Key

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

  2. #2
    Ajax is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    6,862
    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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 09-09-2014, 01:39 AM
  2. Primary Key and Foreign Key Question
    By chrisbas in forum Access
    Replies: 1
    Last Post: 05-07-2012, 04:01 PM
  3. primary/foreign keys
    By st1300 in forum Database Design
    Replies: 38
    Last Post: 12-04-2011, 01:27 PM
  4. Primary and foreign key in relationships?
    By Fatbot in forum Access
    Replies: 1
    Last Post: 04-12-2011, 10:11 AM
  5. Primary/foreign keys + relationships
    By tripptofer in forum Database Design
    Replies: 0
    Last Post: 08-12-2009, 07:44 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums