Solved

Memo field in linked SQL table

Posted on 2012-03-20
10
789 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 100

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
IIF help, YN field 7 22
is Microsoft Access going to Die? 9 53
Alternative of IN Clause in SQL Server 3 21
ORA-02288: invalid OPEN mode 2 31
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

810 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