Selective deleting in tables with VBA

[PB7]

Hello Access users.

Thought this VBA code module would be simple, but as usual, probably missing a nuance, and this doesn't work.

I need to selectively delete about 5 tables in a 2007 database before the major processing, and I thought I could do so in VBA with following example of code:

For Each tblDef in CurrentDb.TableDefs
If tblDef.Name = "TableThatMustBeDeleted" Then
DoCmd.SelectObject acTable, tblDef.Name, True
DoCmd.DeleteObject acTable, tblDef.Name
End If
Next tblDef

Also have:

Dim db as Object
Set db = CurrentDb

Dim tblDef as TableDef

CAN ANYONE SPOT WHAT I'M DOING WRONG??

Thanks in advance to any help.

 

[Joe4]

How are you differentiating the different table names?
If you have the table name hard-coded, won't it delete just that one table?

Is that what the problem is, or is it not deleting anything or are you getting some sort of error?

 

[Joe4]

In case it helps at all, here is a script that I use to delete any ImportErrors tables that are creating from file imports (very similar to yours, except for the IF statment).

    Dim tblDef As TableDef
'   Delete old import errors tables
    For Each tblDef In CurrentDb.TableDefs
        If InStr(1, tblDef.Name, "ImportErrors") > 0 Then
            DoCmd.SelectObject acTable, tblDef.Name, True
            DoCmd.DeleteObject acTable, tblDef.Name
        End If
    Next tblDef

 

[PB7]

Joe, right, I am hard-coding the table names. If that snippet of code works to delete "TableThatMustBeDeleted", then I will replicate that snippet 5 times, with each of 5 snippets to refer to a different table.

I sense there are other ways, maybe better ways, to do this deleting, but as a VBA novice, I'm going for whatever I can visualize working right now.

So, my code doesn't ultimately delete the the linked table...its still there when I run this code. To my dismay.

Looking for mercy and survival, as is often the case with VBA. Thanks.

 

[PB7]

Joe, your code works great, and solves my problem.

I thought a slight variation would work, but it didn't. Thanks..huge help here!!

 

[james_lankford]

CurrentDb.Execute "drop table some_table_name"