Link to home
Start Free TrialLog in
Avatar of techworkssolutions
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_ImportErrors" 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!
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Avatar of techworkssolutions
techworkssolutions

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

Open in new window

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?
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.