?
Solved

Memo field in linked SQL table

Posted on 2012-03-20
10
Medium Priority
?
814 Views
Last Modified: 2012-06-07
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
Comment
Question by:kevin1983
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 37743112
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
 
LVL 17

Expert Comment

by:Natchiket
ID: 37743128
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
 

Author Comment

by:kevin1983
ID: 37743160
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37743430
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
 

Author Comment

by:kevin1983
ID: 37743488
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37743728
Ok, sorry, I was looking at this from the "Access end...
0
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 1000 total points
ID: 37982711
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
 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 1000 total points
ID: 37982753
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
 

Author Closing Comment

by:kevin1983
ID: 38057212
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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this article, we’ll look at how to deploy ProxySQL.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question