Moving from text to varchar(MAX): Are there any troubles to expect with MS Access?

Question

It is well-known that MS Access applications (MDBs) using SQL Server backends have trouble with certain data types. For example,

bit field support is broken: NULL values lead to strange errors,
decimal field support is broken: Sorting does not work correctly,
etc.

We are now considering to move from text/ntext fields to varchar(MAX)/nvarchar(MAX) fields, as recommended by Microsoft:

ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

Are we going to run into trouble doing that?

score 2 · Accepted Answer · answered Apr 14 '15 at 15:45

I know this is an older post, but I think it is still relevant to some folks. I deal a lot with legacy data that is scaled up from Access Memo fields to SQL and then turned into a link table in Access.

I have found that scaling to NVARCHAR(max) does cause issue within the link tables. Depending on which driver you are building the Access Link table with, the problem varies.

Using SQL Native Client 10 my first finding is that Access treats the field as a NVARCHAR(4000). While using SQL Server as the driver does change the issues, there are still issues. With this older driver the issues seem to be harder to track down but do show up. Usually with a similar sizing problem.

Beware, what seems to be running ok, may in fact just be running correctly because the right circumstance has not been hit yet.

If you find that your field data never requires more than 4000 characters, then make it a NVARCHAR(4000). To set at MAX is over kill if you only need 4000 anyway.

score 1 · Answer 2 · edited Oct 06 '19 at 00:43

1

We definitely discovered a pitfall recently. The problem is that one couldn't write more than 8000 bytes to a varbinary field of a linked table, even if the field is defined as varbinary(MAX).

Proof: varbinary(MAX) on linked tables

edited Oct 06 '19 at 00:43

Lee Taylor

7,761
16
33
49

answered Dec 05 '12 at 10:48

Alexey Khoroshikh

425
4
10

Andre · Answer 3 · 2019-10-28T14:18:56.527

This is an addendum to the answer by JStevens.

The newer ODBC drivers for SQL Server limit VARCHAR(MAX) to 8000 characters. Trying to enter more text via a linked ODBC table results in this ODBC error:

[Microsoft][ODBC Driver 17 for SQL Server]String data, right truncation (#0)

It works with the ancient {SQL Server} driver, or with data type TEXT.
And surprisingly, it also works with NVARCHAR(MAX) !

These findings are with Access 2010 or 2016, and SQL Server 2008 R2.

+--------------------+--------------+---------------------------------+
| Data type \ Driver | {SQL Server} | {ODBC Driver 17 for SQL Server} |
+--------------------+--------------+---------------------------------+
| VARCHAR(MAX)       | ok           | ODBC Error                      |
| NVARCHAR(MAX)      | didn't try   | ok                              |
| TEXT               | ok           | ok                              |
+--------------------+--------------+---------------------------------+

So you have to pick your poison if you need to insert more data.

{SQL Server} wasn't an option for me, e.g. because it doesn't support the DATE data type.

So I stick with TEXT and hope that "ntext, text, and image data types will be removed in a future version of SQL Server." is just an empty threat.

Necro edit: NVARCHAR(MAX) doesn't seem to have the 8000 (or 4000) character limit with the new ODBC drivers.

score 1 · Answer 4 · answered Nov 03 '11 at 10:35

We are in the same situation: MS-Access front end, SQL Server back end. We are already creating all new fields as nvarchar(max) instead of ntext, without any problem on the user side. As we do not use either text or image field types, I cannot say anything on them.

score 1 · Answer 5 · answered Nov 15 '11 at 21:35

At work, we have the same setup as well (Access 2003 frontend, SQL Server 2005 backend) and we did exactly what you are asking about:

We had SQL Server tables with text/ntext columns, and we changed them to varchar(max)/nvarchar(max).
We didn't experience any problems at all, if I remember it correctly we didn't even have to re-link the tables in Access...it just worked.

Moving from text to varchar(MAX): Are there any troubles to expect with MS Access?

5 Answers5

Linked