Results 1 to 6 of 6
  1. #1
    Jorgedo99 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    3

    Question How to solve Access limitation of a memo field mapped to nvarchar(max) field in SQL

    Hello,



    Our company is migrating a Microsoft Access 2010 backend database to a SQL Server 2008 database. One of the memo fields in the Access backend can store up to 150 Kb of Unicode data. To store this data in SQL server, we found that we can use the following data types (http://msdn.microsoft.com/en-us/library/ff848814.aspx):

    ntext = (2^30) - 1 = 1,073,741,823 bytes.
    nvarchar(max) = (2^31) - 1 = 2,147,483,647 bytes.

    Because ntext will be deprecated in future releases of SQL Server (http://technet.microsoft.com/en-us/l.../ms143729.aspx), the only good alternative to store an Access memo field in SQL server is to use nvarchar(max), which is what Microsoft recommends for large Unicode texts.

    Storing a large amount of text like 150 Kb in an nvarchar(max) field using only SQL server works as expected. However, if Access is used to store the data in a table linked to SQL server, the maximum number of characters allowed is only 4000. We found that this limitation is imposed by the ODBC driver that limits nvarchar(max) to 4000 characters.

    The connection string we are currently using to link a table to SQL server is this:
    ODBC;DRIVER={SQL Server Native Client 10.0};SERVER= SQLEXPRESS;DATABASE=TestDB;Trusted_Connection=No;U ID=uid;PWD=pwd;

    Has anyone found a solution for this limitation storing large amounts of data in a Microsoft Access memo field mapped to an nvarchar(max) data field in a SQL Server database?

    Thank you,
    George


  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Write the memo fields out to txt files and store the path in the text field?

  3. #3
    Jorgedo99 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    3
    Thank you for replying. The problem with that solution is the amount of text files we're going to end up having in the folder. That solution might work but it kind of defeats the purpose of having a database. There must be a better way to use a memo field in Access.

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    It has been a while since I read this thread but I will venture a guess the issue you are experiencing is the limitation of your ODBC connection. So, if you can not update your SQL tables directly from Access using the current ODBC drivers, you will need an alternative method.

  5. #5
    Jorgedo99 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    3
    SOLVED!

    If you create the following table in SQL Server 2008 and link it to Ms Access, the only way to copy-paste a very large amount of text in Access into the memo field is by using code. Opening the linked table in view mode and copy-pasting the text manually causes the error “ODBC call failed. [Microsoft][SQL Server native Client 10.0]String data, right trunctation (#0)”

    create table tblMemo (
    pkey int not null,
    sqlmemo nvarchar(max),
    primary key (pkey)
    );

    We found that the problem is NOT the ODBC but the driver. If you link the table using the {SQL Server} driver instead of the native client version, you will be able to update the memo field in the linked table using code. We used the following ADO code in VB and were able to update the memo field up to 800K, which is much more than what we really need:

    Dim adoCnn As adoDB.Connection
    Dim adoRst As adoDB.Recordset

    Set adoCnn = New adoDB.Connection
    adoCnn.Provider = "MSDASQL"
    adoCnn.ConnectionString = "DRIVER={SQL Server};SERVER=SQLEXPRESS;DATABASE=MyTestDB;Trusted_Conne ction=No;UID=myuid;PWD=mypwd;TABLE=dbo.tblMemo;"
    adoCnn.Open

    Set adoRst = New adoDB.Recordset
    adoRst.Open "SELECT * FROM tblMemo", adoCnn, adOpenStatic, adLockOptimistic
    adoRst.AddNew
    adoRst!pkey = 1
    adoRst!sqlmemo = <assign variable here with large amount of text>
    adoRst.Update

    adoRst.Close
    Set adoRst = Nothing
    adoCnn.Close
    Set adoCnn = Nothing

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Thanks for posting your solution. Glad you were able to find an elegant way to handle things.

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

Similar Threads

  1. Replies: 1
    Last Post: 07-12-2013, 04:43 AM
  2. Replies: 7
    Last Post: 09-23-2012, 06:18 PM
  3. How to Export Access memo field 87,000 bytes
    By JosmithTwo in forum Import/Export Data
    Replies: 3
    Last Post: 08-10-2012, 02:16 PM
  4. Excel Import field truncation problem (to Access Memo field)
    By jhrBanker in forum Import/Export Data
    Replies: 6
    Last Post: 07-27-2012, 08:52 AM
  5. Replies: 5
    Last Post: 06-19-2012, 10:46 AM

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