Solved 3157: ODBC -- update on a linked table '???' failed (1 Viewer)

Guidon Mendoza

New member
Local time
Today, 13:48
Joined
May 3, 2021
Messages
19
Hi, Masters!

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

I have not been able to find an explanation or a solution.

Can somebody help?

Thanks in advance

G
 

isladogs

MVP / VIP
Local time
Today, 11:48
Joined
Jan 14, 2017
Messages
18,186
Can you post the SQL used for the update query. Does it work if you make a local copy of the MySQL table?
 

Guidon Mendoza

New member
Local time
Today, 13:48
Joined
May 3, 2021
Messages
19
Thanks for your answer!
I've tried with DAO recordsets and with SQL statements. I created a Test Button, to execute every option:
  • The ConnectionStr I already tested (and since it works with delete and create, I don't see a problem it may have.
  • Here I have
    • a docmd.runsql for a straight SQL command. It's commented
    • a set rst instruction for selecting a recordset
    • the .update instruction is where it produces the 3157 error.
Code:
Private Sub BtnTest_Click()
   Dim DB As dao.Database
   Dim rst As dao.Recordset
   Dim strSQL As String
   Inicialization
   On Error GoTo ErrorHandler
   Set WrkSpc = DBEngine(0)
   Set DB = WrkSpc.OpenDatabase("", dbDriverComplete, False, ConnectionStr) 
   strSQL = "SELECT * FROM Workers WHERE Firstname='Daniel'"
'   DoCmd.RunSQL (strSQL) 'this is commented
   Set rst = DB.OpenRecordset(strSQL, TablePermit)
   With rst
      .MoveLast
      ' .FindFirst "Firstname = 'Daniel'"
      .Edit
      !Firstname = "testing"   ' instead of operational
      !LastUpdate = Now()
      .Update
   End With
   GoTo EndSub
ErrorHandler:
   Select Case Err.Number
      Case 3376, 3021
         Resume Next
      Case Else
         MsgBox Err.Number & ": " & Err.Description, vbCritical, "Error in mdlBackup.TableExists()"
   End Select
   Me.TxtStatus = Me.TxtStatus & vbCrLf & "Error: " & Err.Number
EndSub:
   rst.Close
   Set rst = Nothing
   DB.Close
   Set DB = Nothing
   WrkSpc.Close
   Set WrkSpc = Nothing
End Sub

It gets to the .edit statement, and beyond. I've checked the values and it does recognize the rst structure in the remote DB.

Any hints?

Thanks again!

G
 

Guidon Mendoza

New member
Local time
Today, 13:48
Joined
May 3, 2021
Messages
19
Actually, if I try to do the update with an SQL command, it worked this time!

I still would love to do it in Recerdsets, though...

And here is the code that works:
Code:
Private Sub BtnTest_Click()
   Dim DB As dao.Database
   Dim rst As dao.Recordset
   Dim strSQL As String
   Inicialization
   On Error GoTo ErrorHandler
   Me.TxtStatus = "openrecordset con Permit = " & TablePermit
   Set WrkSpc = DBEngine(0)
   Set DB = WrkSpc.OpenDatabase("", dbDriverComplete, False, ConnectionStr)
  
   strSQL = "UPDATE Workers " _
            & "SET Firstname = 'testing' " _
            & "WHERE Firstname = 'Daniel';"
   DB.Execute strSQL, dbSQLPassThrough
   GoTo EndSub
ErrorHandler:
   Select Case Err.Number
      Case 3376, 3021
         Resume Next
      Case Else
         MsgBox Err.Number & ": " & Err.Description, vbCritical, "Error in mdlBackup.TableExists()"
   End Select
EndSub:
'   rst.Close
'   Set rst = Nothing
   DB.Close
   Set DB = Nothing
   WrkSpc.Close
   Set WrkSpc = Nothing
End Sub
 

bastanu

AWF VIP
Local time
Today, 04:48
Joined
Apr 13, 2010
Messages
1,401
It probably worked because you passed it to the server with the pass-through option. Can you show us the table in design view? Does it show the primary key indicator on the PK\unique field? What happens if you try to manually edit the table itself?
Cheers,
 

isladogs

MVP / VIP
Local time
Today, 11:48
Joined
Jan 14, 2017
Messages
18,186
Recordsets are far slower than update queries (or sql statements) especially if there are many records to update.
Whilst recordsets have their uses, there is no reason to use a recordset in this case.

That is also true for the append and delete items that were working for you.

If the tables are linked to your FE, there should also be no reason to use a passthrough query ....unless it is far faster in practice.
Try replacing dbSQLPassThrougb with dbSeeChanges

Also I suggest replacing DBEngine(0)(0) with CurrentDb. Its usually faster
 
Last edited:

Guidon Mendoza

New member
Local time
Today, 13:48
Joined
May 3, 2021
Messages
19
Thanks a lot for the advise! I'll implement it as far as I can.

The reason I wanted to use Recordsets is that the logic for constructing the SQL statements is complex in several parts of the program (and results in not very elegant code...), and the code is hard to abstract. On the other side... it works!

Thanks again!
 

Guidon Mendoza

New member
Local time
Today, 13:48
Joined
May 3, 2021
Messages
19
It probably worked because you passed it to the server with the pass-through option. Can you show us the table in design view? Does it show the primary key indicator on the PK\unique field? What happens if you try to manually edit the table itself?
Cheers,
I did alter the table manually and it works fine. I modified the tables for everyone to have a PK/Unique field. Even then, it did not work...
 

isladogs

MVP / VIP
Local time
Today, 11:48
Joined
Jan 14, 2017
Messages
18,186
Recordsets modify data one record at a time. This is sometimes referred to as RBAR = row by agonising row. So they are slow
Queries/SQL statements effectively modify all records at the same time. This results in a far faster outcome.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:48
Joined
Feb 19, 2002
Messages
42,981
I did alter the table manually and it works fine. I modified the tables for everyone to have a PK/Unique field. Even then, it did not work...
Did you refresh the link? Access won't automatically see design changes to the BE database unless the BE is Jet/ACE.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:48
Joined
May 7, 2009
Messages
19,175
can you use ADODB:
Code:
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
 

Guidon Mendoza

New member
Local time
Today, 13:48
Joined
May 3, 2021
Messages
19
Thanks to all of you.

Isladog, is that correct for every recordset type? And if so, then the database is locked through out the whole process?

Pat, I did refresh it.

Arnelgp, I'll try this option also. Thanks.

Thanks again.

G
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:48
Joined
May 7, 2009
Messages
19,175
you are only editing 1 record?
where is the RBAR there?
 

isladogs

MVP / VIP
Local time
Today, 11:48
Joined
Jan 14, 2017
Messages
18,186
@Guidon Mendoza
I got an email notification of a new reply from you about an hour ago.
Have you deleted that post again?
 

Guidon Mendoza

New member
Local time
Today, 13:48
Joined
May 3, 2021
Messages
19
No, I'm moving forward, using your help. Actually I was able to create a UPDATE query and make it work with an abstract object. Thanks!!

Now I'm stuck with an Error Code 3065. The code I'm using is this:

Code:
Function AddRec(Tbl As String, NewRec As Object, KeyId As String) As Long
   Dim DB As DAO.Database
   Dim rst As DAO.Recordset
   Dim f As DAO.Field
   Dim msgStr As String
   Dim RecID As Long
   Dim strSQL, strFieldsList, strValues, strSelectWhere, newEmail As String
   Dim idx As Integer
   On Error GoTo ErrorHandler
  
   Inicialization ' ONLY during tests
  
   newEmail = "Test1000@gmail.com"
   strSQL = "INSERT INTO " & Tbl & " "
   strFieldsList = "("
   strValues = "VALUES ("
   Set WrkSpc = DBEngine(0)
   Set rst = CurrentDb.OpenRecordset(Tbl, TablePermit)                        ' replica to the local DB
   For Each f In rst.Fields
      If ((f.Name) <> KeyId) Then
         strFieldsList = strFieldsList & f.Name    ' characters to delimit a string/char
         Select Case f.Type
            Case dbText, dbChar, dbMemo
               If f.Name = "Email" Then
                  strValues = strValues & "'" & newEmail & "'"
               Else
                  strValues = strValues & "'" & f & "'"
               End If
            Case dbTime, dbTimeStamp, dbDate
               strValues = strValues & "'" & Format(f, "yyyy/mm/dd  hh:mm:ss") & "'"
            Case Else
               strValues = strValues & f
         End Select
         strFieldsList = strFieldsList & ", "
         strValues = strValues & ", "
      End If
   Next f
   strFieldsList = Left(strFieldsList, Len(strFieldsList) - 2) ' takes away the last 2 chars (", ")
   strValues = Left(strValues, Len(strValues) - 2) ' takes away the last 2 chars (", ")
   strFieldsList = strFieldsList & ") "
   strValues = strValues & ")"
   strSQL = strSQL & strFieldsList & vbCrLf & strValues & ";"
   Set DB = WrkSpc.OpenDatabase("", dbDriverComplete, False, ConnectionStr)   ' connection to the remote DB
   DB.Execute strSQL, dbSeeChanges
   Set rst = DB.OpenRecordset(Tbl, TablePermit)
   rst.MoveLast
   AddRec = rst!Fields(KeyId)
   msgStr = "Success in creating record # " & AddRec
   Debug.Print "AddRec: ", ReportChange(Tbl, msgStr, False)
ErrorHandler:
   Select Case Err.Number
      Case 0
         Set f = Nothing
         rst.Close
         Set rst = Nothing
         DB.Close
         Set DB = Nothing
         WrkSpc.Close
         Set WrkSpc = Nothing
         Exit Function
      Case 3376, 3021   ' error 3021: no records to move to last.
         Resume Next
      Case Else
         MsgBox Err.Number & ": " & Err.Description & vbCrLf & " -> " & Err.Source, vbCritical, "Error in mdlBackup.TableExists()"
   End Select
   AddRec = 0
End Function

I checked the SQL statement using MySQL Workbench and it works smoothly. Also in Access itself. But when I call it from the VBA, it show this 3065 error on the line of DB.Execute...

Any idea why?

Thanks
 

isladogs

MVP / VIP
Local time
Today, 11:48
Joined
Jan 14, 2017
Messages
18,186
Generic functions like this can be useful but for simple action queries they are usually unnecessary.
I suggest you strip out anything not needed.
For example, no need to open the remote dB if you are updating linked tables.
In addition, you can remove all the recordset code as it seems you are no longer using it

I think you can reduce all of this to just the following lines:

Code:
Dim strSQL As String
strSQL = "INSERT INTO..... VALUES ..."
Debug.Print strSQL 'optional but very useful when problems occur
CurrentDB.Execute strSQL, dbSeeChanges,, dbFailOnError

Error 3065=Cannot execute a SELECT query
Do you still get error 3065 when you just use that simplified code? If so, please post the Debug.Print output

For info, I've found SQL /queries anything up to 250 times faster than recordsets especially if changes are being done to indexed fields. The time difference is most obvious when a large number of records are updated.
 

Guidon Mendoza

New member
Local time
Today, 13:48
Joined
May 3, 2021
Messages
19
Actually, I'm not connecting to the remote DB until I get the Insert done, but it's never done, because of the 3065 error.

As background, I need the Recordset in the beginning of the code to create the INSERT query: I receive the Object, and create the strSQL to execute.

Code:
   strSQL = "USE moiil;" & vbCrLf & "INSERT INTO " & Tbl & " "
   strFieldsList = "("
   strValues = "VALUES ("
   Set WrkSpc = DBEngine(0)
   Set rst = CurrentDb.OpenRecordset(Tbl, TablePermit)                        ' replica to the local DB
   For Each f In rst.Fields
      If ((f.Name) <> KeyId) Then
         strFieldsList = strFieldsList & f.Name    ' characters to delimit a string/char
         Select Case f.Type
            Case dbText, dbChar, dbMemo
               If f.Name = "Email" Then
                  strValues = strValues & "'" & NewEmail & "'"
               Else
                  strValues = strValues & "'" & f & "'"
               End If
            Case dbTime, dbTimeStamp, dbDate
               strValues = strValues & "'" & Format(f, "yyyy/mm/dd  hh:mm:ss") & "'"
            Case Else
               strValues = strValues & f
         End Select
         strFieldsList = strFieldsList & ", "
         strValues = strValues & ", "
      End If
   Next f
   strFieldsList = Left(strFieldsList, Len(strFieldsList) - 2) ' takes away the last 2 chars (", ")
   strValues = Left(strValues, Len(strValues) - 2) ' takes away the last 2 chars (", ")
   strFieldsList = strFieldsList & ") "
   strValues = strValues & ")"
   strSQL = strSQL & strFieldsList & vbCrLf & strValues & ";"
   DB.Execute strSQL, dbSeeChanges

Once I would have it executed, I open anew the rst, now pointing to the remote DB to get the ID of the new record

Code:
'   Set rst = DB.OpenRecordset(Tbl, TablePermit)
'   rst.MoveLast
'   AddRec = rst!Fields(KeyId)
'   msgStr = "Success in creating record # " & AddRec

The SQL code I get is

Code:
INSERT INTO Workers (Firstname, Lastname, Email, Country, City, Address, POB, Phone, DNI, LinkedIn, Rating, ReferencedBy, CV, Notes, interviewed, InterviewFile, CreationDate, LastUpdate)
VALUES ('Test1', 'Test1', 'Test1004', 'Israel', 'Givat', '', '', '', '', '', 0, '', '', '<div>Interviewed on May 2, 2021</div>', True, 'Interview Marc Topiol.docx', '2021/05/02  11:13:03', '2021/05/02  14:15:19');

And when I run this SQL, works just fine.

But when I run it on the code, I get this error code 3065.
 

isladogs

MVP / VIP
Local time
Today, 11:48
Joined
Jan 14, 2017
Messages
18,186
Why not link the table and use Access features to simplify your work and reduce development time. That's how its designed to be used
I'm repeating myself but I really don't see the point of using such a convoluted approach ... especially being as you are getting errors.

Just use a standard INSERT query and assuming its an auto number field, use a DMax function to get the KeyID.

Or use a form bound to the linked table to add new records.
Another thing. I suggest you avoid using zero length strings when there are no values to insert. Leave those fields null rather than use ZLS.

Finally the real benefit of action queries/SQL is when these are used to append or update multiple records at once. Can you factor that into your code?
 

Users who are viewing this thread

Top Bottom