Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    Manipulating Table Relationships (Access 97)

    Good evening loungers

    I am attempting to delete and then recreate a realtionship.
    This code pretty much is flogged from Access help.

    Code is falling over at appending the newly recreated relationship.

    Error msg states that object cannot be found.

    Any assistance greatly appreciated.

    Merry christmas

    Geof

    >>>>>>>>>>>>
    Sub relationsTest()
    Dim db As Database
    Dim rel As Relation
    Dim fld As Field
    Set db = CurrentDb
    For Each rel In db.Relations
    If rel.Table = "tblSiteAssessments" And rel.ForeignTable = "tblFaunaFlora" Then
    db.Relations.Delete rel.Name
    Exit For
    End If
    Next rel
    ' Create new relationship and set its properties.
    Set rel = db.CreateRelation("tblSiteAssessments", "tblFaunaFlora")
    ' Set Relation object attributes to enforce referential integrity.
    rel.Attributes = dbRelationDeleteCascade

    ' Create field in Fields collection of Relation object.

    Set fld = rel.CreateField("SurveyNumber")
    ' Provide name of foreign key field.
    fld.ForeignName = "SurveyNumber"
    ' Append field to Relation object and Relation object to database.
    rel.Fields.Append fld
    db.Relations.Append rel
    MsgBox "Relation '" & rel.Name & "' created."

    Debug.Print "Attributes of relations in " & db.Name & ":"
    For Each rel In db.Relations
    Debug.Print " " & rel.Name & " = " & rel.Attributes

    Next rel
    db.Close
    Set db = Nothing

    End Sub

    >>>>>>

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Manipulating Table Relationships (Access 97)

    Seems to me that you forget the name of the relationship:

    Set rel = db.CreateRelation("NameOfRelationship","tblSiteAssessments", "tblFaunaFlora")

    but I haven't test it.
    Francois

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    Re: Manipulating Table Relationships (Access 97)

    Good morning Francois
    Thank you
    Dohl!!!
    It was that simple. Working fine now.
    The perils of working when tired.

    Merry christmas

    Geof

Posting Permissions

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