Solved Access Pass-Through MariaDB

[Adelina_RO]

So, here it goes: i've been using Access since the 97 version (yeah, I'm old...), but only recently i had the need to use a MariaDB server as the backend and I'm stomped...
I've been trying to make a pass-through query to append some rows in the said backend to noavail...
This is the situation:
I have a [tmpClient] local table in Access frontend which contains about 2K rows and i want to append the [Client] table which is located on the server but is linked in the frontend.
I can use the "Execute" statement of currentdb, but it takes too long (~1 min), so... no.
So i thought of using pass-t query to let the server do all the work, but when i use the query "INSERT INTO Client (IdClient, CNP, Nume, NumeAsociat) SELECT tmpClient.IdClient, tmpClient.CNP, tmpClient.Nume, tmpClient.NumeAsociat FROM tmpClienti" it doesn't work, because it expects the [tmpClient] to be on the server as well.
This brings me to the question at hand:
Can i use a pass-t query to append a MariaDB table with data extracted from a local (access) table?
Thank you in advance!

 

[cheekybuddha]

This should be possible.

What is the size of your MAX_ALLOWED_PACKET variable? You should be able to find out by running the following query against the server:

SHOW VARIABLES LIKE 'MAX_ALLOWED_PACKET';

 

[Adelina_RO]

Hey. Thanks for the qucik reply. This is what the server says, but i don't think that this is the problem.
"max_allowed_packet 1048576"
As i said, the [tmpClient] table is local and [Clienti] is sever. I guess the question would be: can I in Access SQL Pass_through insert data from local table into a server table? if so, what is the correct syntax?

 

[arnelgp]

i think you cannot run a pass-through query to update the backend table (linked).
you can also use VBA to run the insert query":


Currentdb.Execute "INSERT INTO Client (IdClient, CNP, Nume, NumeAsociat) SELECT tmpClient.IdClient, tmpClient.CNP, tmpClient.Nume, tmpClient.NumeAsociat FROM tmpClienti", dbFailOnError

 

[cheekybuddha]

No, that wasn't the problem, but it can affect how much you can send to the server in a a single statement.

You will always have performance issues when you try and link a local table with a remote table - so Pass-through is the way to go.

Here's some basic code to get you started, but since I don't have all your details it will likely need tweaking:

  Const ODBC_CONNECTION_STRING As String = "Your Connection String Here"
  Const MAX_ALLOWED_PACKET As Long = 1048576
  Const SQL_CLIENTI As String = "SELECT IdClient, CNP, Nume, NumeAsociat FROM tmpClienti;"
  Const SQL_INSERT As String = "INSERT INTO Client (IdClient, CNP, Nume, NumeAsociat) VALUES "

  Dim db As DAO.Database, qdPT As DAO.QueryDef, strSQL As String, strVals As String lLen AS Long, iFld As Integer
  Set db = CurrentDB
  Set qdPT = db.CreateQueryDef(vbNullString)
  qdPT.Connection = ODBC_CONNECTION_STRING
  With db.OpenRecordset(SQL_CLIENTI)
    Do While Not .EOF
      strSQL = "(" & .Fields(0), & _
                  ", '" & Replace(.Fields(1), "'", "''") & _
                  "', '" & Replace(.Fields(2), "'", "''") & _
                  "', '" & Replace(.Fields(3), "'", "''") & _
               ")"
      If Len(SQL_INSERT & strVals & "," & strSQL) <= MAX_ALLOWED_PACKET Then
        strVals = strVals & IIf(Len(strVals), ",", vbNullString) & strSQL
      Else
        qdPT.Execute SQL_INSERT & strVals, dbFailOnError
        strVals = strSQL
      End If
      .MoveNext
    Loop
    .Close
  End With
  ' Send the last batch
  If Len(strVals) Then
    qdPT.Execute SQL_INSERT & strVals, dbFailOnError
  End If
  Set qdPT = Nothing
  Set db = Nothing

(Nb total aircode!)

 

[arnelgp]

Here's some basic code t

ha, you mean this is much faster (each record is inspected) then insert that single record then loop.
than a single sql insert?

then if the "sql" string is greater than the packet you're talking, will be Excluded?
i hope you can quote the source of this info.

 

[cheekybuddha]

ha, you mean this is much faster (each record is inspected) then insert that single record then loop.
than a single sql insert?

No, in MariaDB you can batch up multiple INSERTS at once (up to the level of the MAX_ALLOWED_PACKET, which is why I wanted to check its value).

If the remote table is large, it has to be read into Access for you to perform the INSERT from the local table using linked tables - hence the huge slowdown.

 

[cheekybuddha]

i hope you can quote the source of this info.

