Referential integrity

[vipersmind]

I am importing tables from another database that have the same names as tables in my database. My tables have existing relationships. I am using vbCode to remove the relationships, delete the existing tables import the new tables and then reinstate the relations after the new tables are imported.
I would like to be able to trap the error that occurs when I can't enforce referential integrity on the new tables because a record doesn't exist in both tables. In the past I have known what the problem was and fixed it manually. But now I want to fix it. I don't have any control over the validity of the imported tables as they are from another department, politics!!.

The vbCode I am using to delete the relationships is thus:

Function DeleteRelation()

    Dim dbs As Database
    Dim fld As Field
    Dim rel As Relation
    
    ' Reference current database.
        Set dbs = CurrentDb
    ' Find existing Collar/LITHOLOGY relation
        For Each rel In dbs.Relations
        If rel.Table = "collar" And rel.ForeignTable = "LITHOLOGY" Then
    ' Prompt before deleting relation
        If MsgBox(rel.Name & " relationship exists, " & vbCrLf _
            & "this relationship will be deleted.", vbOK) = vbOK Then
        dbs.Relations.Delete rel.Name
    ' If user chooses Cancel
        Else
            Exit Function
        End If
    End If
    Next

There are 13 fields in this table and all have relations deleted the same way in the same function. The function is called by a macro behind a command button on a form using the runcode action.

To reinstate the relations:

Function NewRelation()
    Dim dbs As Database
    Dim rel As Relation
    Dim fld As Field
   
    ' Return reference to current database.
        Set dbs = CurrentDb
        DoCmd.Hourglass True
    ' Create new Relationship [collar/LITHOLOGY]and set it's properties.
        Set rel = dbs.CreateRelation("collar LITHOLOGY", "collar", "LITHOLOGY")
    ' Set relation object attributes to enforce referential integrity.
        rel.Attributes = dbRelationUpdateCascade
    ' Create field fields collection of Relation object.
        Set fld = rel.CreateField("hole_id")
    ' Specify field name in foreign table.
        fld.ForeignName = "hole_id"
    ' Append Field to Relation object and Relation object to the database.
        rel.Fields.Append fld
        dbs.Relations.Append rel
        DoCmd.Hourglass False
        MsgBox "Relationship '" & rel.Name & "' created."

This is where I run into troubles, if the data integrity is not complete it won't re-enstate the relationships and won't show an error unless I make them directly into the relationship window.

Can I capture/show this error and fix the problem??
Thanks

Cress

 

[Mile-O]

Double Post