Checking for or Re-Establishing Relationships

[sterlecki]

I am seeking advice. My application is extremely dependent on having a one to many relationship from my main table to ten other tables. I want to be sure that when the application is opened that I have NOT lost my relationships before the user starts working.

I had this happen in testing and noticed some strange things in the data. Upon checking the relationships sure enough they were gone.

I have developed some code to recreate all the relationships and it works perfectly. (For a Change)

[!]My Question/s: Is running this code a good practice?

On what event should this occur? The app opens to a switchboard menu and from there the user chooses to either edit various lookup tables/forms or go to the main user interface.

What would happen if this code runs in a multiuser situation? [/!]

Thanks for any insight you can give!

 

[PHV]

Which code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sterlecki]

Ahh the Code! I was thinking about getting advice on the concept but you're right, this may depend on the code.

Your thoughts PHV?

Option Compare Database
Option Explicit

Const adhcErrObjectExists = 3012


'Modified from:
' From Access 2000  Developer's Handbook
' by Getz, Litwin, and Gilbert. (Sybex)
' Copyright 1999. All Rights Reserved.

Function CreateRelationship() As Boolean


Dim db As DAO.Database
    Dim rel As DAO.Relation
    Dim fld As DAO.Field
    Dim strTablePrimary As String
    Dim varTableListMany As Variant
    Dim varTableListOne  As Variant
    Dim varTableName As Variant
    Dim i As Variant

On Error GoTo CreateRelationship_Err

    Set db = CurrentDb()

'List of Tables that have a one to Many relationship with data_WellHeader table


varTableListMany = Array("data_WellContacts", "data_BH_Images", "data_CasingAndCement", "data_DistributionEmails", _
                    "data_FormationTops", "data_GeoHazard", "data_LogEval", "data_LogEvalDetail", _
                    "data_LogVendorContacts", "data_MudProgram")

'List of Tables that have a one to one relationsip with data_WellHeader table

varTableListOne = Array("data_PermitsAndRegulatory", "data_DirectionalSpecs", "data_DrillingSpecs", _
                "data_Pinedale_PipeSetCriteria ")

strTablePrimary = "data_WellHeader"



   
'Create the Relationships that have a one to many relationsip with data_WellHeader table
  For Each varTableName In varTableListMany

        i = varTableName

 ' Create the new relation object.
    Set rel = db.CreateRelation()

        With rel
             .Name = strTablePrimary & "_" & varTableName

         .Table = strTablePrimary
             .ForeignTable = varTableName
             .Attributes = dbRelationUpdateCascade Or dbRelationDeleteCascade
    End With


' Set the relation's field collection.
    Set fld = rel.CreateField("API")
    ' What field does this map to in the OTHER table?
    fld.ForeignName = "API"
    rel.Fields.Append fld
    ' You could append more fields, if you needed to.

    ' Append the relation to the database's
    ' relations collection.


    db.Relations.Append rel
    CreateRelationship = True
    db.Relations.Refresh
'Debug.Print varTableName, "   " & rel.Name & "one to Many"
Next varTableName

  For Each varTableName In varTableListOne
  
        i = varTableName

 ' Create the new relation object.
    Set rel = db.CreateRelation()

        With rel
             .Name = strTablePrimary & "_" & varTableName
             
         .Table = strTablePrimary
             .ForeignTable = varTableName
             .Attributes = dbRelationUnique Or dbRelationUpdateCascade Or dbRelationDeleteCascade
    End With

    
' Set the relation's field collection.
    Set fld = rel.CreateField("API")
    ' What field does this map to in the OTHER table?
    fld.ForeignName = "API"
    rel.Fields.Append fld
    ' You could append more fields, if you needed to.

    ' Append the relation to the database's
    ' relations collection.
    
    
    db.Relations.Append rel
    CreateRelationship = True
    db.Relations.Refresh
'Debug.Print varTableName, "   " & rel.Name & " One to One"
Next varTableName

'keeps from duplicating a table in the relationship view and handles a special realationship

Call RELATIONSHIP_DELETE
Call DATABASE_RELATIONSHIP_ADD


CreateRelationship_Exit:
    Exit Function

CreateRelationship_Err:
    Select Case Err.Number
        Case adhcErrObjectExists
            ' If the relationship already exists,
            ' just delete it, and then try to
            ' append it again.
            db.Relations.Delete rel.Name
            Resume
        Case Else
            MsgBox "Error: " & Err.Description & _
             " (" & Err.Number & ")"
            CreateRelationship = False
            Resume CreateRelationship_Exit
    End Select
    
End Function

'Function to Delete relationship to data_wellContacts from lkup_QEPContacts

Function RELATIONSHIP_DELETE()

On Error GoTo RELATIONSHIP_DELETE_Err


DoCmd.SetWarnings False
Dim db As Database, rel As Relation, fld As Field
 
 Set db = CurrentDb()


  For Each rel In db.Relations
      If rel.Table = "lkup_QEPContacts" And rel.ForeignTable = "data_WellContacts" Then
      
      'Debug.Print rel.Name
         db.Relations.Delete rel.Name
      Else
      End If
  
  Next rel

db.Relations.Refresh

Set db = Nothing

 DoCmd.SetWarnings True

RELATIONSHIP_DELETE_Exit:

    Exit Function

RELATIONSHIP_DELETE_Err:
    MsgBox Error$
    Resume RELATIONSHIP_DELETE_Exit

 

End Function

'Function to add relationship to data_wellContacts from lkup_QEPContacts


Function DATABASE_RELATIONSHIP_ADD()

On Error GoTo DATABASE_RELATIONSHIP_ADD_Err
DoCmd.SetWarnings False

Dim db As Database, rel As Relation, fld As Field

 Set db = CurrentDb()


    Set rel = CurrentDb.CreateRelation("lkup_QEPContacts_dataWellContacts", "lkup_QEPContacts", "data_WellContacts")
    rel.Attributes = dbRelationDontEnforce
    Set fld = rel.CreateField("Contacts_ID")
    fld.ForeignName = "Contacts_ID"

'

   rel.Fields.Append fld
   db.Relations.Append rel
   db.Relations.Refresh

 DoCmd.SetWarnings True

DATABASE_RELATIONSHIP_ADD_Exit:
    Exit Function


DATABASE_RELATIONSHIP_ADD_Err:
    MsgBox Error$
    Resume DATABASE_RELATIONSHIP_ADD_Exit

 

End Function

 

[BNPMike]

I haven't read the code but lets get one thing clear: in the relational model you create relationships purely (and solely) by putting matching data items in fields in different tables. You then retrieve the joined data by using Join. Nothing else you can do will affect this one way or the other. If you are having problems connecting data then either you have data errors or you are not using SQL (which is what you are really supposed to do).

 

[lespaul]

creating relationships in Access really only makes your query writing easier because the database can guess at the joins you want to make and do them for you. In order to get the data right the joins must be right.

Leslie

Come join me at New Mexico Linux Fest!

 

[sterlecki]

Hello lespaul!

The problem I noticed was not so much in the query joins but when users (in this case me, while I was testing)when the relationships disappeared orphan records and inappropriate duplicates appeared as a result of the user adding or deleting records.

This app is mostly a user input interface and a report printing utility. Changes are constantly made to records so, as I noticed, when a relationship was lost data integrity was lost.

I have since been advised by a friend that re-establishing relationships through code would probably not affect anythnig except if the code ran at the very moment a user was updating/editing a record. I HAVE NOT TESTED THIS THEORY!

Again I was looking to see if anyone has had any experience with this problem?