Deleting a relationship through code

  • Thread starter Thread starter Guest
  • Start date Start date
=?Utf-8?B?RGVyZWs=?= said:
Is there a way to delete a relationship through code?

Thanks,
Derek
Click to expand...

Derek

You can loop through the Relationships collection:

Public Sub sDeleteRelationship(strTable1 As String, strTable2 As String)
On Error GoTo E_Handle
Dim db As Database
Dim rel As Relation
Dim intCount As Integer
Dim intLoop As Integer
Set db = DBEngine(0)(0)
intCount = db.Relations.Count - 1
For intLoop = intCount To 0 Step -1
If ((db.Relations(intLoop).Table = strTable1) And
(db.Relations(intLoop).ForeignTable = strTable2)) _
Or ((db.Relations(intLoop).Table = strTable2) And
(db.Relations(intLoop).ForeignTable = strTable1)) Then
db.Relations.Delete db.Relations(intLoop).Name
End If
Next intLoop
sExit:
On Error Resume Next
db.Relations.Refresh
Set db = Nothing
Exit Sub
E_Handle:
MsgBox Err.Description & vbCrLf & "sDeleteRelationship", vbOKOnly +
vbCritical, "Error: " & Err.Number
Resume sExit
End Sub

The above example only checks on the table names - you might also want to check
the field names as well.

Note that although there is a Name property for a relation which appears to be
created from the names of the two tables involved, it can sometimes be just a
GUID. This appears to be the case if the relationships were created in another
database, and then imported into the current database, so it is probably safer
to check the name of the Table and ForeignTable as above.
 
Derek said:
Is there a way to delete a relationship through code?
Click to expand...

Sure. All you need to figure out is how to identify the
relationship, then use the Relations collection:

CurrentDb.Relations.Delete nameofrelation

or you could Execute an SQL DDL statement:
ALTER TABLE table DROP CONSTRAINT indexname
 
Back
Top