VBA not deleting table?

Question

I'm having trouble with this code to delete a table in Microsoft Access.

When I run code the first time table is deleted. When I run code the second time table is NOT deleted. When I run code the third time table is deleted.

Here's my code:

 strTableName = "TableA"
 CheckTable = IsObject(CurrentDb.TableDefs(strTableName))

'CheckTable = TRUE at this point
    
    If CheckTable = True Then
        checkTableSQL = "DROP TABLE " + strTableName
        DBEngine(0)(0).Execute checkTableSQL, dbFailOnError
    End If
    
    CheckTable = IsObject(CurrentDb.TableDefs(strTableName))

'Still CheckTable = TRUE at this point

Could it be the missing ";" seen in the [documentation](https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/drop-statement-microsoft-access-sql) (and also a different site but I don't want to blind you as it did to me). — Notus_Panda, Mar 06 '23 at 22:51
Semi-colon is not required for simple SQL. Access query designer throws it in saved query object. Where is CheckTable declared? It is a Boolean? Boolean default is False. Why use it? `If IsObject(...) Then`. — June7, Mar 06 '23 at 23:15
I tested your code and it works to delete table. However, when table does not exist, code errors for the IsObject() function. Review https://stackoverflow.com/questions/3350645/how-to-check-if-a-table-exists-in-ms-access-for-vb-macros. If table is deleted first time then why would code run again to delete same table? It's already deleted. — June7, Mar 07 '23 at 03:05

score 0 · Answer 1 · answered Mar 10 '23 at 18:55

0

I would just delete the table and suppress the error when the table is not existing.

    On Error Resume Next
    DBEngine(0)(0).Execute "DROP TABLE " + strTableName
    On Error GoTo 0

answered Mar 10 '23 at 18:55

hennep

545
4
12

Maybe test afterwards to see if deletion has failed – hennep Mar 10 '23 at 18:58

VBA not deleting table?

1 Answers1