Results 1 to 2 of 2
  1. #1
    Phred is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2009
    Posts
    246

    Wink Access Memo field to SQL Server 2008 R2 Nvarchar(max) planning issue.

    This is a brand new development. I am in the Architectural Design phase. Access 2007 front end. Front end will be compiled and will connect via ODBC to a SQL Server 2008 R2 back end, which is also under development. There is no legacy data to deal with or any legacy Access database to migrate. This is a totally new, clean, and empty development. My client wishes to make extensive use of memo fields so my question here is preventative in nature.

    The MSDN recommendation and SSMS recommendation is to map the Access Memo field to a SQL Server nvarchar(max) and NOT to varchar(max), text, or anything else. I have searched a number of sites and MSDN and see lots of problems mentioned even with nvarchar(max), with no clear solutions. One mentioned an ODBC limit of 8000 bytes. Most mention truncation problems past 255 characters related to a variety of issues.

    What I don't see so far is a clear, "Do this and it works great!" answer.

    There is no code or data to show you. I am looking for insight from the 20,000 foot level.

    An ounce of prevention is worth a pound of support, says it all.

    Phred

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    the limit isn't on the SQL side, it's on the Access side.

    A memo field in access can only recognize about 65,000 characters.

    on the SQL side varchar(max), text (2gb limit), nvarchar(max), ntext (2gb limit) all have the ability to go way beyond that 65,000 character limit

    I haven't used all these data types but if you are linking a table that has more than access can display you're definitely going to end up with some truncation issues.

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

Similar Threads

  1. Access 2010 to SQL Server 2008 R2
    By bigroo in forum SQL Server
    Replies: 0
    Last Post: 03-21-2013, 07:02 PM
  2. Access 2010 through VPN and Win Server 2008
    By feguillen in forum Misc
    Replies: 1
    Last Post: 12-01-2011, 06:20 PM
  3. Replies: 0
    Last Post: 01-19-2011, 04:20 PM
  4. Can't connect Access 2010 to SQL Server 2008 R2
    By LAazsx in forum Import/Export Data
    Replies: 6
    Last Post: 12-10-2010, 08:44 PM
  5. .adp in access 2007 to SQl server 2008
    By NoellaG in forum Access
    Replies: 5
    Last Post: 09-07-2010, 09:18 PM

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