How do I disable the import errors table?

[Guest]

I'm importing text files into an Access table. The only problem is, when I
import the file, Access creates a import errors table. That isn't the
problem, I don't care about that table. In fact I'm glad it's being created
because it means my import is working the way I designed it, lol. The problem
is, I want to get rid of the table, but since I have different names on the
import files, the error tables name is never the same. How can I get rid of
the table? Or can I force Access not to create it?

 

[Douglas J. Steele]

I don't believe there's any way to prevent Access from creating it.

You know the name of the import file, though, so you should be able to
figure out the name of the error table. Worst case, make an array of all of
the tables before and compare it to the list of tables after.

On the other hand, the following query should return a list of your tables
in reverse order of creation (i.e.: the most recently created table will be
first)

 

[aaron.kempf]

Dim tbl As AccessObject

For Each tbl In CurrentData.AllTables
If tbl.Name Like "*_ImportError*" Then
DoCmd.DeleteObject acTable, tbl.Name
End If
Next tbl

 

[Douglas J Steele]

Not sure that'll necessarily work if there are two _ImportError tables in a
row.

Instead, try:

Dim intLoop As Integer

For intLoop = (CurrentData.AllTables.Count - 1) To 0 Step -1
If CurrentData.AllTables(intLoop).Name Like "*_ImportError*" Then
DoCmd.DeleteObject acTable, CurrentData.AllTables(intLoop).Name
End If
Next intLoop

At least, I think that's how you'd do it with the AllTables collection: I
always use the TableDefs collection (and I only have Access 97 available to
me at present, so I can't test)

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 tdfCurr.Name Like "*_ImportError*" Then
DoCmd.DeleteObject acTable, tdfCurr.Name
End If
Next tbl

 

[Guest]

Ok, now how do I call this from a macro? Do I have to give it a function name
or something?

I'm pretty good with Access queries but I never really learned how to write
functions, so forgive me if I ask dumb questions, lol.

 

[Guest]

That first one worked perfectly, thanks! I'm not concerned if there are two
ImportError tables. If I did the macros right they should delete the tables
every time they're ran, and the import macro is the only way to import the
data automatically, so there should only ever be one.