Problem
I had the above problem when I was trying to import the Excel data and append it to an existing table in Access 2010. The database was original created in Access 2003. I changed the Excel file to csv format and re-imported it, and still got the same error message. Then I imported the Excel data into a brand new table, and it went well. However, the database I was working on had a lot of tables and the relationship established was pretty complex. The last thing I wanted to do is to create the new table and re-establish all the relations and queries. So here is what I did:
Solution
Apparently, Access 2010 assigns different data format on certain data fields than Access 2003. I did not know which field’s setting was not correct, so I tried a way that was not quick but will surely solve the problem. I imported the Excel data into a brand new table, and checked the settings for each field as shown below:
Then I altered my existing table’s field settings to be exactly the same as the new table. After the alteration, I imported Excel spreadsheet data into Access without any more problem.