none
3155 Insert into Linked Table error RRS feed

  • Question

  • Hi All,

    I've been researching this problem for a few weeks now and have tried many solutions but have yet still to find an answer.

    This is my situation.

    MS Access 2010 front end
    SQL Server 2008 R2 backend
    Linked tables in Access.

    We only get the 3155 error on the one table, Customers, when somebody tries to create a new Customer, i.e. when we need to insert a new record into the Customers table.
    Any updates etc can occur on the table without a problem. As soon as a new record needs to be inserted into the Customers table, it times out after 60 seconds with the 3155 ODBC insert on linked table failed error.

    I have tried relinking the table. This works sometimes but we soon encounter the problem again.
    I restart the database server but the problem comes back after a few new Customers are created. This isn't a solution as I can't keep rebooting the Database server for the obvious reasons.

    I previously experienced this problem a couple of years ago and found it was to do with a nvarchar(max) field in the Customer's table which allowed null values. I changed the field to varchar(500) and made the default value an empty string. This fixed the problem on that occasion.

    Now the problem is back.

    I'm hoping there is someone out there that can point me in the right direction as I am running out of ideas and this is causing us some serious headaches.

    Cheers,

    Chris

    Wednesday, February 5, 2014 3:37 AM

Answers

  • ODBC protocol is generally for big data -- sql server -- which uses "Fire Hose" bandwith.  Access uses (my term) garden hose bandwith (with all due respect for mini RDBMSs) because Access is basically a mini RDBMS (relational database management system) which is also file based.  Unless everything (front/back ends) is set up perfectly and conditions are ideal -- you will encounter the problem you are having.  Microsoft came up with ADODB as a workaround for this problem.  When I have to interface between sql server and Access -- I use ADODB.  This has proven to be much more reliable and consistent between the small and large RDBMSs.  Here is some sample ADODB code for reading from and writing to a Sql Server from Access

    '--add a reference in Tools/References to Microsoft ActiveX Data Objects 2.x Library '--(2.5 or higher)

    Sub readFromSqlSvr2008()   
       Dim cmd As New ADODB.Command, RS As New ADODB.Recordset, RSdao As DAO.Recordset
       Dim i As Integer, j As Long
       On Error GoTo errMsg
    
       cmd.ActiveConnection = "Provider=SQLOLEDB; Data Source=yourSever;Database=testDB;Trusted_Connection=Yes"
       cmd.ActiveConnection.CursorLocation = adUseClient
       cmd.CommandTimeout = 600
       cmd.CommandType = adCmdText
       cmd.CommandText = "Select top 10 * from yourTable Order By yourKeyField"
       Set RS = cmd.Execute
       Set RSdao = CurrentDB.OpenRecordset("yourReceivingTable")
    
       Do While Not RS.EOF
          RSdao.AddNew
          For i = 0 to RS.Fields.Count - 1
             RSdao(i) = RS(i)
          Next
          RSdao.Upate
          RS.MoveNext
       Loop
       RSdao.Close
       cmd.ActiveConnection.Close
       Exit Sub   
    errMsg:
       MsgBox Err.Description
    End Sub

    Sub ExportToSqlServerFromAccess()
       Dim cmd As New ADODB.Command, RSdao As DAO.Recordset, i As Integer, j As Integer
       
       cmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=yourSvr;Database=yourDB;Trusted_Connection=Yes;"
       cmd.ActiveConnection.CursorLocation = adUseClient
       cmd.CommandType = adCmdText
    
       Set RSdao = CurrentDb.OpenRecordset("tbl3") '--local Access Table
    
       DoEvents
       Do While Not RSdao.EOF
          '--I start with RSdao(1) because RSdao(0) is an Identity column as is the Sql Server table
          cmd.CommandText = "Insert Into tbl3A Select '" & RSdao(1) & "', '" & RSdao(2) & "', '" & RSdao(3) & "', '" & RSdao(4) & "', '" & RSdao(5) & "', '" & RSdao(6) & "'"
          cmd.Execute
          RSdao.MoveNext
          i = i + 1
       Loop
    
       RSdao.Close
       cmd.ActiveConnection.Close
       Debug.Print "Done"
    
    End Sub
    Note:  once you start interfacing with an enterprise RDBMS like sql server at the production level  -- you have to start coding like a professional programmer -- (and with all due repsect to admins office workers cuz I was one years ago) not an amateur programmer. 


    Rich P


    Wednesday, February 5, 2014 4:21 PM
  • Hi All,

    Well it looks like I have finally got this problem under control.

    This is what I found for those out there who may come across this.

    The 3155 error can be caused by many things. Previously this problem occurred because I had a nvarchar(MAX) field in one of my larger tables. I solved this issue by changing the field to varchar(500). Many others have said that they have solved their particular problem because they added a timestamp field to their table.

    You can apply all "band aid" solutions to see you through in the short term but, I think in the end, Rich's response was pretty much correct in that you need to be more careful when programming in these environments as they are not forgiving.

    So I decided to look at the performance of the MS Access front end/SQL backend system a bit more carefully. After monitoring in SQL Activity Monitor I found many issues with performance. Primarily the main linked table causing the issue was being "locked" by others accessing the same table via the MS Access front end. When I identified all the MS Access Forms being used to retrieve data from this one table I saw some horrendously resource hungry queries that were causing the issues.

    After making some key changes, such as improving/re writing/removing queries and moving some of the more resource intensive queries to the backend (SQL) via Stored procs, the performance issues were resolved. In turn the 3155 errors ceased.

    So the moral of the story, avoid band aid fixes. Rather look at how your program is performing and act accordingly.

    Thanks all for your input.

    I hope this helps others.

    Chris

    Tuesday, February 11, 2014 10:45 PM

