size of external data

[carl]

My Access97 database is linked to an Excel97 doc. How do I change field size
in my linked table?
Several records contain data bigger than 255 yet Access sets the field size
to "text".

I get errors when I try to query this table "The field is too small to
accept the amount of data you tried to add.Try inserting or pasting less
data..."

I need to set the field size to "memo". How? I don't get this option when
creating/linking the table and I can't do this by designing the table as it
is linked.

 

[carl]

My Access97 database is linked to an Excel97 doc. How do I change field size
in my linked table?
Several records contain data bigger than 255 yet Access sets the field size
to "text".

I get errors when I try to query this table "The field is too small to
accept the amount of data you tried to add.Try inserting or pasting less
data..."

I need to set the field size to "memo". How? I don't get this option when
creating/linking the table and I can't do this by designing the table as it
is linked.

PS: Access sets the field size based on the first row in my Excel doc.
I did a work around by creating a dummy row in my Excel doc with enough text
to force Access to read it as "memo". Whilst this works, I would rather not
have to do this so my question still stands

 

[Jeff Boyce]

Carl

A matter of definition...

In Access, a linked table doesn't exist inside Access. Rather, it stays
where it is and Access looks at it. You can link to data in Excel, but the
definition is in Excel, not Access.

If you are saying you've imported a table from Excel, then the definition is
in Access, and you can change the field size by opening the table in design
mode.

However, Access uses the first few rows to define a default field type/size
when you import a table.

An alternative would be to create an empty table, defined as you wish, then
use one/more queries to load that "permanent" table from either a linked
Excel table or a "temporary" import.

Good luck

Jeff Boyce
<Access MVP>

 

[Tim Ferguson]

An alternative would be to create an empty table, defined as you wish,
then use one/more queries to load that "permanent" table from either a
linked Excel table or a "temporary" import.

Just to add to Jeff's response, if you need a text field with entries
greater than 255 chars, you need to use Memo field.

Hope that helps


Tim F