Results 1 to 8 of 8
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Table relationships (Access XP)

    <P ID="edit" class=small>(Edited by patt on 27-Apr-04 19:19. Add #2 Delete the realtionships)</P>I need to save the relationships that exist in a database, then delete them so I can delete all the tables, then import them from another database, add fields to these tables, and finally reinstate the relationships.
    I need to know:
    1. How to save the relationships, maybe in a temporary table.
    2. Delete all relationships.
    3. Reinstate the saved relationships.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table relationships (Access XP)

    The relationships can be edited through the DAO Relations collection; this is a property of the Database object. To learn about the Relations collection and the Relation object with its properties and methods, type CurrentDb.Relations into the Immediate window (in the Visual Basic Editor) and press F1. The online help contains several examples. To store a relationship, you must store the following properties:
    - Table: the name of the primary table
    - ForeignTable: the name of the foreign table
    - Fields: the collection of fields involved in the relationship.
    - Attributes: a long integer that specifies whether referential integrity is enforced etc.

    Note: the relationships are stored in the MSysRelationships table. This is a system table, to see it in the database window, you must tick System Objects in the View tab of Tools | Options... Although this table cannot be edited directly, you can query it.

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Table relationships (Access XP)

    Thanks Hans, that's certainly food for thought.
    I will peruse this doco tonight and see what I can make of it.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Table relationships (Access XP)

    Found the stuff you suggested and have successfully coded the following:
    1. Populated a especially created file to hold the relation details
    2. Deleted all relationships, although this was a bit strange, I had to put special code into this one.
    3. Re-established the relationships from the file of saved relation details.

    Thanks again for the advice Hans.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table relationships (Access XP)

    For our enlightenment, what was strange/special about the code to delete the relationships? Thanks.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Table relationships (Access XP)

    I'm breaking my own rules now.
    Here is the code:
    <pre> ' Delete all relationships
    DoItAgain:
    Set dbsidx = CurrentDb
    For intLoop = 0 To dbsidx.Relations.Count - 1
    If intLoop > dbsidx.Relations.Count - 1 Then GoTo DoItAgain
    Set rel = dbsidx.Relations(intLoop)
    dbsidx.Relations.Delete rel.NAME
    Next intLoop
    Set rel = Nothing
    Set dbsidx = Nothing
    </pre>

    The problem I was having was I had 6 relationships that were defined in the relationships window, but when the above code go to 3 it would say there were no more relationships left and would bomb on ' Set rel = dbsidx.Relations(intLoop)' command, so I put the if instruction in there to circumvent this problem.
    Did I need to use a refresh or something? Strange?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table relationships (Access XP)

    The usual way to do this is to loop backwards, otherwise you're pulling the rug from under the procedure, as it were:

    For intLoop = dbsidx.Relations.Count - 1 To 0 Step -1
    dbsidx.Relations.Delete dbsidx.Relations(intLoop).Name
    Next intLoop

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Table relationships (Access XP)

    Thanks Hans, I'll remember that.

Posting Permissions

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