Deleting Linked tables programmatically

  • Thread starter Thread starter J
  • Start date Start date
J

J

I want to be able to delete all linked tables. I set up a
table that lists all the linked tables. I can't get past
the MSysRelationship table. I tried using the on error
and it doesn't recognize the NAME_NOT_IN_COLLECTION error,
but I can't get it to move past the MSysRelationship
table. I'm not trying to delete this table, but I get
the "NAME_NOT_IN_COLLECTION" error when it hits that
table. I tried using the on error to bypass it, and it
will run through the code, but it doesn't delete any
tables after it gets to MSysRelationship. If I repeat the
code, it deletes additional table and if I do this 3 or 4
times, it eventually deletes them all, but there has to be
a way around this.

Here's my code:

Const NAME_NOT_IN_COLLECTION = 3265
For j = 0 To CurrentDb.TableDefs.Count - 1

On Error Resume Next
tblStg = CurrentDb.TableDefs(j).Name
If Err <> NAME_NOT_IN_COLLECTION Then
rstStg = "Select * from linked_tables where table_name
= '" & tblStg & "'"
Set rst = CurrentDb.OpenRecordset(rstStg)
If rst.EOF Then
Else
DoCmd.DeleteObject acTable, tblStg
End If
Else

End If
Next
rst.Close
 
The problem is your loop counter. If you delete TableDefs(0), then the
table which used to be TableDefs(1) will now be TableDefs(0), and it will be
missed the next time around the loop.

The trick is to count downwards instead of upwards:
For j = CurrentDb.TableDefs.Count - 1 To 0 Step -1

Incidentally, the same problem occurs if you want to close all open forms.
If you use an ascending counter then you close only every second one.
 
Back
Top