External data messing up the data type ...

[John Smith]

Hi:

I am trying to "Get External Data" by "Link Tables" in MS Access 2003 from
Excel 2003 spreadsheet. After linking, I opened the table to notice that
one of the fields is TOTALLY screwed up. It displays " #Num! " in some of
the records. I checked the design of the table and it shows that the field
is "Number" . HUH? I checked the format of the Excel sheet and the format
is "Text" for that column. Where does this link manager get off determing
the data type, especially when it is totally wrong?

Can anyone help me to either set up the Excel column to actually be "Text"
and/or the table to reflect "Text" and NOT "Number"?


Thanks

 

[John Nurick]

Hi John,

This often happens when a column in Excel contains a mixture of text and
numeric values. When linking or importing, Access assigns the data type
of the corresponding field by looking at the contents of (the first rows
of) the column. Confusingly, the rules it uses for this are different
when importing and when linking, and it doesn't always get the right
answer. It pays little if any attention to the format of the Excel
cells, only to the actual data they contain.

One reliable solution is to prefix the values in the Excel cells with an
apostrophe' . This forces both Excel and Access to treat them as text.
The apostrophes don't show up in either the worksheet or the table.

For information about just how Access assigns field types and how to
influence it via registry settings, see the Access 20003 help topic
"Initializing the Microsoft Excel Driver" and
http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/