sql server - 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?

Answer 1

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.