Memo field in linked SQL table

[KevinSlater]

Hi,

We have an SQL table named OITM on a server running SQL server 2008 which has a field named: U_MAX_COM which has a datatype Nvarchar(max) and set to allow nulls.

In access (either 2003 or 2010) we link to this table but often the field shows up in access as a memo field, whilst other times it shows as a text field. We really need it to appear in access as a text field because it needs to be linked to another table/field in a query.
I don’t understand why sometimes access sees it as a memo field and sometimes a text field. Does anyone have any ideas what could be causing this? and how to ensure shows as a text field in access?.

(I dont think we can amend the current datatype of the field)
Kevin

 

[Fuse3k]

Does the U_MAX_COM field contain values that exceed 255 characters?

 

[KevinSlater]

No, characters are typically no more than 20

 

[laneebee]

CAST(U_MAX_COM as varchar(255)) and/or make sure SQL field length is 255 or less.

Access translates to a memo field type when the field length exceeds 255.