All replies

  • According to the old MSDN Thread

    Ms Access linking table with nvarchar(max)

    and the blog

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

    , the problem seems to related to the SQL Native Client + nvarchar(MAX).  A few things you can check and try:

    1.  Make sure you use the SQL Native Client 10 or later.

    2.  Make sure you have an SQL Server timestamp Field included in the Table.

    3.  If you don't need nvarchar, use varchar(MAX) instead (according to one of the articles above).

    4.  If you do need nvarchar, use nvarchar(255) which will be recognized as a Text Field (255) in Access.  nvarchar with limit > 255 will be interpreted as Memo Field in Access Linked Table and you end up with the same problem.

     


    Van Dinh

    Wednesday, February 5, 2014 8:46 AM
  • ODBC protocol is generally for big data -- sql server -- which uses "Fire Hose" bandwith.  Access uses (my term) garden hose bandwith (with all due respect for mini RDBMSs) because Access is basically a mini RDBMS (relational database management system) which is also file based.  Unless everything (front/back ends) is set up perfectly and conditions are ideal -- you will encounter the problem you are having.  Microsoft came up with ADODB as a workaround for this problem.  When I have to interface between sql server and Access -- I use ADODB.  This has proven to be much more reliable and consistent between the small and large RDBMSs.  Here is some sample ADODB code for reading from and writing to a Sql Server from Access

    '--add a reference in Tools/References to Microsoft ActiveX Data Objects 2.x Library '--(2.5 or higher)

    Sub readFromSqlSvr2008()   
       Dim cmd As New ADODB.Command, RS As New ADODB.Recordset, RSdao As DAO.Recordset
       Dim i As Integer, j As Long
       On Error GoTo errMsg
    
       cmd.ActiveConnection = "Provider=SQLOLEDB; Data Source=yourSever;Database=testDB;Trusted_Connection=Yes"
       cmd.ActiveConnection.CursorLocation = adUseClient
       cmd.CommandTimeout = 600
       cmd.CommandType = adCmdText
       cmd.CommandText = "Select top 10 * from yourTable Order By yourKeyField"
       Set RS = cmd.Execute
       Set RSdao = CurrentDB.OpenRecordset("yourReceivingTable")
    
       Do While Not RS.EOF
          RSdao.AddNew
          For i = 0 to RS.Fields.Count - 1
             RSdao(i) = RS(i)
          Next
          RSdao.Upate
          RS.MoveNext
       Loop
       RSdao.Close
       cmd.ActiveConnection.Close
       Exit Sub   
    errMsg:
       MsgBox Err.Description
    End Sub

    Sub ExportToSqlServerFromAccess()
       Dim cmd As New ADODB.Command, RSdao As DAO.Recordset, i As Integer, j As Integer
       
       cmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=yourSvr;Database=yourDB;Trusted_Connection=Yes;"
       cmd.ActiveConnection.CursorLocation = adUseClient
       cmd.CommandType = adCmdText
    
       Set RSdao = CurrentDb.OpenRecordset("tbl3") '--local Access Table
    
       DoEvents
       Do While Not RSdao.EOF
          '--I start with RSdao(1) because RSdao(0) is an Identity column as is the Sql Server table
          cmd.CommandText = "Insert Into tbl3A Select '" & RSdao(1) & "', '" & RSdao(2) & "', '" & RSdao(3) & "', '" & RSdao(4) & "', '" & RSdao(5) & "', '" & RSdao(6) & "'"
          cmd.Execute
          RSdao.MoveNext
          i = i + 1
       Loop
    
       RSdao.Close
       cmd.ActiveConnection.Close
       Debug.Print "Done"
    
    End Sub
    Note:  once you start interfacing with an enterprise RDBMS like sql server at the production level  -- you have to start coding like a professional programmer -- (and with all due repsect to admins office workers cuz I was one years ago) not an amateur programmer. 


    Rich P


    Wednesday, February 5, 2014 4:21 PM
  • Hi Van Dinh,

    They are all great suggestions as they have been the solution for many people. Unfortunately I've tried them all in the past but still have this problem.

    I'll have a look at Rich's response and see how I go with that.

    Thanks again.

    Cheers,

    Chris

    Thursday, February 6, 2014 1:16 AM
  • Hi All,

    Well it looks like I have finally got this problem under control.

    This is what I found for those out there who may come across this.

    The 3155 error can be caused by many things. Previously this problem occurred because I had a nvarchar(MAX) field in one of my larger tables. I solved this issue by changing the field to varchar(500). Many others have said that they have solved their particular problem because they added a timestamp field to their table.

    You can apply all "band aid" solutions to see you through in the short term but, I think in the end, Rich's response was pretty much correct in that you need to be more careful when programming in these environments as they are not forgiving.

    So I decided to look at the performance of the MS Access front end/SQL backend system a bit more carefully. After monitoring in SQL Activity Monitor I found many issues with performance. Primarily the main linked table causing the issue was being "locked" by others accessing the same table via the MS Access front end. When I identified all the MS Access Forms being used to retrieve data from this one table I saw some horrendously resource hungry queries that were causing the issues.

    After making some key changes, such as improving/re writing/removing queries and moving some of the more resource intensive queries to the backend (SQL) via Stored procs, the performance issues were resolved. In turn the 3155 errors ceased.

    So the moral of the story, avoid band aid fixes. Rather look at how your program is performing and act accordingly.

    Thanks all for your input.

    I hope this helps others.

    Chris

    Tuesday, February 11, 2014 10:45 PM