Drop All Relationships and relAttributes

Question

I'm trying to repair some relationships in a database and I've created an xml copy of a backup DB. Do you need to drop all existing relationships first. I tried DropRelationships from here Importing/Exporting Relationships Function DropRelationships() With CurrentDb For Each rel In .Relations .Relations.Delete Name:=rel.Name Next End With End Function

and I get "The database engine could not lock table 'MSysAccessStorage' because it is already in use by another person or process. " In addition when trying to import the xml file, I get an error on this line. relAttr = xmlRel.selectSingleNode("Attributes").Text It appears to be looking for a string value like dbRelationUpdateCascade + dbRelationDeleteCascade The xml file has a number like 4352 So do I need to create a routine to change the number into the constant names above to get it to work. I did find some code here https://www.access-programmers.co.uk/forums/threads/how-to-get-foreigntable-field-and-relationship-types-using-vba.267268/ that I can change to get the constant names. Is this the correct way to get this to work. How do you work around this. I am a novice programmer, so a one word answer may not help. Sorry and thanks.

I tried deleting the lock file with no success.

score 0 · Answer 1 · answered Jun 28 '23 at 16:14

So, I was able to get past the Lock table problem with

   If Not Left(rel.Name, 4) = "MSys" Then 'exclude system 
              relations
             Debug.Print rel.Name
          .Relations.Delete Name:=rel.Name
     End If

Can't delete system relations. Learned somthing new. ALthough, I did have to run it several times before all of the relationships went away. Any idea why that would happen?

Still have the issue in setting the attributes from the xml file to rebuild them.

When deleting items from a collection, you need to loop backwards. See e.g. https://stackoverflow.com/a/59632166/3820271 — Andre, Jun 28 '23 at 19:35

score 0 · Answer 2 · answered Jun 29 '23 at 01:56

Why not create a new blank database in Access, and then import the data (tables) from that other database.

When you import, you can choose to ignore (not import) the relationsips.

So, this option:

(click on the options button, it expands the options),

So, uncheck the above "relationships" box, and thus you can import "some" tables, or all of them, and relationships will not be imported.

And this is often a good option if the source database say is damaged, since you can say select the first 10 tables (or 5) or whatever, and see how far you can get.

And since you just created a blank fresh database, then the "version" and other things are now current to the version of access you are using.

So, I would give the above idea a try if say for some reason you don't want to mess with, or change the relationships that exist in the source database.

So, try create a new blank database, and then import the table from that database - but make sure you un-check the Relationships box during this import.

Drop All Relationships and relAttributes

2 Answers2