One to many – Multiple field keys (Access 2002/SP2)

Topic

New Reply

It is straightforward to establish a one to many relationship by linking the primary key in the one table to the foreign key in the many table. Access shows the relationship with a single arrow. How can I (in or out of VBA code) establish a one to many relationship from a table whose primary key consists of two contiguous fields (very common) to a table containing the same two fields as its foreign key?

Reply | Quote

Replies

Hi there…

This MAY be a waste of time since I only have Access 97 SR2 at work, but I’ll give you this answer and hope it’s close to how you do it in that version.

k… I’ve attached an example for you to see… Here’s how I do it…

1) For the One side of your relationship… from table design mode… you just select both fields (rows in the fld definitions) that will make up the key, right click and select primary key… (Make sure the indexing is as you want it…) … You should see the key next to both field names… Save the table design…
2) Then in the Many side… table design mode… Ensure that the two corresponding have indexing “Yes (Duplicated Ok)” … Save the table design…
3) Then add both tables… in order (tblOne first, then tblMany) to the relationships window… Drag the first field of the primary key in the One table over to the first foreign key in the Many table… A window should pop up where you define the relationships…. Fill in the second field of the primary key and the corresponding one in the Many table… Check the “Enforce Referential Integrity” option… Click OK… As soon as the window disappears you should see the symbols for One-To-Many relationships in both places…

All done…

Hope this makes sense… and HOPE it works the same in 2002… Let me know how it goes… I don’t get the opportunity to answer questions on here often…

Reply | Quote

Trudi

It works the same way in 2000 and 2002.

Reply | Quote

Trudi,
Thank you very much for the information. It worked exactly as you said it would. Now the relationship window shows two one to many connection link lines one on top of the other. I proved, I think, that the two links are parts of one combined link: deleting one link (line) deleted them both. (Incidentally, while I would have liked to see from your .mdb attachment if your example looked like what I have, I was unable to open it. It seems that when Access 2002 opens an order DB for the first time it has to be able to write to it. I got an error saying that the DB was read only.)

Thanks again, John

Reply | Quote

Are you working with ADO or DAO? Here’s an ADO routine that demonstrates creating a relationship in code. If you are working with a multifield key, you add the individual fields by appending both columns.

Sub CreateRelationship(strDBPath As String, _
                       strForeignTbl As String, _
                       strRelName As String, _
                       strFTKey As String, _
                       strRelatedTbl As String, _
                       strRTKey As String)
                    
    Dim catDB As ADOX.Catalog
    Dim tbl   As ADOX.Table
    Dim key   As ADOX.key
    ' Note that VB "enforces" the lowercasing of
    ' "key" in ADOX.key.
    
    Set catDB = New ADOX.Catalog
    ' Open the Catalog object.
    catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source =" & strDBPath

    Set key = New ADOX.key
    ' Create the foreign key to define the relationship.
    With key
        ' Specify name for the relationship in the Keys collection.
        .Name = strRelName
        ' Specify the related table's name.
        .RelatedTable = strRelatedTbl
        .Type = adKeyForeign
        ' Add the foreign key field to the Columns collection.
        .Columns.Append strFTKey
        ' Specify the field the foreign key is related to.
        .Columns(strFTKey).RelatedColumn = strRTKey
    End With
    
    Set tbl = New ADOX.Table
    ' Open the table and add the foreign key.
    Set tbl = catDB.Tables(strForeignTbl)
    tbl.Keys.Append key
    
    Set catDB = Nothing

End Sub

Reply | Quote

Charlotte,
Thank you also for your coded answer. Since I am trying to break into ADO, your code will be studied. Incidentally, as my background in Access is Versions 1 and 2 (a few years ago), can you recommend a book with which I can learn ADO as it applies to Access?

Reply | Quote

There’s one from WROX Press on Programming Access ADO, but I don’t recall the exact title. If you do a search on books in this forum, you’ll find previous discussions on book recommendations.

Reply | Quote