How to Delete a Relationship in VBA

[Peter Hibbs]

Hi All,

How can I delete a relationship between two tables using VBA code. I am
using the code below to CREATE a relationship (from the Access Developers
Handbook)

'================
Dim rel As DAO.Relation
Dim fld As DAO.Field
Dim vRelType As Long

On Error GoTo CreateRelationship_Err

Set rel = db.CreateRelation("Relation1", vPKTableName, vFKTableName,
vRelType)
Set fld = rel.CreateField(vPKFieldName)
fld.ForeignName = vFKFieldName
rel.Fields.Append fld
db.Relations.Append rel
CreateRelationship = True
.... etc
'================
where string variables
vPKTableName = the primary key table
vPKFieldName = the Primary key field in table
vFKTableName = the Foreign key table
vFKFieldName = the Foreign key field in table and
vRelType = relationship Type (=Long Int)

How can I delete a relationship using VBA code and what is the string
"Relation1" in the code above. It is presumably the 'name' of the
relationship object but where is it stored. I cannot find it in any of the
table or field properties for the relevant tables. If I need to delete a
relationship would I need to know the 'name' of the relationship given that I
know the name of the tables and fields used in the relationship.

 

[Allen Browne]

Yes, Relation1 is the name of the object.
It is part of the Relations collection.
You can delete it with:
CurrentDb.Relations.Delete "Relation1"

For an example of deleting all relations in a database, see:
http://allenbrowne.com/DelRel.html

 

[Dirk Goldgar]

Hi All,

How can I delete a relationship between two tables using VBA code. I
am using the code below to CREATE a relationship (from the Access
Developers Handbook)

'================
Dim rel As DAO.Relation
Dim fld As DAO.Field
Dim vRelType As Long

On Error GoTo CreateRelationship_Err

Set rel = db.CreateRelation("Relation1", vPKTableName, vFKTableName,
vRelType)
Set fld = rel.CreateField(vPKFieldName)
fld.ForeignName = vFKFieldName
rel.Fields.Append fld
db.Relations.Append rel
CreateRelationship = True
... etc
'================
where string variables
vPKTableName = the primary key table
vPKFieldName = the Primary key field in table
vFKTableName = the Foreign key table
vFKFieldName = the Foreign key field in table and
vRelType = relationship Type (=Long Int)

How can I delete a relationship using VBA code and what is the string
"Relation1" in the code above. It is presumably the 'name' of the
relationship object but where is it stored. I cannot find it in any
of the table or field properties for the relevant tables. If I need
to delete a relationship would I need to know the 'name' of the
relationship given that I know the name of the tables and fields used
in the relationship.

You need to provide the relationship's name in order to delete it. If
you created the relationship, you may know its name. But if Access
created it, you may not know its name. Sometimes Access makes
reasonably meaningful names by concatenating the names of the tables
involved, and sometimes it generates GUID strings for them; it depends
how the relationship was created.

You can find the relationship you want to delete by looping through the
Relations collection and checking the Table and ForeignTable properties
of each relation, and the Name and ForeignName properties of the fields
in each relation's Fields collection. Or you could query the
MSysRelationships table directly, either by opening a recordset on the
appropriate SQL SELECT statement or by using the DLookup function with
suitable arguments. If you set your Access options to View System
Objects, you can open and examine the MSysRelationships table and see
how relationships are represented.

 

[Peter Hibbs]

Hi Allen and Dirk

Thanks very much for the help. I have written the code which is reproduced
below just in case there is anyone else on the planet that wants to do the
same thing.

'==================
Public Sub DeleteRelationship(vPKTableName As String, vPKFieldName As
String, vFKTableName As String, vFKFieldName As String)

'Delete a relationship between two tables
'Entry (vPKTableName) = Name of table for Primary Key
' (vPKFieldName) = Name of Primary Key in primary table
' (vFKTableName) = Name of table for Foreign Key
' (vFKFieldName) = Name of Foreign Key field in Foreign table
' (db) = Database object referenced to back-end file
'Exit Relationship deleted

Dim rst As Recordset

On Error GoTo ErrorCode

Set rst = db.OpenRecordset("SELECT szRelationship FROM MSysRelationships
" _
& "WHERE szReferencedObject = '" & vPKTableName & "' " _
& "AND szReferencedColumn = '" & vPKFieldName & "' " _
& "AND szObject = '" & vFKTableName & "' " _
& "AND szColumn = '" & vFKFieldName & "' " _
& "OR szReferencedObject = '" & vFKTableName & "' " _
& "AND szReferencedColumn = '" & vFKFieldName & "' " _
& "AND szObject = '" & vPKTableName & "' " _
& "AND szColumn = '" & vPKFieldName & "'") 'fetch
Relationship name
If Not rst.BOF And Not rst.EOF Then
db.Relations.Delete rst!szRelationship 'and delete
relationship
End If
rst.Close
Exit Sub

ErrorCode:
MsgBox Err.Description

End Sub
'================

The db database object is declared in the main module and is linked to the
back-end database file. The reason for the code after the OR statement in the
SQL string is because, depending on how the user creates the relationship and
the type of relationship, Access can reverse the order of the two
tables/fields in the relationship field. If there is any reason that this may
not work under circumstances that I have not tested for then please respond.