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!
Microsoft AccessDatabases

Avatar of undefined
Last Comment
techworkssolutions

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Rey Obrero (Capricorn1)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
jmoss111

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
techworkssolutions

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

ASKER
techworkssolutions

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?
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
techworkssolutions

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.