Access import trick: Make sure columns are wide enough

So there I am trying to import data into Access, and I’m getting the dreaded subscript out of range error. Grrr….

I went through the usual fixes, but nothing worked. Then I decided to investigate a little. I imported the data into a new table, and then compared the field types of that table with the table I’d been trying to append the data to. Turns out that two date fields were being imported as ‘nvarchar’ instead of ‘datetime’. Hmmmm. I look at the data. All the values in these date fields are ‘######’.

If you import an excel sheet with a column narrow enough that the data shows up as ‘######’ in Excel, the data will be imported as ‘######’. How stupid is that?

Turns out those two date columns were just narrow enough to show the values as ‘######’, and that caused them to be imported as the wrong datatype.

[UPDATE 1-30-2010: Whoops! Turns out that it was a macro I wrote to depivot data that somehow takes the #### literally. Still, it pays to make sure columns are wide enough]

Related posts:

1. Another trick for bypassing unknown error on Access data import
2. Avoiding errors when importing data into Access.
3. Another Access Trick
4. Another trick for avoiding an Access import error
5. Access data import is idiotic

Tagged as: Access, Excel, import data, Subscript out of Range, Unknown Error