Deleting multiple tables misses some each iteration

[gnawoncents]

Greetings.
I have a split Access 2013 database (back end with tables and front end with everything else) an am having trouble with deleting tables.

I run the code below to check the back end for only tables that meet my required criteria then delete them from the back end and delete the links from the front end.

When I test the code using a simple msgbox line naming the tables that will be deleted, it iterates through all the appropriate tables; however, when I run the actual code, only two or three tables are deleted at a time (with no popup errors). If I run the code again, a few more are deleted, and I can continue running it until all the correct tables are deleted.

Why won't it delete all the tables in one go?

I suspect it is some sort of a timing issue, but am not sure. Ideas anyone? Thanks for your help.

Private Sub btnDeleteTables_Click()
 
'---------------------------------------------------------------------------------------------
'Find all past and future-dated (not current month) schedule tables not in use then
'delete them from the back end and remove the links in the front end
'---------------------------------------------------------------------------------------------
 
Dim dbDEL As Database
Dim tdfDEL As TableDef
Dim strTable As String
strTable = ""
 
'Get the location of the back end
Dim strBackEndPath As String
Dim lenPath As Integer
Dim i As Integer
Dim j As Integer
 
strBackEndPath = CurrentDb.TableDefs("tbScheduleALL").Connect
 
'Remove the datebase & password prefix
j = InStrRev(strBackEndPath, "=") + 1
strBackEndPath = Mid(strBackEndPath, j)
 
Set dbDEL = OpenDatabase(strBackEndPath)
 
For Each tdfDEL In dbDEL.TableDefs
    strTable = tdfDEL.Name
    'Check to see if the table is a schedule table, linked, and not marked as in use (fldActive <> -1)
    If (strTable Like "tb######*") And strTable <> "tb" & Format(Date, "yyyymm") Then
        If Len(CurrentDb.TableDefs(strTable).Connect) > 0 Then
            If (DLookup("[fldActive]", strTable) <> -1) Then
                dbDEL.TableDefs.Delete strTable 'delete table from the back end
                DoCmd.DeleteObject acTable, strTable 'Remove link to deleted tables from the front end
            End If
        End If
    End If
Next
 
dbDEL.Close
Set dbDEL = Nothing
Set tdfDEL = Nothing
 
End Sub
 

[jforbes]

I have a feeling that your iterator (For Each Loop) is not aware that tableDefs are being Deleted. So when one is deleted, either the iterator gets lost, or it skips what would become the new current one (after the current one is deleted), effectively skipping the one right after the one that is deleted.

You might be able to re-write you loop to a Do While and reference the TableDefs by Index.

[gnawoncents]

Thanks--I'll give that a go!

[gnawoncents]

Many thanks, jforbes, you were right on the mark. Instead of deleting the tables in my For Each Loop, I created and added them to a collection, then iterated through the collection once complete. It works perfectly. You are awesome!

[NeoPa]

That's absolutely correct.

Every time a TableDef is removed from the collection any previous pointer, or object set, to that collection remains pointing to the original collection and is not equivalent to a pointer/object set to the collection after one is deleted. The original keeps the same dimensions as before but simply sets the place for deleted entries to a marker indicating it no longer exists.

I added two new tables to my database and ran the following code (All on one line in the Debug Pane but I show it here separated out for ease of reading) :

Set TS=CurrentDb().TableDefs
?TS.Count;CurrentDb().TableDefs.Count;"|";
Call DoCmd.DeleteObject(acTable,"tblUpdate1")
?TS.Count;CurrentDb().TableDefs.Count;"|";
Call DoCmd.DeleteObject(acTable,"tblToDelete1")
?TS.Count;CurrentDb().TableDefs.Count;

The results I got were :

 93  93 | 93  92 | 93  91

You'll notice the TS object doesn't get reduced but continues to 'think' it's covering 93 tables even after two have been deleted.

In case you're wondering, adding tables invalidates the object entirely. Had you tried that your code would have crashed instead.

@Nonsense.
Your solution is a good one. You should also have been able to remove them in order from the original as long as you went backwards through the collection. That would involve accessing it numerically though, which is less intuitive than using a string reference.

Another thing to consider, assuming you only ever want today's one at any one time, is to rename and repoint the single link to ensure it points to the correct table. That might get complicated though as you'd need to identify the table to rename by something other than its name of course. All just ideas.