Changing field type of a linked Excel table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ammo - 02 Nov 2004 16:09 GMT

Hi,

Does anyone know how I can change the field type of an
Excel table that I have linked to my Access database. The
field concerned is a descriptive field that I would like
change from 'Text' to 'Memo'. Hope someone can help.

Best Wishes

Ammo

Reply to this Message

Frank Stone - 02 Nov 2004 17:09 GMT

hi
don't think you can to that.
access wont let you change this property in a linked table
meaning that you would have to change it on the excel side
and excel don't have a memo type.
regards
Frank

>-----Original Message-----
>Hi,
[quoted text clipped - 8 lines]
>Ammo
>.

Reply to this Message

Jamie Collins - 03 Nov 2004 15:18 GMT

> >Does anyone know how I can change the field type of an
> >Excel table that I have linked to my Access database. The
[quoted text clipped - 4 lines]
> access wont let you change this property in a linked table
> meaning that you would have to change it on the excel side

Frank is correct, you would have to make changes on the Excel side.
For details on how the an Excel column's data type is determined, see:

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

In short, you may need to change your registry key to TypeGuessRows=0.

> excel don't have a memo type.

Frank is incorrect, which is easily demonstrated:

CREATE TABLE
[Excel 8.0;Database=C:\temp.xls;].MyTable
(
MyTextCol VARCHAR(255) NULL,
MyMemoCol MEMO
)
;
INSERT INTO
[Excel 8.0;Database=C:\temp.xls;].MyTable
(MyTextCol, MyMemoCol) VALUES (
'A12345789',
'A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789')
;
SELECT
LEN(MyTextCol), LEN(MyMemoCol)
FROM
[Excel 8.0;Database=C:\temp.xls;].MyTable
;

I get 9 and 270 respectively, demonstrating that MyMemo will accept
more than the maximum 255 characters that the text data type imposes.
To demonstrate the reverse is NOT the case, try:

INSERT INTO
[Excel 8.0;Database=C:\temp.xls;].MyTable
(MyTextCol, MyMemoCol) VALUES (
'A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789A12345789',
'A12345789')
;

I now get an error, 'The field is too small to accept the amount of
data you attempted to add.'

In conclusion, Excel can and does distinguish between the Jet Text and
Memo data types.

Jamie.

--

Reply to this Message