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
kevin1983Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Jeffrey CoachmanMIS LiasonCommented:
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 CoachmanMIS LiasonCommented:
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 MVPDesigner and DeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.