Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Florence, Oregon, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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?

  2. #2
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: One to many - Multiple field keys (Access 2002/SP2)

    Hi there... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    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... <img src=/S/shy.gif border=0 alt=shy width=15 height=15>
    Attached Files Attached Files

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: One to many - Multiple field keys (Access 2002/SP2)

    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.

    <pre>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</pre>

    Charlotte

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: One to many - Multiple field keys (Access 2002/SP2)

    Trudi

    It works the same way in 2000 and 2002.
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    May 2002
    Location
    Florence, Oregon, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: One to many - Multiple field keys (Access 2002/SP2)

    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

  6. #6
    3 Star Lounger
    Join Date
    May 2002
    Location
    Florence, Oregon, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: One to many - Multiple field keys (Access 2002/SP2)

    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?

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: One to many - Multiple field keys (Access 2002/SP2)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •