CODEWITHSUNDEEP
mysqlsqlexcelvba
crazylane
crazylane

Reputation: 62

MySQL error when submitting more that one query using vba

I have a strange problem when I try to send this MySQL query:

insert into tblWydatkiPLN(
  IDDelegacji, 
  IDKategoria, 
  IDTypTransakcji, 
  Kwota, 
  Opis
) values(
  '13', 
  '3', 
  '1', 
  '44.15', 
  'Taxi'
);

insert into tblWydatkiPLN(
  IDDelegacji, 
  IDKategoria, 
  IDTypTransakcji, 
  Kwota, 
  Opis
) values(
  '13', 
  '3', 
  '1', 
  '57.71', 
  'Taxi'
);

using vba. I'm sending it with this code (using strSQL variable):

Set DBConnection = CreateObject("ADODB.Connection")
DBConnection.Open strConnString
Debug.Print strSQL

If InStr(strSQL, "select") = 1 Then
    Set DBRecordset = CreateObject("ADODB.Recordset")
    DBRecordset.Open strSQL, DBConnection, adOpenStatic
    If Not DBRecordset.EOF Then
        myArray = DBRecordset.GetRows()
    End If
    Set DBRecordset = Nothing
Else
    DBConnection.Execute strSQL
End If
Set DBConnection = Nothing

this is the error message that I receive:

"[MySQL][ODBC 5.2(w) Driver][mysqld-5.5.35-MariaDB]You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax"

The strange thing is that when I submit a single insert statement it works perfectly. I do realize that a workaround would be to send these statements one by one - but that doesn't seem to be an optimal solution.

Thanks in advance, any help would be very much appreciated.

Upvotes: 0

Views: 675

Answers (1)

talegna
talegna

Reputation: 2403

The problem appears to be when you are attempting to perform multiple actions in a single ADODB connection and it appears you are not the only person with this problem if you look in the right places. One solution I've seen offered is to use the multiple row insert syntax:

INSERT INTO tblWydatkiPLN(
  IDDelegacji, 
  IDKategoria, 
  IDTypTransakcji, 
  Kwota, 
  Opis
) VALUES
    ('13', '3', '1', '44.15', 'Taxi')
   , ( '13', '3', '1', '57.71', 'Taxi' )

You might find the following articles helpful (found by googling "multiple actions adodb"):

Upvotes: 1

Related Questions