troubleshooting Question

Need VBA Code to Handle Access Database Import Errors from "_ImportErrors" tables

Avatar of techworkssolutions
techworkssolutions asked on
DatabasesMicrosoft Access
5 Comments1 Solution1595 ViewsLast Modified:
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!
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros