Thursday, June 11, 2009
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
- 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).