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]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s