Select a Category

Access 2010 Subscript Out of Range at Excel Data Import

Problem

Subscript Out of Range when Importing Excel Data to Access 2010

Subscript Out of Range when Importing Excel Data to Access 2010

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:

New Data Table Structure

New Data Table Structure in Access

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.

Comments are closed.