MySQL :: MySQL 5.7 Reference Manual :: B.3.2.8 Packet Too Large

dev.mysql.com

 

[Adelina_RO]

Wow It's nice having an account here... Should've done this 10 years ago )))

Public Sub CreateSQLValues(cColumns As String, cIndex As String, cLocTable As String, cExtTable As String, nRows As Integer, Optional IdMin As Long = 0, Optional IdMax As Long = 0)
Dim Rc As DAO.Recordset
Dim Qc As DAO.QueryDef
Dim i As Integer
Dim vRow As String
Dim vSql As String
Dim vValues As String

If IdMax = 0 Then IdMax = nRows

vSql = "SELECT " & cColumns & " FROM " & cLocTable & " WHERE [" & cIndex & "] BETWEEN " & IdMin & " AND " & IdMax & " ORDER BY [" & cIndex & "]"
Set Rc = CurrentDb.OpenRecordset(vSql)
POP Rc

vRow = " ( "
vValues = ""

Do While Not Rc.EOF
    For i = 0 To Rc.Fields.Count - 1
        Select Case Rc.Fields(i).Type
            Case 3, 4 'integer, boolean
                vRow = vRow & Nz(Rc.Fields(i).Value, 0) & ", "
            Case 10 'string
                vRow = vRow & "'" & Rc.Fields(i).Value & "', "
        End Select
    Next
    vValues = vValues & Mid(vRow, 1, Len(vRow) - 2)
    vRow = "), ( "
    Rc.MoveNext
Loop

vValues = vValues & ")"

If IdMin = 0 Then
    Set Qc = CurrentDb.QueryDefs("Clienti_Flush") 'i used this because, after a few inserts i would get the "not enough resources" message
        Qc.Execute
End If

Set Qc = CurrentDb.QueryDefs("Clienti_Insert")
    vSql = "INSERT INTO " & cExtTable & " ( " & cColumns & " ) VALUES " & vValues
    Qc.Connect = "Connection String removed by Admin"
    Qc.sql = vSql
    Qc.ReturnsRecords = False
    Qc.Execute

If DMax(cIndex, cLocTable) > IdMax Then
    IdMin = IdMax + 1
    IdMax = IdMax + nRows
    CreateSQLValues cColumns, cIndex, cLocTable, cExtTable, 1000, IdMin, IdMax
End If

Set Qc = Nothing
Set Rc = Nothing

End Sub

P.S. 1: I had no ideea about the MAX_ALLOWED_PACKET, so now it's clear i have to use this, instead of number of records
P.S. 2: it's way faster then the normal currentdb.execute (10-15 seconds v 0.3 seconds)
P.S. 3: i want to make this as reusable as possible
P.S. 4: it's not complete. i haven't taken the date and other column types into account, no error trapping, no nothing...
thank you so much.

 

[cheekybuddha]

Are you sure you are not getting Resources error because you are calling this sub recursively?

I think you are missing my point about the multiple INSERTS.

Instead of executing an INSERT statement for each recor in the local table, MariaBD/MySQL allow the ability to make multiple INSERT's at once.

So instead of:

CurrentDb.Execute "INSERT INTO YourTable (Fld1, Fld2, Fld3) VALUES (Rec1_Val1, Rec1_Val2, Rec1_Val3);", dbFailOnError
CurrentDb.Execute "INSERT INTO YourTable (Fld1, Fld2, Fld3) VALUES (Rec2_Val1, Rec2_Val2, Rec2_Val3);", dbFailOnError
CurrentDb.Execute "INSERT INTO YourTable (Fld1, Fld2, Fld3) VALUES (Rec3_Val1, Rec3_Val2, Rec3_Val3);", dbFailOnError

You can do:

CurrentDb.Execute "INSERT INTO YourTable (Fld1, Fld2, Fld3) VALUES " & _
                    "(Rec1_Val1, Rec1_Val2, Rec1_Val3), " & _
                    "(Rec2_Val1, Rec2_Val2, Rec2_Val3)," & _
                    "(Rec3_Val1, Rec3_Val2, Rec3_Val3);", _
    dbFailOnError

 

[arnelgp]

please remove your connection string.
your service might be compromised.

 

[arnelgp]

CurrentDb.Execute "INSERT INTO YourTable (Fld1, Fld2, Fld3) VALUES " & _ "(Rec1_Val1, Rec1_Val2, Rec1_Val3), " & _ "(Rec2_Val1, Rec2_Val2, Rec2_Val3)," & _ "(Rec3_Val1, Rec3_Val2, Rec3_Val3);", _ dbFailOnError

no, this won't work.
you need to try it first.

 

[arnelgp]

please remove your Connection string from your Code.
your server is now vulnerable to attack!

 

[Uncle Gizmo]

