Solved Problem in ADO, with .AddNew to a remote DB (1 Viewer)

[Guidon Mendoza]

Hi, everyone!

I have a project to share information for a work group over the Internet. To do so, I created a front-end platform in Access 2016 and remotely connected it to a MySQL DB. The connection seams to work fine, since I'm able to read and make updates using the connection.

I've been several days trying to add a new record to a table in the remote DB, but I get an error:

This says (after the number): the oppeation of several steps of OLE DB generated errors. Check the values of status of the OLE DB if it's possible. Nothing was done.

Function ReportAction(Tbl, Act As String) As Boolean
   ' ******************************
   ' This is always a Remote Report
   ' It works INSERTing a new record to the ActionsLog informing every event.
   ' ******************************
   Dim Rst As adodb.Recordset
   Dim strSQL As String
  
   Init
   On Error GoTo ErrorHandler
   strSQL = "select * from ActionsLog"
   Debug.Print strSQL
   Set Rst = OpenRst(strSQL, (RemoteConnectionStr), True)
   With Rst
      .AddNew
      !TblName = Tbl
      !Actions = Act
      !ActionDate = Now()
      .Update
      .Close
   End With
   Set Rst = Nothing
   ReportAction = True
   Exit Function
ErrorHandler:
   Select Case Err.Number
      Case Else
         MsgBox Err.Number & ": " & Err.Description, vbCritical, "Error in mdlBackup.TableExists()"
   End Select
   ReportAction = False
End Function

The function OpenRst() returns an ADODB.Recordset. It internally generates the connection. As I said, I check and the connection works. BUT, before the AddNew statement, I wrote a .RecordCount, which returns -1 every time.

This is the code for creating the Recordset:

Function OpenRst(StrSelect As String, ConnStr As String, Remote As Boolean) As Object ', CursorDef As Integer
   ' Creates also the connection
   Dim strProvider As String
   Dim LocalRst As Recordset
  
   If Remote Then
      Set Conex = OpenConnection(ConnStr)
      If Conex.State = 0 Then
         Debug.Print "Error! Connection Failed!"
         Set Conex = Nothing
         Set OpenRst = Nothing
         Exit Function
      End If
      Debug.Print "Connection Success!"
      Set OpenRst = CreateObject("adodb.recordset")
      With OpenRst
         .LockType = adLockOptimistic
         .CursorLocation = adUseServer
         .CursorType = adOpenKeyset
         .ActiveConnection = Conex
         .Open (StrSelect)
      End With
   Else
      Set OpenRst = CurrentDb.OpenRecordset(StrSelect, dbDenyRead)
   End If
   Debug.Print "Recordset stablished!"
End Function

Conex is a Global variable.

The code fails on the .Update statement (on the first piece of code).

Can someone shed light on this for me, please? I'll really appreciate it!

Thanks in advance.

 

[Minty]

The fact that you are getting

I wrote a .RecordCount, which returns -1 every time.

Indicates a potential issue, and possibly a clue to your problem.
With an ADODB recordset you shouldn't need to issue an rs.MoveLast to obtain an accurate record count, but I would add one and see what you get.

I would also be tempted to not use your OpenRst function whilst debugging, just open the explicit recordset until you get things working, to remove all possible causes of the issue.

 

[Guidon Mendoza]

I just found the answer on a MySQL document. Here the quote:

Multiple-Step Operation Error​

Using the AppendChunk() or GetChunk() ADO methods, the Multiple-step operation generated errors. Check each status value error is returned.

The GetChunk() and AppendChunk() methods from ADO do not work as expected when the cursor location is specified as adUseServer. On the other hand, you can overcome this error by using adUseClient.

 

[arnelgp]

use adUseClient for the CursorLocation?

adUseServer is only used for Stored-proc.

 

[Guidon Mendoza]

Thanks, Minty! That was fast!

 

[Guidon Mendoza]

And thanks to you too, arnelgp!