Code to delete relationships?

[bulrush]

MS Access 2003 on WIN XP, MDB file.

I am deleting a table in the current MDB file so I can copy it from another MDB file using this code:

DoCmd.DeleteObject acTable, "oldTable"

But it's in a relationship. How do I delete the relationship first so I can delete the table?

Thanks.

 

[vbaInet]

Look in the Relationships Window

Or try to,

Create a new database shell, import ALL objects EXCEPT that table.

 

[bulrush]

I already know how to delete relationships via the user interface. I need to automate this using VBA code. Thanks.

 

[vbaInet]

It can be done but what you're attempting is a risky task. Sounds like it's a process that would be repeated quite often? Can you give us some background.

 

[bulrush]

My dev database (MDB) is the one I use for development. But there is also a version in production. Sometimes, when I produce a new report to be reviewed by someone else, I need to update my data, i.e. get all new data from the production mdb file. Because they only have access to the production MDB file. So in my dev MDB file, I just want to click one button, delete 2 tables, and get these 2 tables from the production MDB file.

I have a backup table in my dev MDB file called "oldTable", so first I want to delete "oldMyTable" then rename "MyTable" to "oldMyTable" in my dev MDB file, then import the new "MyTable" from the production MDB file into my dev MDB file. Access will not let me delete "oldMyTable" because it is in a relationship. So I need code to break or delete the relationship for "oldMyTable" first.

 

[vbaInet]

Alrighty!

Look into CurrentDb.Relations

 

[Rabbie]

Alternatively just delete the contents of your existing tables then import the contents of the new table

 

[bulrush]

Ah. So I can just do this import and overwrite all current records in the table?

dbsrc = "M:\foo\blah.mdb"
tname = "Mytable"
DoCmd.TransferDatabase acImport, "Microsoft Access", dbsrc, acTable, tname, tname

 

[Steve R.]

Allen Browne has this code to delete a relationship.

Function DeleteRelationDAO()
    DBEngine(0)(0).Relations.Delete "tblDaoContractortblDaoBooking"
End Function

A concern with the code above is that you need to know the name of the relationship before it can be deleted. Below is code to list the names of the relationships in your database.

Public Sub PrintRelations()
    Dim db As DAO.Database
    Dim rel As DAO.Relation    
  
    Set db = CurrentDb    
    For Each rel In db.Relations
        Debug.Print rel.Name, rel.Attributes
    Next rel    
End Sub

After deleting a relationship there may be a need to recreate relationships. Allen Browne covers establishing relationships here.

 

[MarkK]

Given the names of two tables, I expect that this code would delete the first relation it finds between them...

Function DeleteRelation(table1 As String, table2 As String) As Boolean
   Dim rel As DAO.Relation
   
   With CurrentDb
      For Each rel In .Relations
         If (rel.Table = table1 And rel.ForeignTable = table2) _
         Or (rel.Table = table2 And rel.ForeignTable = table1) Then
            .Relations.Delete rel.Name
            DeleteRelation = True
            Exit For
         End If
      Next
   End With
End Function

...but I haven't tested it at all.

 

[gemma-the-husky]

to do it by code, I presume you would have to check both the tables involved in the link

so I expect the way to do it is to iterate the relations collection, and delete any where the table and the foreigntable are the ones you want. note that they may be in any order.

Coincidentally, I have been messing around all weekend with managing tables/fields/indexes and relations in linked tables - but I haven't got around to trying to create/delete relationships yet.


sort of

dim rel as relation
for each rel in currentdb.relations
  {check rel.table and rel.foreigntable
   you may also need to iterate the rel.fields collection to see which fields are linked}
next

[edited - I see lagbolt has given a fuller example already

further note - the relation names are very curious - I iterated the colelction to get the names, and a lot of them showed up as curious GUID type values - eg this is one - I have just replaced the actual path with fullpath


[D:\DATABASES\fullpath].{1CD97DD8-84F3-4618-BFFA-1D6111E4CBEC}

 

[Steve R.]

Given the names of two tables, I expect that this code would delete the first relation it finds between them...

Function DeleteRelation(table1 As String, table2 As String) As Boolean
   Dim rel As DAO.Relation
   
   With CurrentDb
      For Each rel In .Relations
         If (rel.Table = table1 And rel.ForeignTable = table2) _
         Or (rel.Table = table2 And rel.ForeignTable = table1) Then
            .Relations.Delete rel.Name
            DeleteRelation = True
            Exit For
         End If
      Next
   End With
End Function

...but I haven't tested it at all.

Thanks for the example. I had gone as far as finding out how relationships can be conceptually deleted and added through code and even SQL, but never got around to actually doing it.

I see gemma-the-husky was working on this too!

Coincidentally, I have been messing around all weekend with managing tables/fields/indexes and relations in linked tables - but I haven't got around to trying to create/delete relationships.

 

[MarkK]

Yeah, those relation names are weird.