I'm wondering if it is possible to use VBA to store, delete and recreate relationships on tables in Access VBA? The deletion part is easy, but how to store it in such a way as to be able to restore it after it's been deleted is where I get stuck.
I originally wanted to know so that I could bulk copy certain tables from one database into another copy of that database. I ran into trouble as the ref. integrity on the tables was interfering with the inserts. I thought about trying to store then delete the relations, insert the data, then restore the relations using DAO.
After thinking about it and trying to come up with some code for it, I abandoned the idea and inserted it in a different way to avoid the issue altogether. However, after the fact, I was pondering if what I had been trying is doable.
Any thoughts?
EDIT: Here's the code I started to write.
Private Sub Save_Click()
Dim db As DAO.Database
Set db = CurrentDb
'Save db.Relations somehow as SavedRelations
End Sub
Private Sub Delete_Click()
Dim db As DAO.Database
Dim rel As DAO.Relation
Set db = CurrentDb
For Each rel In db.Relations
db.Relations.Delete (rel.Name)
Next
End Sub
Private Sub Restore_Click()
Dim db As DAO.Database
Dim rel As DAO.Relation
Dim newRel As DAO.Relation
For Each rel In SavedRelations 'Stored relations from the Save sub
Set newRel = db.CreateRelation(rel.Name, rel.table, rel.ForeignTable, rel.Attributes)
For Each fld In rel.Fields
newRel.Fields.Append fld
Next
db.Relations.Append newRel
Next
End Sub