Access Import from Excel not working

Murray Brown
Murray Brown used Ask the Experts™
on
Hi
I am getting the following error when trying to import Excel data into Access with no explanation of what is causing the error. Is there a way to get more detail than this

1
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
probably you could start by chopping the Excel...maybe a field was interpreted erroneously and
'bombs out' on import
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
I'd start out by linking to the Excel sheet.

You can then examine the data types that Access is assigning to each of the Excel columns.

When I import from Excel, I generally do it to a staging table, which contains all of the fields in the Excel spreadsheet as well as a couple of others.  Each of these fields is defined as text, which allows me to read all of the records without getting any errors.  Then I run a series of queries to make sure the data in each column matches the data type that it should in order for me to push it into my production tables.

If all those queries run without raising any flags, then I run an INSERT query that uses type conversion functions to change the data from the text that I imported it as into the correct data type.

HTH
Dale
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
thank you
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
glad I could help
FreddSharePoint Jedi

Commented:
you can have pretty problems here if the excel file has non printable characters other than line feed etc I learned over several weeks of scratching my head. usually though when you import or copy but not linking. until you touch a field with said characters.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial