mysql backend query syntax error

[roberto21]

I have a working app using access frontend and MS SqlServer backend (sqlexpress). My intention was to replace sqlexpress with mysql backend, so I migrated the tables using mysql workbench and relinked them in the access frontend. I was expecting many problems in the code, but this I don't understand.
In the original vba code I use querydef with parameters to insert data into the tables, as follows, and it works without problems.

     
  Dim dbsRicevute As DAO.Database
.....
     Set dbsRicevute = CurrentDb
     
     Set qdfric = dbsRicevute.CreateQueryDef("", "PARAMETERS p1 INT, p2 DateTime, p3 Text, p4 Float, p5 Text, p6 Text, p7 Text, p8 Text; " & _
            "INSERT INTO Ricevute (Numero, Data, Da, Totale, INLETTERE, Descrizione, [Note], MetodoPagamento) " & _
            "VALUES (p1, p2, p3, p4, p5, p6, p7, p8);")
    
    Set qdfprimanota = dbsRicevute.CreateQueryDef("", "PARAMETERS p1 float, p2 float, p3 Text, p4 Datetime, p5 Float, p6 Float, p7 Text, p8 Text; " & _
            "INSERT INTO PrimaNota (Ricevuta, [Conto N], Descrizione, Data, Entrate, Uscite, [Note], Mese) " & _
            "VALUES (p1, p2, p3, p4, p5, p6, p7, p8);")

....
   With qdfric
        !p1 = NumRec
        !p2 = NData
        !p3 = NDa
        !p4 = NTotale
        !p5 = NINLETTERE
        !p6 = Ndescrizione
        !p7 = Nnote
        !p8 = NMetodo
    End With




    qdfRic.Execute dbFailOnError

....
 With qdfprimanota
        !p1 = NumRec
        !p2 = NumConto
        !p3 = Ndescrizione
        !p4 = NData
        If NEU = "E" Then
            !p5 = NTotale
            !p6 = Null
        Else
           !p5 = Null
           !p6 = NTotale
        End If
        !p7 = Nnote
        !p8 = NMoName
    End With


    qdfprimanota.Execute dbFailOnError

The Mysql backend refused to work in this way (error 3155, no other info, I am not sure why, but probably there is a reason, maybe someone may enlighten me), so I changed the code as follows

    StrSql = "INSERT INTO Ricevute (Numero, Data, Da, Totale, INLETTERE, Descrizione, [Note], MetodoPagamento) " & _
          "VALUES (" & NumRec & ",#" & NData & "#,'" & NDa & "'," & NTotale & ",'" & NINLETTERE & "','" & Ndescrizione & "','" & Nnote & "','" & NMetodo & "');"

     dbsRicevute.Execute (StrSql)
....

        StrSqlPN = "INSERT INTO PrimaNota (Ricevuta,[ContoN],Descrizione,Data,Entrate,Uscite,[Note],Mese) " & _
                   "VALUES (" & NumRec & "," & NumConto & ",'" & Ndescrizione & "',#" & NData & "#," & NTotale & "," & Null & ",'" & Nnote & "','" & NMoName & "');"

    dbsRicevute.Execute (StrSqlPN)

Now inserting into the first table (first Execute) works, inserting into the second table fails complaining about syntax error in Insert into. I write here the two INSERT INTO statements (taken by debug.print just before the second execute command, the one that fails):I cannot spot the syntax error in the second string. What am I doing wrong?



