Solved 3157: ODBC -- update on a linked table '???' failed

[isladogs]

Yes, I remember how this started.
Whatever you originally intended to do, at some point you changed from discussing an UPDATE query/recordset to an INSERT (append).

I've consistently been suggesting you keep it simple (the KISS principle) rather than use an unnecessarily complex approach.
My suggestions apply equally to DELETE and CREATE TABLE operations though the latter should only rarely be needed.

Good luck with your project. Unless there is anything else specific I can offer, I will drop out now

 

[Guidon Mendoza]

can you use ADODB:

Private Sub BtnTest_Click()
    Dim cn As Object
    Dim rs As Object
    Dim strSQL As String
    Inicialization
   On Error GoTo ErrorHandler
    strSQL = "SELECT * FROM Workers WHERE Firstname='Daniel'"
    Set cn = CreateObject("adodb.connection")
    cn.ConnectionString = ConnectionStr
    cn.Open
    Set rs = CreateObject("adodb.recordset")
    With rs
        .CursorType = adOpenKeyset
        .CursorLocation = adUseClient
        .LockType = adLockOptimistic
        .Open strSQL, cn
        If Not .EOF Then
            .MoveFirst
            !Firstname = "testing"   ' instead of operational
            !LastUpdate = Now()
            .Update
        End If
    End With
exit_here:
    Set rs = Nothing
    Set cn = Nothing
    Exit Sub
ErrorHandler:
    MsgBox Err.Number & ": " & Err.Description
    Me.TxtStatus = Me.TxtStatus & vbCrLf & "Error: " & Err.Number
    Resume exit_here
End Sub

At the end, this is the solution I was looking for! Thanks a lot!!!

 

[glilly1]

Just for future reference, for anyone who may encounter this error. After several days of troubleshooting and also trying all the solutions here but still getting the error (error would come and go), I finally realized what the issue was. I was causing the issue myself.

In Oracle, if you update / alter any records in a table via SQL Workbench, Toad or any other Visual DB Interface, your changes will not take affect in the main DB until you 'commit' your changes. Turns out, I was making minor changes to some of my records and forgot to issue a 'commit;' statement. This causes all of the records I altered to be in a lock state for anyone but mem, including my ODBC driver and Linked table.

What was confusing me is that I was able to add new records just fine and I was able to update some records in my Access Application with the ODBC Linked table. But, some would cause the system to freeze and timeout with the Error 3157 ODBC Update Failed...

Once I realized what the cause was, I issued the 'commit' statement in Oracle Workbench... and no more errors.

I gotta remember to always 'commit' any changes from Workbench... probably the cause of most peoples issues when they get this error in these situations.