I have been spending a lot of time trying to find out why some of the code used to insert new records into a linked SQL Server table would systematically fail with an error:
Run-time Error ‘3155’ ODBC–insert on a linked table failed
It was driving me mad.
I could insert a simple record using _SQL Server Management Studio_, I could add new records to the table in datasheet mode within Access, but as soon as I tried to insert a record from code, whether using DAO recordset or executing the same SQL INSERT, it would miserably fail.
After a fair bit of investigation and tests, of which you can read the full account on [the question I asked on StackOverflow], it turns out that this is a long-standing bug in the ODBC Driver (or Access).
`Memo` fields in Access are usually translated into `nvarchar(MAX)` in SQL Server by tools like [SSMA].
Unfortunately, when you link tables having these fields using the _SQL Server Client_ driver, these fields get incorrectly interpreted as `string`, even though they appear ok from the table design view.
It’s only if you try to insert something into the field, either text larger than 255 chars or NULL, that you get the error message.
So, the solution, at least in this case, is to revert to the older _SQL Server_ ODBC driver instead, or use `varchar()` instead of `nvarchar()`, but if you’re dealing with Unicode, you have to stick with `nvarchar()`.
* My question [Inserting NULL in an nvarchar fails in MSAccess] on [StackOverflow].
* A reference I found on this issue: [Ms Access linking table with nvarchar(max)].