Text format converting to Number when linked to Access2002

[Mike]

I have a spreadsheet listing some project information for
an engineering firm. The first column lists the project
number and so on. Occasionally, we have a letter suffix
on the project number to designate segments. I wanted to
link this info to Access 2002, and then sort the
information more efficeintly. When I link the
spreadsheet, it always converts the field data type to
number, and I lose some of my data. I have tried to reset
the datatype on the spreadsheet to text and linked it
with no luck. I have also tried to add an ID field to the
spreadsheet with just a number in it, thinking that maybe
Access needs a number format for the first field, with no
luck. Any ideas?

 

[John Nurick]

Hi Mike

If there is a single numeric value in the first 8 (I think) rows of an
Excel column, Access will link it as a numeric field even if the other 7
rows contain text values.

See
http://support.microsoft.com/default.aspx?scid=kb;en-us;208414
for details.

This seems totally crazy and stupid - and it's different from the
behaviour when _importing_ from Excel: then, a single text value in the
first few rows forces the column to be imported as text.

One work round is to put an apostrophe in front of each numeric value to
force Excel (and therefore Access) to treat them as text. IOW replace
123987
with
'123987
Another is (if I remember right) to use Excel VBA to do something like
this, appending an empty string to the end of each number to force Excel
to treat it as text:

Sub Textify()
Dim C As Excel.Range
For Each C in Selection.Cells
C.Value = C.Value & ""
Next
End Sub