The other day I was importing some data into Access from an Excel file. I got a “Subscript out of range error”. This is a pretty infamous error that Access throws up quite a bit when importing data, and there are numerous causes.
Some are pretty logical, like having text data in a column you’ve designated as a number, or having invalid column names that you’re trying to turn into field names.
Sometimes however, it just seems like it’s astrological, related to the weather, or what I had for breakfast.
But I just figured out a trick. It turns out I was trying to import data into an existing table. I’d created the table, and defined fields how I wanted them and made sure the spreadsheet had fields that matched. However when I tried to import I got an error.
I struggled with the usual nonsense – checking for invalid entries, deleting innocently-empty looking rows and columns, and looked for trailing whitespace. No luck.
So for kicks I tried to import the file and instead of importing it into a new table, I just had Access create a new table. It worked. Hmmm.
Checking out the difference between the two tables it seems that some of the fields I’d designated as integers in the table I’d created were actually imported as float values by Access. I changed the fields in my table, did another import, and it worked. Hurray!
Related posts:
