VBA MySQL Several queries in one Execute

Question

I'm programming a tool which grabs data from local systems and then sends data to MySQL database. Now, it's running on Access DB, but it's very time-consuming to merge data from few local databases (it's running on some computers).

I decided to move to MySQL, connection works as well executing single queries. But doing that in this way is not time-acceptable (for my data batch - 1,5 second to add data to accdb and almost 80 seconds to add the same to MySQL).

I'm aware that remote MySQL database will never be so fast as local Access DB (MySQL DB is on shared hosting, it's my private project).

I talked to my friend who is PHP & MySQL dev and he told me that running queries at batch would shorten the execution (e.g. execute 100 queries in one time instead of 100 single executions).

I modified my script to do so, but if I try to execute multiple queries I have Syntax Error (when I paste the same query into phpMyAdmin immediate window it works, so this is not syntax).

I read that ADODB Connection (which I use) cannot execute multiple queries - but if there any alternative?

I could write little console .NET application which reads .sql files and then executes a batch query, but first I want to be sure if it's really necessary.

My connection is declared

Set cn = CreateObject("ADODB.Connection")
cn.Open "Driver={MySQL ODBC 5.3 Unicode Driver};Server=SERVER;Database=DB_NAME;Uid=DB_USER;Pwd=DB_PWD;Option=3" 

strQuery = "CREATE TABLE Z AS (SELECT * FROM tblOffers WHERE 1=2); CREATE TABLE ZZ AS (SELECT * FROM tblOffers WHERE 1=2);"
cn.Execute (strQuery)

These queries are just for testing - I'm not creating new tables in my project, but upsering data (insert or update if duplicate).

My single query looks like this:

INSERT INTO tblOffers (`TITLE`, `LINK`, `IMG`, `PRICE`, `DATE`, `PLATFORM`, `OFF_ID`, `LOCATION_ID`) 
SELECT * FROM (
SELECT "Zgrywus PC", "https://www","zgrywus-pc-znin.jpg",10,'2017-07-08',"PC","6hZH9","0929865") As Tmp 
ON DUPLICATE KEY 
UPDATE `TITLE` = "Zgrywus PC", 
`LINK` = "https://www", 
`IMG` = "zgrywus-pc-znin.jpg", 
`PRICE` = 10, 
`DATE` = '2017-07-08', 
`PLATFORM` = "PC", 
`OFF_ID` = "6hZH9", 
`LOCATION_ID` = "0929865";

EDIT: @joanolo, here's full code snippet:

Sub ExportDataToAccess()

    Dim cn As Object
    Dim strQuery As String, strClrQuery As String
    Set cn = CreateObject("ADODB.Connection")

    cn.Open "Driver={MySQL ODBC 5.3 Unicode Driver};Server=SERVER;Database=DB;Uid=UN;Pwd=PWD;Option=3"
    strQuery = "CREATE TABLE Z AS (SELECT * FROM tblOffers WHERE 1=2); CREATE TABLE ZZ AS (SELECT * FROM tblOffers WHERE 1=2);"
    Debug.Print strQuery
    cn.Execute (strQuery)


    cn.Close
    Set cn = Nothing

End Sub

And produced error is: Screen

EDIT2, 10-07-2017

I decided to run multiple row INSERT - it works - so my problem is resolved, but if anyone would have any solve for issue of this topic, please share.

Can you show us a sample of the code that produces the *Syntax error*? — joanolo, Jul 09 '17 at 10:54
does this work ? `CREATE TABLE abc ( a CHAR , b char );CREATE TABLE xyz ( a CHAR , b char );` — jsotola, Jul 24 '17 at 00:17
@jsotola - I think that's issue specific for ADODB connection and these types of queries cannot be executed at once. This error could be only fixed by changing vba code, not SQL. — Krukosz, Jul 25 '17 at 09:09
that is why i asked you to try a really simple create table query. because if that does not work, then a more complicated query will not work either. — jsotola, Jul 27 '17 at 01:18

score 1 · Answer 1 · answered Mar 25 '21 at 16:30

@Krukosz - I had the same question today, I solved it on another way. Well you can execute each command in a ForEach- oder other Loop, separated/splitted by the 'virtual' Dash-Comma ';' you use in 'normal' SQL-Statements not in VBA. It's cool that you can use vars over more Execute's.

Here is an example code, and @start will be 1082 in the end of this example. But it seems to remember vars only for a few minutes. If you wait 5 minutes and try the last row again, the value of @start will be 'Null'.

Set rst = dbc.Execute("SET @start = 898")
Set rst = dbc.Execute("SET @start = @start+184 ")
Set rst = dbc.Execute("SELECT @start,'test'")
..
.

VBA MySQL Several queries in one Execute

1 Answers1