Formula question

[bodhisatvaofboogie]

I am trying to delete a table if it shows up. I utilize macros to format
documents in excel, then an access database to do comparisons. When
importing the data into a table from Excel there is nearly always an import
errors table generated for each one. I looked to see what was causing the
errors from the excel side and fixed that so it wouldn't happen. HOWEVER,
there are still occasional errors for a variety of reasons, thus a table is
created. I wanted to write into my access database macros a command line
that would do the following:

If *$_ImportErrors table exists Then
Delete the table
End If

I was thinking the * wildcard as there are approx 40 different table
possibilities that will be encountered, but the $_ImportErrors is always
consistent. I wondered if a VBE code could search for any tables with that
suffix on it and delete them. If the wildcard won't work, I'll take your
advice and do a code for each of the table possiblities. Thanks in advance
for the help!!!!

 

[Douglas J. Steele]

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim intLoop As Integer

Set dbCurr = CurrentDb()
For intLoop = (dbCurr.TableDefs.Count - 1) To 0 Step -1
Set tdfCurr = dbCurr.TableDefs(intLoop)
If Right$(tdfCurr.Name, 14) = "$_ImportErrors" Then
dbCurr.TableDefs.Delete tdfCurr.Name
End If
Next intLoop


Note that you need to loop through the TableDefs collection from end to
beginning, rather than the more common other way, in order to ensure that
you deal with each table.