What does subscript out of range mean?

  • Thread starter Thread starter Guest
  • Start date Start date
I have just came accross the same issue. I tried all suggestions mentioned in the thread and still had the same issue so I decided to write my own experience.

I recall my database was split into 2 other databases so users could access it at the same time. I then went to one of the split db and tried importing the Excel file there. It WORKED! Then I went to the other split db and imported the Excel file there, it worked again. Finally, I went back to my initial db and imported the Excel file. Bingo, it finally worked w/o any problem!
 
Causes
1. Field Types don't match
2.Extra Rows and Columns
3.Existing Relationships that "enforces data integrity" prevents Access from appending the table due to entires on your records that are not yet present on your other related tables. Temporarily un opt the "enforce" box. Append. Find the new entries, update the other tables, then reinforce the data integrity option again.
 
Well, I had the same problems as above today: "subscript is out of range" window will pop up and couldn't upload my data from Excel into Access! grrrr:o I tried most of the examples above given as advice and nothing. So, having done that and nowhere else to look, I closed Access, rebooted the computer, and GUESS WHAT? It worked perfectly as it has in the past!! :bow: Try this! it might work for you too!
 
Looks like an old thread but.. I just ran into this problem recently and the best solution I found was similar to what another user mentioned, import your excel data into a new table in access then run an append query and append the data in the new table to your existing table. Obviously if this is something you need to do frequently you would want to automate the process of importing the data, create a delete query to keep the temp table free of clutter the create a firm append query with well defined logic to move the freshly imported data from the temp table to the master table.
 
It seems from the other posts in this thread that access is very picky about excel formatting when trying to import excel files. I have had the same problem with even the most basic of excel files.
I think the best solution to this problem is to forget abut importing directly as an excel fiel with all its formatting guff. Save the excel file as a simple CSV(MS-DOS) (comma seperated text file). then in access import as text. I also think its best if the excel/csv file has a single row header with the names identical to the access fields.9 times out of 10 when transferring data from one database to another, CSV format is the most robust option.
 
Well for what it's worth, my problem was solved by checking the format of True/False,Yes/No fields in the Access Db. In the Excel Spreadsheet True/False had been entered for the field value. Access 2013 throws the 'Subscript out of Range' error if the field format is set to Yes/No.
I would guess that the opposite is also true i.e a True/False field format will not accept an imported Yes/No
 
After trying a lot of the suggestions here and checking all of my data I finally just closed an reopened the database before importing. I am importing several tables and there is enforced referential integrity so maybe that was causing the problem,

Anyway, closing the database between each import allowed me to import without the error so maybe it updates some indexes or something only on a close.
 
Back
Top