Links Between Excel and Access

[Dave]

I'm trying to link an Excel spreadsheet (that contains
both numerical and text data) with an Access table. When
the link is created, Access sets up the "ID" number field
as a numerical field in the table (it appears to look for
numbers and then makes an assumption). I need this to be
read as a text field because of other tables in the
database. When I try to change the type of the field in
the table, Access says that these changes can't be saved.
How do I create a link to this spreadsheet and be able to
specify the type of field.

 

[Ken Snell]

You cannot. But you can "trick" ACCESS into thinking that field is a text
field by either putting as the first row a text (nonnumeric) value in that
cell. You could make this a letter or number/letter combo, or you could put
a ' character in front of the number so that ACCESS will think it's text.

Otherwise, you'd need to import the data into a table that has the correct
format already set for the fields.

 

[John Nurick]

Ken,

AFAIK the trick with putting a text value first works if you're
importing data from Excel not you're linking. According to
http://support.microsoft.com/default.aspx?scid=kb;en-us;208414
if there is a single numeric value anywhere the first 8 rows Access will
assume it's a numeric field even if the other 7 rows contain text
values.

This seems totally crazy and stupid.

You cannot. But you can "trick" ACCESS into thinking that field is a text
field by either putting as the first row a text (nonnumeric) value in that
cell. You could make this a letter or number/letter combo, or you could put
a ' character in front of the number so that ACCESS will think it's text.

Otherwise, you'd need to import the data into a table that has the correct
format already set for the fields.

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

 

[John]

It maybe a slight nuisence but what I do is, say it's
column "C" that has both text and number then I write a
formula in excel in any blank column =C1&"" (no space
between "") copy and drag it down to the end of records.
Now copy the formula column paste value over "C" column
which will convert all value in "C" to text. Delete
formula column.