Solved

Memo field in linked SQL table

Posted on 2012-03-20
10
808 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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
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 250 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 250 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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

717 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