Looping through Table Catalog causes Access to crash

[frozbie]

hi,

I wrote a little routine to clean out temporary tables that are used to store data in Access while producing complex reports from a back end Unix database.

The code works fine in a new test database but not when I place it in an older existing database.

I am using MS Access 2002 with both old and the new databases on 2000 file format.

OS is Win XP.

I have added the reference I need to Microsoft ADO Ext. 2.7 for DDL and Security in both databases.

I have played around with the order of the references in the older database to see if that was causing the problem. Tried compacting the old database.

Every time it crashes, the program brings up and error message asking if I want to notify microsoft of the problem and try repairing and opening the database.

I could just copy all database objects from the old database to the new but I would like to understand why I am getting the problem. Any suggestions?

Thanks,

Mark

Option Compare Database

Option Explicit


Sub DeleteTempTables()
    
    Rem requires reference to Microsoft ADO Ext. 2.7 for DDL and Security
    
    Rem code to delete all rows in every table that begins 'tbl_'
    
    Dim conn As New Connection
    Dim adoCat As New ADOX.Catalog
    Dim adoTbl As New ADOX.Table
    
    Dim strSQL As String
    
    
    DoCmd.SetWarnings False
    Set conn = CurrentProject.Connection
    adoCat.ActiveConnection = conn
    
    Rem for each table in the collection of database tables
    For Each adoTbl In adoCat.Tables
        
        If Left(adoTbl.Name, 4) = "tbl_" Then
        
            Rem delete all rows from table
            strSQL = "DELETE * FROM " & adoTbl.Name
            DoCmd.RunSQL strSQL
        
        End If
        
    Next adoTbl
    DoCmd.SetWarnings True
    MsgBox ("Temp tables beginning tbl_ have had all rows deleted")
End Sub