Excel Linking Problem

[LGC]

Hello all,

I am having trouble linking to an excel worksheet. One column is
predominately numerical but must be linked as text. It is formatted as text
in the worksheet. Access insists on converting this column to a number
field. In an attempt to correct the problem, I inserted some temporary rows
as the top of the worksheet and entered text in these rows. Upon linking,
Access correctly assigns the field as text, however, when I deleted the
temporary rows, the linked table field reverted back to a number. I will
likely end up using ADO to access this worksheet, but I'd sure like to know
how to work around this "feature".

Thanks for any input.

LaVern

 

[Jamie Collins]

...

Spreadsheets are much more flexible in their
data types and even when you specify a data
type for a column, you can still insert data that
deviates

That depends on how the column's data type was defined and how the
insert is being performed e.g.

CREATE TABLE
[Excel 8.0;Database=C:\dbs.xls;].Sheet1
(
MyDateCol DATETIME
)
;
INSERT INTO
[Excel 8.0;Database=C:\dbs.xls;].Sheet1
(MyDateCol) VALUES (9999999)
;

The insert fails with an overflow because the value can't be coerced
as a date. Within the Excel UI, a similar effect can be achieved using
data validation (Data, validation).

Therefore, Access can't trust the given data type
and so attempts to determine it for itself.

Technically speaking, this is a Jet process, not something MS Access
does. The following explains some of the details:

http://www.dicks-blog.com/excel/2004/06/external_data_m.html

Jamie.

--