Wednesday, April 11, 2012

"Subscript out of range?" My Ass, MS Access

I HATE Microsoft Access. It should be like a logical extension of Microsoft Excel that can help you organize more complex types of interrelated data. Instead it's just a mess that's really hard to use, and it's a super pain to troubleshoot when you get some really unhelpful error message like,

"Subscript out of range"

when you're trying to import an apparently perfectly formatted and matching set of data from an Excel worksheet into an existing table in your Access database. Oh, there are suggestions in the online help forums and stuff for what to do in that case. E.g., "Go into the Excel worksheet and make sure there aren't any funny formattings or hidden values in cells outside the range of data that you want to import." But sometimes you try everything and still keep getting the horrible error message.

Here's one stupid trick that works for me sometimes when all else fails:

1. Forget trying to import the data into the table you want to append. Just import it into it's own fresh table, which you can give a name like, "deleteme."

2. After importing, highlight and copy the data in the "deleteme" table and paste it into a pristine new Excel worksheet.

3. Delete the "ID" column from the data you just pasted into the Excel worksheet. Now that Excel worksheet probably looks exactly like the one you were originally trying to import, but somehow, in some secret, arcane way, it's different, and Access won't balk at it.

4. Import it to Access. If you're lucky, it works and you have successful outwitted that @#$% "Subscript out of range" message.

5. Delete the "deleteme" table because you don't need it anymore.

4 comments:

Johnny Douglass said...

This kind of stuff is why I have used STATA instead of Excel or Access whenever I'm able to.

வேடபட்டி .மு. சிவக்குமார் said...

Thats very fine... It worked very well thank you...

Rob said...

You are a genius! Thank you!

Scott Nadeau said...

Thank you. This fixed my problem and I made my deadline!