How to delete tables using vba from within the access 2007 database?

[ncsthbell]

I am importing a lot of data from spreadsheets and 'import error' tables are being created. I need to be able to delete these tables using vba code in the database. Also, each of the tables are named differently so I would need to do a mask for the deletion. I have import error tables named:
"AdminSvcs$'_ImportErrors1"
"AdminSvcs$'_ImportErrors2"
"AdminSvcs$'_ImportErrors3"

I would probably need to use a mask to look for "*ImportErrors*".

I have never delete tables in the database using vba, just deleted data in the tables.
Any suggestions would be appreciated!!!

[NeoPa]

You can use the TableDefs collection from a database (EG. CurrentDb).

Loop through checking for whatever identifies the tables you want removed then call :

Call CurrentDb.TableDefs.Delete(TableName)

[Mariostg]

Something like this. I did not test it though...

Public Function fncDocumentTables() As String
    Dim tbl As DAO.TableDef
 
    For Each tbl In CurrentDb.TableDefs
         If InStr(tbl.Name, "ImportErrors") Then
            CurrentDb.TableDefs.Delete (tbl.Name)
        End If
    Next tbl
End Function
 

[NeoPa]

Line #6 won't compile Mario.

Parentheses are only used when the procedure is used to return a value, or if the value is dropped explicitly by using Call. Otherwise it thinks it's dealing with an array reference.

[Mariostg]

Yes sometime I tend to forget about parentheses rules. Interestingly enough, it compiled. So I just tried it with and without parentheses and dropped two tables... I am glad it was a dummy db.
But yes normally I don't use parentheses if I don't assign the return value to a variable.

[NeoPa]

Shock! Horror! Don't tell me you don't have mandatory variable declaration in your projects? That code wouldn't compile in my setup (It gives Compile Error - Expected: =).

See Require Variable Declaration.

[Mariostg]

Shame on me (more or less), I always added the Option Explicit manually. I did not know about the ability to have this set on by default. LOL. It is going to save me some typing.
I don't want to hijack the thread, but whether or not I use Option Explicit, with or with out parentheses at line 6, it compiles.

[NeoPa]

Well, we're discussing code suggested as a solution so I'm happy we're on topic.

I have no idea what is different then between my setup and yours that it treats that differently. Never mind.

PS I'm glad the link helped.