VBA to delete all relationships

[boboivan]

Hello all!

I need to automatically delete all the relationships in my database and I've found on internet a code that returns:

Run-time error '3211' 
The database engine could not lock the table 'X' because 
it is already in use by another user or process.

The code is:

Private Sub Command39_Click()
Dim db As Database
Dim rex As Relations
Set db = CurrentDb()
Set rex = db.Relations
Do While rex.Count > 0
Debug.Print rex(0).Name
rex.Delete rex(0).Name
Loop
End Sub

I have no table in use. The problem could be the multiple relationships that I have from table 'X' to other tables.
Is there any way to improve this code?

Thx!

[orange]

Please give the link for where you found the code.

See this for info

[boboivan]

http://allenbrowne.com/DelRel.html

[orange]

Please show us ALL of the code you are using.

[John_G]

Try code something like this:

Dim r As Relation
 For Each r In CurrentDb.Relations
   If Left(r.Name, 4) <> "MSys" Then
     Debug.Print r.Name
     CurrentDb.Relations.Delete (r.Name)
   End If
 Next

I gave it a try in my sandbox db and it seemed to be OK.

[orange]

bob,

I agree with JohnG's code, and the full code sample shown by Allen Browne. But, can you tell us why you want to delete all existing relations? Just curious.

[boboivan]

Thank you John!

I've tried your code, but I get exactly the same error.
Don't know why

Thank you Orange for all the info.
Quite hard to explain the reason

[John_G]

Where are you running this code? If you are running it from a form, and the form has a recordsource (table or query) defined, then you do have a table open, as part of the form operations.

Try putting the code in a standard module (i.e. not in a form), then run it from the immediate pane (Ctrl_G) with no forms open.

[boboivan]

I'm running it from a button placed on the main menu form, which doesn't have any recordsource defined.

[boboivan]

You're right John. It works placed in a module.
And if I want it in a button, I have to close the form first.

So, your code will be:

Private Sub Command38_Click()
DoCmd.Close acForm, Me.Name
Dim r As Relation
 For Each r In CurrentDb.Relations
   If Left(r.Name, 4) <> "MSys" Then
     Debug.Print r.Name
     CurrentDb.Relations.Delete (r.Name)
   End If
Next
End Sub

[John_G]

I was able to replicate your 3211 error by running the code with a form open (but with the code in a standard module), and the error referenced the table I expected it to.

Is there any code in your Main Menu form that might open a recordset, or otherwise work with a table or query? If there is a lot of code in the form, you could do a text search for the name of the table listed in the error message. Does your main form have a combo box or list box on it? Those have rowsources wich usually reference tables or queries.

I am almost certain the problem is in your form somewhere, because I was able to successfully run my code from a form button.