Deleting Tables

[cable]

Trying to use the following code:

'deletes all tables starting with '_'
Dim db As DAO.Database
Dim td As DAO.TableDef
Dim X As Integer

Set db = CurrentDb

SysCmd acSysCmdClearStatus
X = 0
SysCmd acSysCmdInitMeter, "Deleting Old Tables...", db.TableDefs.Count

For Each td In db.TableDefs
    If Left(td.Name, 1) = "_" Then
        db.TableDefs.Delete td.Name
        'DeleteTables                'for some odd reason it doesnt!
    End If
    SysCmd acSysCmdUpdateMeter, X: X = X + 1
Next td

SysCmd acSysCmdClearStatus
db.TableDefs.Refresh

but its not deleting all the tables starting with _!, it does if i uncomment the call in the IF statement...but that can't be way to do it!!

Is it because the number of td's change instantly? throwing the for each loop?

 

[dcx693]

Is it because the number of td's change instantly? throwing the for each loop?

Since you're using a For Each..Next loop, the tabledefs count won't make a difference.

Are you sure it's not deleting the tables? I don't think you need a Tabledefs.Refresh if you're in a single-user environment, but what about an Application.RefreshDatabaseWindow ?

 

[cable]

well the count is just a status thing (and yea its broke but not terribly)

And its definitely not deleting, cause the code after it breaks (as it creates these tables again)

 

[dcx693]

You're saying that the table deletion works when you uncomment this line in the sub?
'DeleteTables 'for some odd reason it doesnt!

 

[cable]

sorry the comment should say "have to call again because for some odd reason it doesn't"

ie calling the routine again and again deletes the tables eventually, and as i'm only deleteing those begining with _ its safe to do this...but i can't see this being the 'correct' way, even if it does work.

 

[dcx693]

Well, I must admit to being a bit confused. I always thought the For Each...Next structure would visit all the items within a collection, even if you deleted some of them within the loop itself, but it seems not to be the case. I know that this can be an issue if you use a For Next loop because the items renumber themselves as you delete them.

Anyway, here is a work around. Use the For Next loop structure, but have the counter go from the last object to the first object. Note that like most collections, the TableDefs start at TableDefs(0):

Sub Delete_Tables()

Dim dbs As DAO.Database
Dim intX As Integer

    Set dbs = CurrentDb
    For intX = dbs.TableDefs.Count - 1 To 0 Step -1
        If Left(dbs.TableDefs(intX).Name, 1) = "_" Then
            dbs.TableDefs.Delete dbs.TableDefs(intX).Name
        End If
    Next intX
    
    Application.RefreshDatabaseWindow

End Sub

Note: the RefreshDatabaseWindow method is necessary.