techworkssolutions
asked on
Need VBA Code to Handle Access Database Import Errors from "_ImportErrors" tables
I have a small Access database that is used monthly to analyze invoice data that is received as a CSV file from one of our large vendors. I have created a import routine that allows the user to browse to the CSV import file and import it into the database into a specific table where queries and reports can then be run. Prior to the import process the code deletes all records from the table so that it is blank prior to the import. Everything works great however occasionally one of the CSV files that comes from the vendor's system has rouge text or a formatting issues in the wrong column that causes a Import Error table named "csv-file-name-here_Import Errors" to be created. There are typically only 2 to 5 errors per CSV file that is imported and each CSV file has hundreds of records in it. What I would like to do is find a way to :
A) Enable a user to easily view the errors after the import so that they know that a few of the records didn't get imported and can see which ones they were. This could just be a report that shows the data from the ImportErrors table. The problem there is that each file imported creates an error table with a different name so the report or query will have to use a wildcards or perhaps somehoe the Right() function in the data source name to find all the tables with _ImportErrors as the last 13 characters.
B) Delete all tables that contain the characters _Import Errors in the table name to flush out the error tables so that they dont build up in the database over time. This could be a simple button that the user can click occasionally to perform the deletion or it could be something that happens each time the database is first opened, thus deleting the error table from the previous session.
Thanks!
A) Enable a user to easily view the errors after the import so that they know that a few of the records didn't get imported and can see which ones they were. This could just be a report that shows the data from the ImportErrors table. The problem there is that each file imported creates an error table with a different name so the report or query will have to use a wildcards or perhaps somehoe the Right() function in the data source name to find all the tables with _ImportErrors as the last 13 characters.
B) Delete all tables that contain the characters _Import Errors in the table name to flush out the error tables so that they dont build up in the database over time. This could be a simple button that the user can click occasionally to perform the deletion or it could be something that happens each time the database is first opened, thus deleting the error table from the previous session.
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You could just import into a staging table where all columns are data type text. This should stop the import errors from occuring. Then you can append from the staging table.
ASKER
capricorn1's solution works with some slight modifications but I would rather have a pop up form or report open based on the error table(s) that displays the import errors in a more user friendly format and layout. How can I query the ImportError tables from within the code and open a report based on that data?
Private Sub Check_Import_Errors()
Dim tbl As TableDef
For Each tbl In CurrentDb.TableDefs
If InStr(tbl.Name, "ImportErrors") <> 0 Then
If MsgBox("Some records encountered errors during import and were not imported do you wish to view the errors?", 36, "Import Errors Encountered!") = 6 Then
'Yes was clicked so open the error table to view the errors...
DoCmd.OpenTable tbl.Name
Else
'No was clicked so delete error tables and exit...
'Deletes any Table in the database that contains the string "ImportErrors" in its name
CurrentDb.Execute "Drop table [" & tbl.Name & "]"
Exit Sub
End If
End If
Next
End Sub
ASKER
Any one know how I can create a query that looks at any table with ImportError in the name from within the VBA code and open a report based on that data?
ASKER
Thanks! This worked well. Additionally I would like to have a pop up form or report open based on the error table(s) that displays the import errors in a more user friendly format and layout. Do you know how can I query the ImportError tables from within the code and open a report based on that data? I would like to create a query that looks at any table with ImportError in the table name from within the VBA code and open a report based on that query. Thanks again.