Access: Run-time Error 3155 ODBC insert on a linked table failed

Thursday, June 11, 2009

Microsoft Access 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().

References

Entry Filed under  :  Database,MSAccess,Programming,SQL Server

3 Comments Add your own

  • 1. Rx  |  March 4th, 2010 at 10:27 am

    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

  • 2. James  |  May 12th, 2012 at 6:20 am

    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.

  • 3. progers  |  June 5th, 2013 at 3:23 am

    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?

Leave a Comment

(Will not be shown)
Notify me of follow-up comments via e-mail

Some HTML allowed:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Trackback this post  |  Subscribe to the comments via RSS Feed


about

Renaud This is a simple technical weblog where I dump thoughts and experiences from my computer-related world.
It is mostly focused on software development but I also have wider interests and dabble in architecture, business and system administration.
More About me…

My StackOverflow Profile
My (sporadically active) StackOVerflow account

Most Recent Posts

Categories

Links