Solved Access Pass-Through MariaDB (1 Viewer)

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:31
Joined
May 7, 2009
Messages
19,169
Did you try it?
i did try it.
you code is using
CurrentDb.Execute "INSERT INTO YourTable (Fld1, Fld2, Fld3) VALUES "
currentdb does not permit multiple values.
wha the OP has is a pass-through.

maybe you are referring to ADO?

also the reference you should shows that the packet is in MB (or GB).
1 MB = 1,048,576 bytes.

you are only getting the Length of the string?
suppose you are only inserting a single character "A", the length is 1?
when in fact a single character is 2 bytes long (for english characters).
what if that single character is in chinese letter (double-byte, 8 bytes of data packet).

and how sure are you on your formula that Access will send it "As-Is", word per word
on your "SQL statement?". do you know the internal working of Access?
Access is interpretative so the code for an "insert command", will be translated
to a code that Marian will understand. so we don' exactly know how much packet is this
and will Access send it in Bulk (single send) or by packet rule of Marian (chunk by chunk).

We don't know whether Access will insert an additional code for "insert" or "update", so that
the receiving end will know what is the requested action to take.

remember that when packet is more than what is defined in marian, it will send an error
message and cut the connection. in which case this does not happen to the OP, instead
experienced long delay of query action.
 

cheekybuddha

AWF VIP
Local time
Today, 12:31
Joined
Jul 21, 2014
Messages
2,237
currentdb does not permit multiple values.

Yeah, my mistake! :eek: I meant pass-through QueryDef.Execute, like in my example to Adelina_RO.

Apologies, I thought you were complaining about the syntax of the SQL and made the caffeine-deficient typo in trying to explain the example.

Definitely do not send that query by CurrentDb.Execute, only by pass-through!

And yeah, it should probably use LenB() instead of Len(). I did warn that tweaks would likely be required! ;)
 

Adelina_RO

Member
Local time
Today, 14:31
Joined
Apr 9, 2021
Messages
42
Code:
Public Function ADO_InsertSQL(cColumns As String, cIndex As String, cLocTable As String, cExtTable As String) As Boolean
On Error GoTo Eroare

Dim Rc As DAO.Recordset
Dim I As Integer
Dim vRow As String
Dim eSql As String
Dim vSql As String
Dim vValues As String
Dim MaxAllowedPacket As Long
Dim lRecordsAffected As Long

'Initial Values
ADO_InsertSQL = False
vRow = " ("
vValues = ""
eSql = "INSERT INTO " & cExtTable & " ( " & cColumns & " ) VALUES "
vSql = "SELECT " & cColumns & " FROM " & cLocTable & " ORDER BY [" & cIndex & "]"

'function to check if the connection to the server is active and if not, build it again
SQL_SVN_OC

'flush the table prior to adding data
SQL_SVN.Execute "FLUSH TABLE `" & cExtTable & "`", False

'get the MAX_ALLOWED_PACKET value from MariaDB server
MaxAllowedPacket = SQL_SVN.Execute("SHOW VARIABLES LIKE 'MAX_ALLOWED_PACKET'").Fields(1)
  
Set Rc = CurrentDb.OpenRecordset(vSql)
If Not POP(Rc) Then 'if the local table has no data, exit with message
    MsgBox "Tabelul selectat nu contine date!", vbOKOnly + vbCritical, Application.Name
    GoTo IESIRE
End If

Do While Not Rc.EOF
    'buid the string containing the data
    For I = 0 To Rc.Fields.Count - 1
        Select Case Rc.Fields(I).Type
            Case 1 'boolean
                vRow = vRow & Nz(Val(Rc.Fields(I).Value), "NULL") & ","
            Case 3, 4, 7 'integer, double
                vRow = vRow & Nz(Rc.Fields(I).Value, "NULL") & ","
            Case 8 'date
                vRow = vRow & IIf(Nz(Rc.Fields(I).Value, "") = "", "NULL,", "'" & Format(Rc.Fields(I).Value, "yyyy-mm-dd") & "',")
            Case 10, 12 'string, memo
                vRow = vRow & "'" & Replace(Nz(Rc.Fields(I).Value, ""), Chr(39), "") & "',"
            Case Else 'case a different type i haven't coded yet. to lazy to check for everything
                Debug.Print Rc.Fields(I).Name
                Stop
        End Select
    Next
  
    'check if the size in bytes is greater than server's max_allowed_packet variable
    If LenB(vValues & vRow) <= MaxAllowedPacket Then
        'if not, continue to add text to the main string variable
        vValues = vValues & Mid(vRow, 1, Len(vRow) - 1) & ")"
        vRow = ",("
    Else
        'if so, write to the server the current string, then start building a new one with the rest of the values
        SQL_SVN.Execute eSql & vValues, lRecordsAffected
        MsgBox "Au fost inserate " & lRecordsAffected & " inregistrari in [" & cExtTable & "]!", vbOKOnly + vbInformation
        vValues = Mid(vRow, 2, Len(vRow) - 2) & ")"
        vRow = ",("
    End If
      
    Rc.MoveNext
Loop

'if there are leftovers, write the to the server as well
If LenB(vValues) <> 0 Then
    SQL_SVN.Execute eSql & vValues, lRecordsAffected
    MsgBox "Au fost inserate " & lRecordsAffected & " inregistrari in [" & cExtTable & "]!", vbOKOnly + vbInformation
End If

'everything went OK so update the function's value
ADO_InsertSQL = True

'cleanup
IESIRE:
Set Rc = Nothing

Exit Function

Eroare:
    Debug.Print Err.Description
    ADO_InsertSQL = False 'on error the function returns false
    Resume IESIRE
End Function

this is what i ended up using
thank you everyone
and... i haven't posted any more passwords :D
 

Users who are viewing this thread

Top Bottom