access97, sql2005 memo field in a linked table shows as a text field

[Roger]

I've got two tables in sql2005 which have an 'ntext' field
when I linked the first table in access97 last week using an odbc data
source the access-field type was 'memo'

when I link the 2nd table today, it is linked as a text(255) field,
ditto for the first table if I link it today

if I link the 2nd table using access2003 (and the same odbc data
source) it is linked as a memo field

I don't think anything was changed on sql2005 or access97 since
linkage of the first table produce the correct field type

does anyone know what is required to get access97 to link a memo
field ?

[Roger]

On Aug 21, 12:59 pm, Roger <[e-mail]:

I've got two tables in sql2005 which have an 'ntext' field
when I linked the first table in access97 last week using an odbc data
source the access-field type was 'memo'

when I link the 2nd table today, it is linked as a text(255) field,
ditto for the first table if I link it today

if I link the 2nd table using access2003 (and the same odbc data
source) it is linked as a memo field

I don't think anything was changed on sql2005 or access97 since
linkage of the first table produce the correct field type

does anyone know what is required to get access97 to link a memo
field ?

If I change the sql table field from 'ntext' to 'text' and relink, the
access field is now 'memo', but when I display the table contents I
see "#Name?" in all fields

yet if I open a recordset to the table and debug.print all the data in
all rows, it looks ok

note, the sql table has a 'timestamp' field, which I thought fixes the
#Name? problem

recreating the ODBC data source doesn't help

[Roger]

On Aug 21, 2:44 pm, Roger <[e-mail]:

On Aug 21, 12:59 pm, Roger <[e-mail]:

I've got two tables in sql2005 which have an 'ntext' field
when I linked the first table in access97 last week using an odbc data
source the access-field type was 'memo'

when I link the 2nd table today, it is linked as a text(255) field,
ditto for the first table if I link it today

if I link the 2nd table using access2003 (and the same odbc data
source) it is linked as a memo field

I don't think anything was changed on sql2005 or access97 since
linkage of the first table produce the correct field type

does anyone know what is required to get access97 to link a memo
field ?

If I change the sql table field from 'ntext' to 'text' and relink, the
access field is now 'memo', but when I display the table contents I
see "#Name?" in all fields

yet if I open a recordset to the table and debug.print all the data in
all rows, it looks ok

note, the sql table has a 'timestamp' field, which I thought fixes the
#Name? problem

recreating the ODBC data source doesn't help

the sql table actually has 3 'text' fields, and if I delete 2 of them,
the linked table shows it as a memo field, and the table view
correctly shows the data instead of #Name?

note, using one 'ntext' field still fails to create a memo field

so unless someone can provide more insight, I'll create three tables,
where the last 2 will have just a 'text' field, and I'll modify the
form(s)