Delete a Relationship

[jalewis999]

I have a system that when a new user is added 4 new Tables are created with 4
Relationships. The New tables are copies of a Master set mostly empty.
(Template).
The new tables are prefixed by name John_Smith_Transaction. I have mastered
the adding Relationships on the fly using the master set as a reference.
The problem is when a user is deleted. I delete the relationships first then
the Tables. The first run through the code 2 of the 4 are found and deleted
the next 1 of the 2 remaining is found and finally on the third run the last
one is found and deleted. I have tried a refresh after deletion but that
dosn't help.
If I run the code without deleting it finds all Relationships.
The code is below. Can anyone help Thank you.

Function DeleteRelationships(strUserName As String)
Dim db As Database
Dim rls As Relations
Dim rl As Relation
Dim strTemp As String
' Replace spaces and add _ to end
strTemp = Replace(LTrim(RTrim(strUserName)) + "_", " ", "_")
Set db = CurrentDb
Set rls = db.Relations
For Each rl In rls
If rl.name Like (strTemp + "*") Then
db.Relations.Delete rl.name
End If
Next
Set db = Nothing
Set rls = Nothing
End Function

 

[John Spencer]

Without going into detail on your structure I do want to say that this
design is highly unusual and seems to be a bad design for a database.

That said, you have to step through the relationships in reverse order.
When you delete the first relationship all the remaining relationships move
up in the system, so when you move next you skip the relationship that has
just moved up one slot.

Use a For loop that decreases by 1 each time

Dim iLoop as Integer

For iLoop = rls.Count-1 to 0 Step -1
If rls(iLoop).Name Like (strTemp + "*") Then
db.Relations.Delete rls(iLoop).Name
End If
Next iLoop

I would advise testing the above UNTESTED AIRCODE on a copy of your database
to see if that works.

Another method if you know what the relationship names are would be to just
delete the four relationships.