MySQL error when submitting more that one query using vba

Question

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.

Look at this stackoverflow.com/questions/4144961/…

sqlab
– sqlab

2014-03-26 16:02:11 +00:00
Commented Mar 26, 2014 at 16:02 — sqlab
– sqlab, Commented Mar 26, 2014 at 16:02

talegna · Accepted Answer · 2014-03-26 16:03:32Z

1

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"):

answered Mar 26, 2014 at 16:03

talegna

2,3992 gold badges21 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

crazylane Over a year ago

Brilliant, it works - I used multiple row insert syntax. Thanks

Collectives™ on Stack Overflow

MySQL error when submitting more that one query using vba

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related