If you move data from Excel to Access on a regular basis, you'll know that sometimes Access can't resolve the data being imported from Excel. In these cases Access automatically creates a new table called ImportErrors..
.
The ImportErrors table is supposed to log all the data points that failed to be imported. For example, imagine I have an Excel table with two columns Customer Number and Contract Number. Each column contains numbers, but let's say some entries are "NA". When Access tries to import this table, it'll interpret the two columns as Number columns and will throw out the NA values..
.
In a gesture of good will, Access will create an ImportError table to tell you which data points were ignored. Here's what this table looks like:
Well, this is semi-useful….sometimes.
Often times though, the errors are legitimate and benign (like "NA" being ignored in a Number field). But Access, like an OCD Uncle, continues to push out the ImportError tables.
After a few imports, your database will start looking like this:
..
If you have an automated import feed coming in every Month, you'll eventually end up with a database full of ImportError tables..
.
Before you ask, NO there isn't a way to suppress the creation of ImportError tables. What you can do, however, is run a procedure to delete all ImportError tables. The idea is to run this piece of code after your automated import processes. This way, your database won't be inundated with ImportError Tables.
-
Function DeleteImportErrorTables()
-
Dim oTable As DAO.TableDef
-
-
For Each oTable In CurrentDb.TableDefs
-
If oTable.Name Like "*ImportErrors*" Then
-
CurrentDb.TableDefs.Delete oTable.Name
-
End If
-
Next oTable
-
-
End Function
.
.
So OCD Access can keep creating its ImportError tables and you can automatically blow them away. You're both happy.
Ideally you'll want to check if the import data can be "fixed" to avoid the problem altogether.
Hello
Your blog has saved me manytimes - thank you so much for that !
But now I am completly stuck ans was wandering if you perheps had the answere? (and could write a post about it)
I have made a linked table query (as you descriped in you pulitzer post
) but my problem is that I need the table to be linked both ways that is so thatthe changes made in each workbook cnahnge tha query and than tha query changes all tha workbooks to (they are all itentical)
Is this even possible?