Drop Table

[ahleah1031]

I KNOW this will sound silly asking and I promise I have read every possible post all over the internet, but for some reason I can not get my Drop Table command to work.

The Access '03 tables I want to delete/drop are automatically created when the import is initiated. Each table has "ImportErrors" verbaige in it's name, but the suffix of the name is a sequential running based on the number of files imported.

Exactly how would i automate the droping of these tables? Macro calling a module? Macro calling a SQL qry? Macro with a SQL stmt in it? Nothing I do seems to work. Please help me!!

Thank you in advance

[FishVal]

Hi, there.

You've surprised me. ;)
The following SQL statement drops table without any problem.

DROP TABLE <TableName>;
 

Regards,
Fish

[ahleah1031]

But, I have multiple tables, that all have different endings to their table names, so I can't get the Drop Table to work with any type of "wildcard" characters

[FishVal]

But, I have multiple tables, that all have different endings to their table names, so I can't get the Drop Table to work with any type of "wildcard" characters

The following procedure will do it.

Public Sub DropMultipleTables(strLike As String)
 
    Dim tbl As DAO.TableDef
 
    For Each tbl In CurrentDb.TableDefs
        If tbl.Name Like strLike Then CurrentDb.TableDefs.Delete tbl.Name
    Next
 
    Set tbl = Nothing
 
End Sub
 

[ahleah1031]

ok...i must be close, because I'm not getting any errors...but I'm not seeing where anything is happening either. The tables I'm attempting to drop are still showing..

I know I'm missing something simple, it's because I've been working on this for TOO long!!

I pasted your code into a new module and set to view watch, but nothing is getting triggered.....any thoughts? Other than the obvious...I'm stupid! :o)

Thank you for helping me, I so greatly appreciate it.

[FishVal]

:)

Do you include wildcards in the sub argument?
Like the following:
DropMultipleTables "Table*"

Certainly I've tested the code. It works just fine deleting [Table1], [Table2], [Table3].

[jaxjagfan]

Try this for the error tables. Its a Function I use in a module to drop tables with the "_InportErrors" in its name. You can call the function from your import routines.

Public Function CleanErrors()
 
Dim tname As TableDef
Dim db As Database
Dim tdef As TableDefs
On Error Resume Next
Set db = DBEngine(0)(0)
Set tdef = db.TableDefs
 
For Each tname In tdef
    If tname.Name Like "*_ImportErrors*" Then
    DoCmd.DeleteObject acTable, tname.Name
    End If
Next
 
End Function
 

[ahleah1031]

Thank you both so much!

JaxJagFan,
Your coding is "exactly" what I was trying to come up with and for the same purpose. It ran like a deer!!!

Thank you so very much.
P.S. Jax Jag huh? I believe we're in the same location

[jaxjagfan]

HTH

Have had same issue. I keep a lot of my previous Access projects in a folder I call Toolbox. Some date back over 10 years.

BTW - Jacksonville (Mandarin) and will be at the game on Sunday.