Results 1 to 4 of 4
  1. #1
    JJJJJJJJ is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2016
    Posts
    5

    Linked SQL Server tables how to display data in access from a nvarchar(max) SQL field.

    I have a SQL Server 2012 linked table that has a nvarchar(max) SQL field.



    When I open the table and look at the data I can see all the data but the data in the nvarchar(max) field. The nvarchar(max) field just says OLE object not the actual data within this field. BTW the test data is only 20 - 50 bytes not the multi K data that will be going in there during production.

    How can I get access to show this data?

  2. #2
    nrgins's Avatar
    nrgins is offline some guy
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    62
    Create a view on the SQL Server side to convert your field to nvarchar(255), and then link your view instead of the table, and Access will bring it in as a text field. Otherwise, it will be an OLE object.

    If you need more than 255, than you can do nvarchar(2000), and I believe Access will bring that in as Memo

  3. #3
    JJJJJJJJ is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2016
    Posts
    5
    What if I need larger than 2,000 characters (right now I do not need larger than 2,000 but in the future I will)? Also would I be able to insert new data?

  4. #4
    nrgins's Avatar
    nrgins is offline some guy
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    62
    Well, you could do nvarchar(4000) instead of 2000. 4000 is the upper limit of nvarchar(max), so you wouldn't be able to do more than 4000 anyway.

    And yes, you should be able to insert new data. No reason why a data conversion would prevent that.

    But... I just looked into a little, and it seems that Access is supposed to interpret nvarchar(max) as memo, which makes sense (even though memo is unlimited, unlike nvarchar(max)). So there must be something else going on here.

    Could be an Access 2013 glitch. I read another post where someone was having problems with data type conversions from SQL Server in A2013. That was the first version where Access changed its data types a bit, so there might be some sort of glitch. They resolved it by doing a type conversion in SQL Server. So you might try converting to nvarchar(4000).

    BTW, here's the Microsoft article which states the nvarchar(max) is supposed to be interpreted by Access as Memo (but it was written for A2007):

    https://support.office.com/en-us/art...rs=en-US&ad=US

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 08-20-2013, 11:25 AM
  2. Replies: 1
    Last Post: 07-12-2013, 04:43 AM
  3. Replies: 0
    Last Post: 05-21-2012, 07:00 PM
  4. Replies: 3
    Last Post: 05-17-2012, 05:41 AM
  5. Replies: 3
    Last Post: 01-03-2012, 12:28 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums