delete relationships (Access 2000)

Topic

New Reply

My function for deleting tables is not working, since i receive the mesage that the tables participate in one or two relationships.
Cann i add on some code to delete these relationships,in order to delete all the tables after that.My current function is the following
Function DeleteAll()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim qdf As DAO.QueryDef
Dim frm As Document
Dim rpt As Document
Set db = CurrentDb
For Each tdf In db.TableDefs
If Left(tdf.Name, 4) “MSys” Then
DoCmd.DeleteObject acTable, tdf.Name
End If
Next
End Function

Reply | Quote

Replies

You should go at this a bit differently. When you delete a TableDef, you change the TableDefs collection, so you will probably miss a few. You should step backwards through the collection. Same for the relationships: step backwards.

Function DeleteAll()
Dim dbs As DAO.Database
Dim i As Integer
Set dbs = CurrentDb

‘ Loop backwards through relations
For i = dbs.Relations.Count – 1 To 0 Step -1
dbs.Relations.Delete dbs.Relations(i).Name
Next i

‘ Loop backwards through tabledefs
For i = dbs.TableDefs.Count – 1 To 0 Step -1
If Left(dbs.TableDefs(i).Name, 4) “MSys” Then
dbs.TableDefs.Delete dbs.TableDefs(i).Name
End If
Next i

Set dbs = Nothing
End Function

Reply | Quote

Thank you very much indeed for your reply.I have tried your suggestion but i must have missed something since i get the Run Time error 91 namely ” “Object Variable or With block variable not set “. Could you check up my function again to find where my error is ?
Here is the function:
Function DeleteAll()
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim i As Integer
Set dbs = CurrentDb

‘ Loop backwards through relations
For i = dbs.Relations.Count – 1 To 0 Step -1
dbs.Relations.Delete dbs.Relations(i).Name
Next i

‘ Loop backwards through tabledefs
For i = dbs.TableDefs.Count – 1 To 0 Step -1
If Left(tdf.Name, 4) “MSys” Then
dbs.TableDefs.Delete dbs.TableDefs(i).Name
End If
Next i

Set dbs = Nothing
End Function

Best Regards

Reply | Quote

Which instruction is giving error?
Set a breakpoint and step thru one instruction at a time till you come to it.

Reply | Quote

I feel obliged to let you know that the code suggested by you works now!! I did not cooy your suggestion in the right way
but i have copied again and it is excellent !!!!!!!!!!!

Best regards

Reply | Quote

When I tried the code with For Each tdf In db.TableDefs, I had to run it several times before all tables had been deleted. When I used For i = db.TableDefs.Count -1 To 0 Step -1, all tables were deleted in one go.

Reply | Quote