INSERT INTO Ricevute (Numero, Data, Da, Totale, INLETTERE, Descrizione, [Note], MetodoPagamento) VALUES (5377,#18/01/2025#,'Antonino Abate (2061) (1717)',15,'quindici / 00','Quote associative di rinnovo',' - Antonino Abate (2061) (1717)','1');




INSERT INTO PrimaNota (Ricevuta,[ContoN],Descrizione,Data,Entrate,Uscite,[Note],Mese) VALUES (5377,10200,'Quote associative di rinnovo',#18/01/2025#,15,,' - Antonino Abate (2061) (1717)','GENNAIO');

[Welshgasman]

Just guessing, missing value?

15,,

[roberto21]

Just guessing, missing value?

15,,

Well, no: there is Null there, as should be: you can have either Entrate(income)=value and uscite (outcome) = null, or viceversa, as you can seee in the qdfrec parameters . Unless mysql requires something different for null values in a column.

[roberto21]

Well, no: there is Null there, as should be: you can have either Entrate(income)=value and uscite (outcome) = null, or viceversa, as you can seee in the qdfrec parameters . Unless mysql requires something different for null values in a column.

Thank you welshgasman. Your remark made me think, and here is the solution. Inserting Null in the VALUES set causes the error. It is enough to skip completely the column where Null should go, and mysql fills the value null automatically.
So the code for the second insert becomes the following, removing column name Uscite and the corresponding Null.

StrSqlPN = "INSERT INTO PrimaNota (Ricevuta,[ContoN],Descrizione,Data,Entrate,[Note],Mese) " & _
                   "VALUES (" & NumRec & "," & NumConto & ",'" & Ndescrizione & "',#" & NData & "#," & NTotale  & ",'" & Nnote & "','" & NMoName & "');"

    dbsRicevute.Execute (StrSqlPN)

[June7]

Everything I've read indicates specifying NULL should work. Examples show NULL is embedded not concatenated. I tested with SQLServer linked table as well as Access table and it works same for both - concatentation fails, embedding works. I would expect same behavior with MySQL.

& ",NULL,'" &

[jojowhite]

i think Querydefs is much easier to use. You don't need to add Quotes to your Values.
You can try this modified version of your code:

  Dim dbsRicevute As DAO.Database
.....
     Set dbsRicevute = CurrentDb
     
     Set qdfRic = dbsRicevute.CreateQueryDef("", _
            "INSERT INTO Ricevute (Numero, Data, Da, Totale, INLETTERE, Descrizione, [Note], MetodoPagamento) " & _
            "VALUES (p1, p2, p3, p4, p5, p6, p7, p8);")
            
    
    Set qdfprimanota = dbsRicevute.CreateQueryDef("", _
            "INSERT INTO PrimaNota (Ricevuta, [Conto N], Descrizione, Data, Entrate, Uscite, [Note], Mese) " & _
            "VALUES (p1, p2, p3, p4, p5, p6, p7, p8);")


....
   With qdfRic
        .Parameters("p1") = NumRec
        .Parameters("p2") = NData
        .Parameters("p3") = NDa
        .Parameters("p4") = NTotale
        .Parameters("p5") = NINLETTERE
        .Parameters("p6") = Ndescrizione
        .Parameters("p7") = Nnote
        .Parameters("p8") = NMetodo
        
        .Execute dbFailOnError
    End With


....
 With qdfprimanota
        .Parameters("p1") = NumRec
        .Parameters("p2") = NumConto
        .Parameters("p3") = Ndescrizione
        .Parameters("p4") = NData
        If NEU = "E" Then
            .Parameters("p5") = NTotale
            .Parameters("p6") = Null
        Else
           .Parameters("p5") = Null
           .Parameters("p6") = NTotale
        End If
        .Parameters("p7") = Nnote
        .Parameters("p8") = NMoName
    
        .Execute dbFailOnError
    End With

[roberto21]

Thank you all for your interest and help.I have a lot to learn, anyway
The solution provided by jojowhite works fine, and indeed is very useful to me, since I have to insert many records changing only some of the parameters, so this solution is far easier and less prone to errors. I did not try the solution of june7 for lack of time, but I will.
Can you suggest some documentation that covers the impact of switching from ms sql server to mysql on vba access code? For example, I did not know about the jojowhite solution... and proceeding by trial and errors is quite time consuming (and not effective in the long run). Another example (but maybe I'll open another thread): the following code (previously working) fails when updating with error

Dim dbs As DAO.Database
Dim rstLibroSoci As DAO.Recordset
Dim StrSql As String

Set dbs = CurrentDb

StrSql = "SELECT * FROM LibroSoci WHERE [Numero Tessera] = " & NTes

Set rstLibroSoci = dbs.OpenRecordset(StrSql, dbOpenDynaset, dbSeeChanges)


rstLibroSoci.Edit
If Month(nDate) > 8 Then
rstLibroSoci![Quota associativa] = Year(nDate) + 1
Else
rstLibroSoci![Quota associativa] = Year(nDate)
End If

If Me.Nuova_TessElett <> "" Then rstLibroSoci![Tessera Elettronica n] = Me.Nuova_TessElett

rstLibroSoci.Update

Access database engine stopped the process because you and another user are attempting to change the same data


I had to change the code using dbs.execute "Update ...set where....." , but I don't understand why it does not work. The solutions found in internet were:
1) if there are bit-type fields in the table, change them to number and initialize to 0 (I do not have boolean type fields in the table)
2) add a column rowversion of type timestamp. I tried that, but mysql workbench refuses to add the column with that type TIMESTAMP() complaining that "the given datatype contains errors and cannot be accepted" (even though the datatype is listed...)

[jojowhite]

I think you will get this error when you have the table LibroSoci open on a Form and trying to edit it and
at the same time you are running you update code on the same record.

[roberto21]

That's quite reasonable, but this is not the case. The table LibroSoci is not open when I update it. In any case, this does not explain why that code worked flawlessly before, with sqlserver as backend.
PS found the problem with timestamp. Mysql workbench proposes TIMESTAMP() as data type, but only TIMESTAMP is accepted.

[jojowhite]

always, test first your recordset for EOF and BOF if both are true then you did not have any record on the returned recordset:

Dim dbs As DAO.Database
Dim rstLibroSoci As DAO.Recordset
Dim StrSql As String


Set dbs = CurrentDb


StrSql = "SELECT * FROM LibroSoci WHERE [Numero Tessera] = " & NTes


Set rstLibroSoci = dbs.OpenRecordset(StrSql, dbOpenDynaset, dbSeeChanges)


With rstLibroSoci
    If Not (.BOF And .EOF) Then
        .MoveLast
        .MoveFirst
        .Edit
        ![Quota associativa] = Year(nDate) - (Month(nDate) > 8)


        If Len(Trim(Me.Nuova_TessElett & "")) <> 0 Then
            ![Tessera Elettronica n] = Me.Nuova_TessElett
        End If
        .Update
    End If
End With

[Welshgasman]

I just test for EOF?
What is the point of MoveLast and MoveFirst unless you want the correct record count of the recordset?

With the code above, what happens if there is more than one record?

[roberto21]

Of course, I expect to retrieve one and only one record: barring major problems, only one member can have that NTes (Card number, that is). Checking eof and bof does not hurt, anyway: but I doubt this can be the cause of the update error. By the way, inserting the rowversion column (timestamp data type), the original code (with recordset) actually works...
PS for sure I am wrong, but should the line of code read


![Quota associativa] = Year(nDate) + (Month(nDate) > 8)

[June7]

Where is NTes declared and populated?

What error?

The expression with minus sign is correct. What happens is If Month(nDate) > 8 it returns True (which is -1) and subtracts that (and - -1 is a +).

[roberto21]

The error in update record using recordset .edit and .update was reported in one of the previous posts:
Access database engine stopped the process because you and another user are attempting to change the same data

As reported, adding a timestamp column did the trick
Ntes is a string passed to the procedure

Private Sub AggiornaLibroSoci(NTes As String, nDate As Date)
    Dim dbs As DAO.Database
    Dim rstLibroSoci As DAO.Recordset
    Dim StrSql As String, ExpDate As Integer
    
    Set dbs = CurrentDb
 
    StrSql = "SELECT * FROM LibroSoci WHERE [Numero Tessera] = " & NTes
    
    Set rstLibroSoci = dbs.OpenRecordset(StrSql, dbOpenDynaset, dbSeeChanges)
    rstLibroSoci.Edit
    If Month(nDate) > 8 Then
                            rstLibroSoci![Quota associativa] = Year(nDate) + 1
                        Else
                            rstLibroSoci![Quota associativa] = Year(nDate)
    End If
    
    If Me.Nuova_TessElett <> "" Then rstLibroSoci![Tessera Elettronica  n] = Me.Nuova_TessElett
    
    rstLibroSoci.Update
  
    rstLibroSoci.Close
    Set rstLibroSoci = Nothing
  
End Sub

Ok, understood. I was wrong believing that TRUE = 1. Sorry.