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

[Guidon Mendoza]

Actually, that's what I wanted to do, but it did not work. This is how I started the question:

"I have an MS Access 2016 platform linked to a MySQL database on a server somewhere. My platform is in 32-bit. I implemented Deletions, Creations and both work fine. But when I tried to Update records, it shows error 3157 ([it] shows what's in the title).

I have tried to change the amount of retrials, there are no rules violations as far as I can see, I extended the timeouts... nothing."

With your help I was able to update the registers. Since the speed was higher, I tried to implement it also on the Insert case. But it does not work for me yet...

 

[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.