Memo field in linked SQL table
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. 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
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. 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
If you can't amend the datatype then perhaps you can amend the size e.g. nvarchar(200)
The maximum text field size in Access is 255 characters, so that is why it is defaulting to memo.
The maximum text field size in Access is 255 characters, so that is why it is defaulting to memo.
ASKER
Ok, its odd access sometimes shows it as a text field, could a view be created in SQL that uses the original field but gives a new field name with a limit of 255 characters? then I can link to the view
Just note the "realistic" limit of this filed.
The limit of a text field is 255 characters.
The *default* is 50, so many people think that is the limit.
255 characters translates into roughly *3 sentences* worth of text.
This should be more than enough for most applications.
So unless you *really* have a need for more than this, just make the field a Text field in Access.
JeffCoachman
The limit of a text field is 255 characters.
The *default* is 50, so many people think that is the limit.
255 characters translates into roughly *3 sentences* worth of text.
This should be more than enough for most applications.
So unless you *really* have a need for more than this, just make the field a Text field in Access.
JeffCoachman
ASKER
I cant make the field a text field in access as its a linked SQL table and access wont allow changing the field, unless theres a way around this?
50 characters is enough text for what we need in the field, we can add other fields to the SQL table but not amend the current field
50 characters is enough text for what we need in the field, we can add other fields to the SQL table but not amend the current field
Ok, sorry, I was looking at this from the "Access end...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot, I created a view in SQL and linked in access, the access query suggestion sounds like a good idea also
So I think you are facing a losing battle here.
http://office.microsoft.com/en-us/access-help/import-or-link-access-to-sql-server-data-HA010341762.aspx