Access limitation of memo field mappped to SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Access limitation of memo field mappped to SQL

Author

Topic

jorgedo99
Starting Member

3 Posts

Posted - 2014-01-23 : 11:19:05

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

jorgedo99
Starting Member

3 Posts

Posted - 2014-01-28 : 11:09:49

Does anyone know a work around to mapping a memo field from Access to SQL server?

jorgedo99
Starting Member

3 Posts

Posted - 2014-01-30 : 11:27:40

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources