Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Importing from Excel to Access - need to handle exceptions

Avatar of julescantwell
julescantwell asked on
Microsoft Access
1 Comment1 Solution416 ViewsLast Modified:
I have inherited an Access database from someone I no longer have contact with. There is a form which the user uses to import access spreadsheets into the db, with each worksheet in the spreadsheet going to a certain table based on its name.

If I try to import a file when Excel is open, even if it's not actually that file that is open, it basically crashes. I think I have identified where the exception handling code should go, but how do I handle this problem?

Also, I have a separate issue when I click the Append button to copy the imported data from the Import tables to the data tables - I get a few prompts asking me to enter values for certain fields. I believe this happens when those fields are missing, but it's not a problem if they are missing. Also, I can import say, 5 sheets from one file to 5 different import tables, and then click Append at the end to copy the data in all 5 import tables to the relevant tables, but even if I only import 1 table, for which I know there are no missing fields, I still get a prompt to enter fields from other tables.

There is one macro mcrImport which is triggered by the Append button, and it calls several queries which copy data from the import tables to the data tables. Any idea how I can get rid of the prompts?

Also, if the user accidentally selects a worksheet with a header row only and no data rows, when they try to import it, it goes into what seems to be an infinite loop. Is there code I can add to handle this case?

Many thanks.
Avatar of Rey Obrero (Capricorn1)
This problem has been solved!
Unlock 1 Answer and 1 Comment.
See Answers