The database that comes with Office Professional. Access can handle tens of thousands of rows with ease and has great connectivity support for traditional database servers.
Subscript out of range when importing Excel into Access
I have a couple of fairly large Excel worksheets (within one spreadsheet) that I am trying to import into Access. The import wizard says "Subscript out of range" and does not complete the import. I know that this error may have something to do with the size of the array, but I'm unsure what that means.
Could it mean that I'm trying to import too large of a worksheet? The worksheets have between 2600 and 2800 lines/records each.
Any help would be appreciated.
there is very little chance that you can get something into excel that cannot be imported into access... at least as far as the number of rows is concerned.
how many columns?
how are you importing (from .XLS or .CSV)? --- sometimes it pays to take an XLS to CSV before the import to MDB.
are you importing in code or from the menu (i.e. is it your code that is "running out of subscripts", or an access-internal problem)?
I may have solved at least this problem by splitting one of the worksheets in half and importing just that portion of the data into Access. When I tried it this way, I had no problems with the import.
However, I just now see the message from izy to try with a CSV file.
I have 18 columns, and I'm importing from the menu.
I will report back.
When I try CSV, not all the data gets imported. There are a number of errors in several different fields that show up in an ImportErrors table.
great! you solved the problem.
meanwhile, i don't see why splitting the XLS in two changed anything! every month i import a few thousand rows of .CSV sales stats with 12 fields. (actually i don't import, i link to the .CSV and use code to check/reformat/append the data to my master table). i don't get a subscript issue.
the reason i prefer .CSV is the Advanced button on the import dialog - you get some control over how the fields will be handled and you can save the import spec to use with multiple (same format!!) spreadsheets. importing from .XLS lets A make all the decisions.
linking then appending to an existing table (via query or via code) may also be a better strategy - you gain control over field type/size and have a reformatting opportunity. i generally specify all the .CSV fields as text (Advanced button) to minimise the chance of import errors and then reformat in code to suit my table.
18 fields is not an issue, hundreds of fields could be.
...but your problem has gone away. fine!
I redid the CSV import and defined all fields as text, as you suggested.
The CSV format dropped out the leading zeroes on a couple of fields, but I was able to restore the zeroes with update queries.
Then I redefined one field as number in order to use it in linking to another table (my original intent).
The major outcome is I did not get the "Subscript out of range" error with the CSV import (when I followed your suggestions.)
This procedure sure beats all that monkey business with splitting the Excel file.
Many many thanks!