• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 829
  • Last Modified:

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
0
kevin1983
Asked:
kevin1983
2 Solutions
 
peter57rCommented:
According to MS's rules, nvarchar(max) should always be treated as Memo by Access.
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
0
 
NatchiketCommented:
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.
0
 
kevin1983Author Commented:
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Jeffrey CoachmanCommented:
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
0
 
kevin1983Author Commented:
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
0
 
Jeffrey CoachmanCommented:
Ok, sorry, I was looking at this from the "Access end...
0
 
mlmccCommented:
YOu should be able to create a view that uses substring functions to get the first (left) 250 characters.  That should be useable in Access.

mlmcc
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Every time I see Access treat a memo field as a text field is when it as  truncated to 255 characters. For example a Group by  will truncate a memo field.

I would make a view in SQL server or a query in Access that truncated the memo field to 255 or 50.  For example in an Access query:   Left([myMemoField], 255) as MyMemo255
0
 
kevin1983Author Commented:
Thanks a lot, I created a view in SQL and linked in access, the access query suggestion sounds like a good idea also
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now