Changing field type of a linked Excel table

  • Thread starter Thread starter Ammo
  • Start date Start date
A

Ammo

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
 
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
 
Frank Stone said:
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
Click to expand...

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.
Click to expand...

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.

--
 
Back
Top