ODBC INSERT on linked SQL Server tables in Access Fails

Description

In version 2604 Build 19929.20090, an attempt to insert text into nvarchar(max) fields in linked SQL Server tables (Long Text fields in Access) fails with an error message similar to this:

Status

The Microsoft Access team has indicated that they can reproduce the bug and have identified the cause. The bug happens with the SQL Server driver that comes with Windows.

There is an ongoing discussion about whether it occurs, in some cases, with the ODBC 17 Driver for SQL Server.

You should not see it with ODBC Driver 18 for SQL Server.

The issue does not occur in version 2603.

Workaround 1

Use a modern driver, ODBC Driver 18 for SQL Server.

Workaround 2

You can set the following registry key

HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Common\ExperimentEcs\Overrides\Microsoft.Office.Access.UseOutputInsertedForOdbcIdentity = "false"

Update. A poster on Reddit has confirmed the registry key workaround resolved the issue for him.

Workaround 3

A temporary remedy is to roll back to version 2603 of Office/Access in which the problem does not occur.

If you encounter a variation of this problem, especially if you can confirm the issue occurs with ODBC Driver 17 for SQL Server, feel free to describe your specific issue in a comment on this article so that we can inform Microsoft about it.