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][2], 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][1].
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()`.
###References
* My question [Inserting NULL in an nvarchar fails in MSAccess][2] on [StackOverflow][3].
* A reference I found on this issue: [Ms Access linking table with nvarchar(max)][4].
[1]: /2009/ms-access-upsizing-to-sql-server-2008/
[2]: http://stackoverflow.com/questions/979269/inserting-null-in-an-nvarchar-fails-in-msaccess/
[3]: http://stackoverflow.com/
[4]: http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/c6d2466e-ecb5-4a98-963f-ae827dbf8caa
Comments
Something changed! I have two copies of Access 2003 linked to two 2008 servers one will odbc OK and edit, the other wont. Have looked side-by-side to not find any difference. they are all on the same domain
When trying to resolve this issue, I discovered that if you are using ODBC links to update you MUST set the Commit Mode to Commit Immediately (*NONE). You will find this in the ODBC Data Source Administrator, under System DSN, configure, server, and advanced.
I found that switching to the “SQL Server native client v10.0” driver corrected this problem. Also, there appears to be a significant improvement in performance. The “SQL Server” driver is quite dated and still around for backward compatibility, but who’s still using SQL 2000 / 2005?
more properly described, the solution you found doesn’t really have anything to do with “error 3155”, which is returned by MS Access for a host of sql-based failures. 3155 can be just about anything.
Comments are closed.