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

[jorgedo99]

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:

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, 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;UID=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

 

[mdlueck]

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?

I did not, but got bit at a different spot. The place I got bit was in displaying varchar(max) data in a form text field. I banged into a crash out beyond "65,535" aprox characters... then later found that the amount of form text able to be held by a text area control was indeed that very number.

VBA itself is able to deal with the entire text string, just not display it on a form. So I implemented a truncation for form display, and allow the full data to be passed through VBA to the SQL BE DB just fine.

 

[jorgedo99]

Thank you for replying. We had a similar problem but resolved it with a third party text control. There must be a work around to mapping a memo field from Access to SQL server.

 

[jorgedo99]

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_Connection=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