Detecting import error table (2003 sp3)

Topic

New Reply

I have a routine that imports a large amount of data from multiple spreadsheets. Occasionally an import error occurs so Access creates an import errors table for that particular spreadsheet. I need to identify the spreadsheets which cause the errors so I thought I could add a line that would check for the existence of the import errors table after each loop and if found I would use a message box to give the name of the spreadsheet. I tried to adapt the code Hans wrote in another post relating to this but I have not been successful.

How can I detect the import errors table?

Reply | Quote

Replies

It would have been helpful if you had told us which other post you looked at.

Here’s a snippet of code you might use. It looks if there is an Import Errors table, and if so displays a message and deletes the Import Errors table (otherwise the message would keep on appearing).

Dim obj As AccessObject
Dim strFile As String

...

strFile = ...
' Import the spreadsheet
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "tblImport", strFile, True
' Loop through the tables
For Each obj In CurrentData.AllTables
' Inspect the name
If obj.Name Like "*Import Errors" Then
' Display a message
MsgBox "An error occurred while importing " & strFile, vbInformation
' Delete the error table
DoCmd.DeleteObject acTable, obj.Name
' And exit the loop
Exit For
End If
Next obj

Reply | Quote

Sorry Hans. The post is post 695155. I’m still too much a novice at adapting code.

Reply | Quote

The function in that post tests whether a table with a fixed name exists in an external database. You want to check whether a table with a variable name exists in the current database, so the code is quite different.

Reply | Quote

The snippet worked perfectly. With 301 spreedsheets involved, Hans, you are a life saver.

Reply | Quote