please remove your Connection string from your Code.
your server is now vulnerable to attack!

Thanks Arnel, I have removed it...

 

[Adelina_RO]

please remove your connection string.
your service might be compromised.

thank you. i'm a noob. fortunately, the server is internal with no access to the web

 

[Adelina_RO]

I got this working:

Public Sub CreateSQLValues(cColumns As String, cIndex As String, cLocTable As String, cExtTable As String)
On Error GoTo Eroare

Const MaxRows As Integer = 500

Dim Rc As DAO.Recordset
Dim Qc As DAO.QueryDef
Dim i As Integer, j As Integer
Dim vRow As String
Dim eSql As String: eSql = "INSERT INTO " & cExtTable & " ( " & cColumns & " ) VALUES "
Dim vSql As String
Dim vValues As String

Set Qc = CurrentDb.QueryDefs(cExtTable & "_Flush")
    Qc.Execute
    
Set Qc = CurrentDb.CreateQueryDef(vbNullString)
    Qc.Connect = "ODBC;DRIVER={MySQL ODBC 8.0 Unicode Driver};UID=Removed by moderator"
    Qc.ReturnsRecords = False

vSql = "SELECT " & cColumns & " FROM " & cLocTable & " ORDER BY [" & cIndex & "]"
Set Rc = CurrentDb.OpenRecordset(vSql)
POP Rc

vRow = " ("
vValues = ""

Do While Not Rc.EOF
    j = j + 1
    
    For i = 0 To Rc.Fields.Count - 1
        Select Case Rc.Fields(i).Type
            Case 3, 4 'integer, boolean
                vRow = vRow & Nz(Rc.Fields(i).Value, 0) & ","
            Case 10 'string
                vRow = vRow & "'" & Rc.Fields(i).Value & "',"
            Case Else
                Stop
        End Select
    Next
        
    If j < MaxRows Then
        vValues = vValues & Mid(vRow, 1, Len(vRow) - 1) & ")"
        vRow = ",("
    Else
        Qc.sql = eSql & vValues
        Qc.Execute dbFailOnError
        vValues = Mid(vRow, 2, Len(vRow) - 2) & ")"
        vRow = ",("
        j = 1
    End If
    
    Rc.MoveNext
Loop

If Len(vValues) Then
    Qc.sql = eSql & vValues
    Qc.Execute
End If

Iesire:
Set Qc = Nothing
Set Rc = Nothing

Exit Sub

Eroare:
    Debug.Print Err.Description
    Resume Iesire
End Sub

Thank you everyone

P.S. I couldn't use MAX_ALLOWED_PACKET since the lenght of the string is way smaller than the constant so in the end i used number of records

 

[cheekybuddha]

you need to try it first.

ummm...

Did you try it?

 

[cheekybuddha]

MySQL >  localhost   5.7.33-log 
 SQL  USE testbed;
Default schema set to `testbed`.
Fetching table and column names from `testbed` for auto-completion... Press ^C to stop.
MySQL >  localhost   5.7.33-log   testbed 
 SQL > CREATE TABLE t1 (
     >   ID INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
     >   fld1 VARCHAR(20) NOT NULL,
     >   fld2 VARCHAR(20) NOT NULL,
     >   fld3 VARCHAR(20) NOT NULL
     >  ) ENGINE=INNODB
     > ;
Query OK, 0 rows affected (0.1098 sec)
MySQL >  localhost   5.7.33-log   testbed 
 SQL > SELECT * FROM t1;
Empty set (0.0494 sec)
MySQL >  localhost   5.7.33-log   testbed 
 SQL > INSERT INTO t1  (fld1, fld2, fld3)
     > VALUES
     >   ('rec1_val1', 'rec1_val2', 'rec1_val3'),
     >   ('rec2_val1', 'rec2_val2', 'rec2_val3'),
     >   ('rec3_val1', 'rec3_val2', 'rec3_val3')
     > ;
Query OK, 3 rows affected (0.0466 sec)

Records: 3  Duplicates: 0  Warnings: 0
MySQL >  localhost   5.7.33-log   testbed 
 SQL > SELECT * FROM t1;
+----+-----------+-----------+-----------+
| ID | fld1      | fld2      | fld3      |
+----+-----------+-----------+-----------+
|  1 | rec1_val1 | rec1_val2 | rec1_val3 |
|  3 | rec2_val1 | rec2_val2 | rec2_val3 |
|  5 | rec3_val1 | rec3_val2 | rec3_val3 |
+----+-----------+-----------+-----------+
3 rows in set (0.0356 sec)

 

[Uncle Gizmo]

That's Pretty!

 

[NauticalGent]

Wow It's nice having an account here... Should've done this 10 years ago ))

Absotively, posilutly!