I posted a question on another forum, but I'm hoping you will have an answer for me. Here is the link: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f8e049f2-8eb4-4df8-93df-2a90559b3bab/odbc-error-4000-character-limit-from-access-to-sql-server-2008-r2-using-sqlncli-or-new-microsoft?forum=accessdev
I get a 3146 ODBC error when I try to save 4000+ characters from Access to SQL Server 2008 R2 Express to a nvarchar(max) column. This is using either SQLNCLI 10 or 11 or new Microsoft ODBC Driver 11 for SQL Server. I have no problem using SQLNCLI saving 4000+ characters to a ntext column. I can save up to 4000 characters just fine with the nvarchar(max) column, so I do not have the 255 character limitation. I am using DAO recordsets. I also tried updating the Service Packs, and upgrading to SQL Server 2012 Express and I'm pretty sure I tried SQL Server 2014 Express.
The DB Engine error says
Error number: 0
[Microsoft][ODBC Driver 11 for SQL Server]Connection is busy with results for another command
Error number: 3146
I also tried sp_tableoption N'tblMyTable', 'large value types out of row', 'ON' without success.
If you're looking at the link, Van Dinh's reply 9/23/14 was informative.