StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POStoring and recreating relations in Access
text
Body
copied!<p>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.</p> <p>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.</p> <p>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.</p> <p>Any thoughts?</p> <p>EDIT: Here's the code I started to write.</p> <pre><code>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 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload