DeleteObject Method

[Richard]

I was wondering if its possible to use the "DeleteObject Method" with the
"Like *" operator to delete tmp tables? I import excel file into my database
and sometimes are left with import error tables i.e. 'XXXXX$_ImportErrors,
'XXXXX$_ImportErrors1, 'XXXXX$_ImportErrors2, and so on. I'd like to remove
them all at once with a cmdbutton and some code if this possible. These
tables don't appear to have any useful information.

Thanks for your time
Richard

 

[kc-mass]

Run a query to populate a dataset with the names of such tables like:
SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Name) Like "*errors*") AND ((MSysObjects.Type)=1))
ORDER BY MSysObjects.Name;

Then walk the dataset and delete them.

Regards
Kevin

 

[Peter Hibbs]

Richard,

Here is a function I use for this. It will delete ANY table that has
the word 'ImportErrors' in the name so you need to make sure that none
of your other tables have this word in the table's name.

The code actually has two functions, if you pass it True (as shown
below) it will delete all the error tables, if you pass it False it
will return the name of the first error table it finds or NULL if
there are none. I used this to check if any error tables had been
created after an import operation. You could modify the code to remove
that option if you don't need it.

Dim vTableName As String

vTableName = FetchErrorTable(True)

Copy and paste this code into a VBA module, watch out for line wraps.

'----------------------------------------------------------------------------------
Public Function FetchErrorTable(vDel As Boolean) As String

'Return with name of error table OR NULL if none
'Entry (vDel) = True to delete all Error tables or False to return
first Error table name
'Exit (FetchErrorTable) = Name of errors table (or NULL if no table
found)

Dim db As DAO.Database
Dim tdf As DAO.TableDef

Set db = CurrentDb()
db.TableDefs.Refresh
For Each tdf In db.TableDefs
FetchErrorTable = tdf.Name
If InStr(1, FetchErrorTable, "ImportErrors") > 0 Then
If vDel = True Then
DoCmd.DeleteObject acTable, FetchErrorTable
Else
Exit Function
End If
End If
Next tdf
FetchErrorTable = ""

End Function
'----------------------------------------------------------------------------------

HTH

Peter Hibbs.

 

[marcus]

I was wondering if its possible to use the "DeleteObject Method" with the
"Like *" operator to delete tmp tables? I import excel file into my
database
and sometimes are left with import error tables i.e. 'XXXXX$_ImportErrors,
'XXXXX$_ImportErrors1, 'XXXXX$_ImportErrors2, and so on. I'd like to
remove
them all at once with a cmdbutton and some code if this possible. These
tables don't appear to have any useful information.

Thanks for your time
Richard

 

[Richard]

Thanks Peter,

I will give your code a try and post back.

Richard