Multiple MySQL SQL UPDATE statements through MS Access / VBA

Hi All,

I need to run multiple UPDATE statements from Access/VBA to MySQL.

eg SQL:
"START TRANSACTION; UPDATE database.table SET field = v1 WHERE index=n; UPDATE database.table SET field = v2 WHERE index=n2; COMMIT;

Open in new window


Error:
[MySQL][ODBC 3.51 Driver][mysqld-5.6.17]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ... at line 1.

The error is the same no matter what is sent through, and the SQL runs fine in MySQL Workbench (and dbForge Studio), so I presume it's an Access-specific issue?

Public Sub RunMySQL_Passthrough(db As String, sSQL As String, Optional OVERRIDE_SVR As String, Optional AllowLogErrors As Boolean = True)
Dim cmd                       As ADODB.Command
Dim strConn                   As String
Dim ConnectStrg               As String
Dim str                       As String

If Not CBool(Len(OVERRIDE_SVR)) Then
  ConnectStrg = "Driver={MySQL ODBC 3.51 Driver};" & _
                "Server=" & SVR & ";" & _
                "Port=3306;" & _
                "Option=131072;" & _
                "Stmt=;" & _
                "Database=" & db & ";" & _
                "Uid=" & USER_MYSQL_DB & ";" & _
                "Pwd=" & PASS_MYSQL_DB & ""
Else
  ConnectStrg = "Driver={MySQL ODBC 3.51 Driver};" & _
                "Server=" & OVERRIDE_SVR & ";" & _
                "Port=3306;" & _
                "Option=131072;" & _
                "Stmt=;" & _
                "Database=" & db & ";" & _
                "Uid=" & USER_MYSQL_DB & ";" & _
                "Pwd=" & PASS_MYSQL_DB & ""
End If
strConn = ConnectStrg  
str = sSQL
Set cmd = New ADODB.Command
With cmd
  .ActiveConnection = strConn
  .CommandText = str                                                 
  .CommandType = adCmdText                                   
  .Execute
End With
Set cmd = Nothing

Exit Sub
End Sub

Open in new window


Can anyone help? I can see a workaround using a multiple INSERT statement (works fine) into a temp table but at the moment this is all data in memory. Also interested to find out if there's a solution.

Many thanks!
katerina-pAsked:
Who is Participating?
 
katerina-pAuthor Commented:
Scott - yes I'd seen elsewhere that it was fine with SQL Server.

Anyway: I'm unsure why I didn't find it on previous searches, however I've found the following: https://bugs.mysql.com/bug.php?id=70479 which essentially sets out that Multiple Statements are not a feature within the 3.5.1. MySQL ODBC driver.

I've downloaded 5.2 from https://dev.mysql.com/downloads/file.php?id=452089 and created new DSN with 'Multiple Statements' True, created new pass-through query from that and it's working.

I suppose it's besides the point I've already spent the time coding the workaround!
0
 
jimyXCommented:
> MySQL server version for the right syntax to use near ... at line 1.

Near which part of your query? You need to show the complete error message.

Try one statement:
UPDATE database.table SET field = v1 WHERE index=n;

Open in new window

Because IIRC, MS Access/ADO does not support multiple statements (not sure though, haven't used them for long).
0
 
katerina-pAuthor Commented:
The sql doesn't appear to be relevant;  as I said the SQL is good and it fails for any multiple UPDATE statement.

Single statements do work.  I was hoping wrapping in transaction would help. It gets around the MySQL limit on multiple updates.

Is there no way to properly pass through SQL from Access to MySQL?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
jimyXCommented:
> Is there no way to properly pass through SQL from Access to MySQL?

Quick research resulted in SQL pass-through.

I can not test if it will work with multiple statements.
0
 
katerina-pAuthor Commented:
> Quick research resulted in SQL pass-through.

This adds a 'standard' pass-through qry to the Access project, but you can't run multiple statements.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You don't really need to use ADOX to create a Pass Through. You can use the Access interface to do it.

1. Click Create - Query Design.
2. Don't select any tables on the "Show Tables" dialog. Instead, click Close
3. Click "Pass-Through" in the Design ribbon
4. In the Property Sheet, set the "ODBC Connect Str" to a valid Connection String
5. Save that query.

I'm not sure this will work either, however. Your module above basically bypasses Access by creating an ADO Command object. If it won't work with that method, there's a good chance it won't work with this one, and you'll be stuck issuing commands one at a time.
0
 
jimyXCommented:
Now this is pure MS Access, can't help you further.
I see MS Access is coming up short in supporting that.
I leave the floor for MS Access Experts, in case there is a work around. But I suspect there isn't and you will end up parsing the Queries and executing each at your own pace.
0
 
Dale FyeCommented:
A pass-through query must use SQL that is valid for the RDBMS that it is being passed through to, so multiple statements should work properly.

Using DAO, it would look something like:

    Set db = CurrentDb
    Set qdf = db.CreateQueryDef("")
    qdf.SQL = "START TRANSACTION;" _
            & "UPDATE database.table SET field = v1 WHERE index=n; " _
            & "UPDATE database.table SET field = v2 WHERE index=n2;" _
            & "COMMIT;"
    qdf.Connect = ConnectString
    qdf.Execute  dbFailOnError

Open in new window

But I'm concerned with the syntax of your update statement.  It appears that the values of V1, V2, N, and N2 are all variables, and would not work in this format.  The values of those variables would have to be written out in the SQL statement, something like:

& "UPDATE database.table SET field = " & v1 & " WHERE index= " & n & "; "

This assumes that those variables are numeric, not strings.  If strings, they would have to be wrapped in whatever the appropriate string delimiter is for MySQL (in SQL server, it would be the single quote').
0
 
katerina-pAuthor Commented:
Scott McDaniel - thanks, but yes Access throws error when multiple statements are attempted, hence as you suspected my attempt in module in the OP.
jimyX - many thanks

Dale Fye I'm concerned with the syntax of your update statement It was not actual code, just the structure. I didn't want to confuse the issue with the SQL because I can't get any multiple statements to work via Access at all. Actual example of this update SQL:

START TRANSACTION;
UPDATE list.eans 
SET 
    bean_SiteQuantityCycleStatus = 1,
    bean_SiteQuantityCycleAsAtDate = '2015-04-03 23:37:29'
WHERE
    eans.bean_brandid = 'AAB'
        AND eans.bean_ean = '9780071221351';
UPDATE list.eans 
SET 
    bean_SiteQuantityCycleStatus = 1,
    bean_SiteQuantityCycleAsAtDate = '2015-04-03 23:37:45'
WHERE
    eans.bean_brandid = 'AAB'
        AND eans.bean_ean = '9780071351454';  
        COMMIT;

Open in new window

0
 
Dale FyeCommented:
katerina,

if that syntax works in MySQL, then it should work in an Access pass-through query.

I generally create two generic pass-through queries in my Access applications which work with SQL Server.  One of these is simply used to run stored procedures which do not return any data to Access.  The other has the "returns records" property set to Yes and is used when I expect SQL Server to return one or more records to Access to process.
0
 
katerina-pAuthor Commented:
if that syntax works in MySQL, then it should work in an Access pass-through query.

One would think :) but it doesn't work in Access. I think this is an Access issue because the SQL is fine in Workbench and other 3rd-party applications.

 I can't /don't want to do them individually because there can be up thousands.

Stored Procedures, single statements using a View, Functions, all working fine. Just can't find a way to send multiple statements through Access.

Many thanks
k.
0
 
Dale FyeCommented:
Did you try Scott's recommendation to create a pass-through query using the query designer, using DAO rather than ADO?

Try a simple one (one update statement, no transaction), setup the connection string in the query properties, and then attempt to run the query.

Then add a second update statement to the SQL string and run that.

If you can get that to work, it does with SQL Server, then you can modify the connection string and the querydef.SQL properties via code.
0
 
katerina-pAuthor Commented:
Did you try Scott's recommendation to create a pass-through query using the query designer, using DAO rather than ADO?

Sorry for not being explicit. Yes I already tried this prior to posting. I use pass-throughs where possible elsewhere, Single statements are fine. Multiple (be it 2 UPDATES, or a single/n UPDATE statement in a trans ), and it throws the syntax error from the OP.
0
 
jimyXCommented:
> I can't /don't want to do them individually because there can be up thousands.

Despite that note, I just want to give different prospective, just in case no multi-statement solution found or the queries themselves accept to be shortened/merged. For instance, the two queries you provided become:

UPDATE list.eans
SET bean_SiteQuantityCycleStatus =
CASE
WHEN (eans.bean_brandid = 'AAB') AND (eans.bean_ean = '9780071221351') THEN    1
WHEN (eans.bean_brandid = 'AAB') AND (eans.bean_ean = '9780071351454') THEN    1
End,

bean_SiteQuantityCycleAsAtDate =
CASE
WHEN (eans.bean_brandid = 'AAB') AND (eans.bean_ean = '9780071221351') THEN    '2015-04-03 23:37:29'
WHEN (eans.bean_brandid = 'AAB') AND (eans.bean_ean = '9780071351454') THEN    '2015-04-03 23:37:45'
END

WHERE
((eans.bean_brandid = 'AAB') AND (eans.bean_ean = '9780071221351'))
OR
((eans.bean_brandid = 'AAB') AND (eans.bean_ean = '9780071351454'));

Open in new window


I realize that there are too much conditions, but I just translate-merged your two queries , word for word. I just though that would be clearer to match against your original. It could be made shorter and easier than that.

PS: I did not test that query, might need some work.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Hmmm ... that's odd. I'm connected a MS SQL Server, and if I create a Pass Through I can issue multiple statements. For example, I did this in a Pass Through in my Access 2013 database:

update material set description='END CAP FITTING-4-SANITARY 00' WHERE material='CR-001000';
update material set description='ANSI ELBOW FLANGE-1 00' WHERE material='CR-001330'

The point of my test was to verify if you could use this syntax in Access, and it worked fine - the above statement updated the two records in my database, as expected, so I would have to assume that you CAN issue multiple statements in Access, at least against a MSSQL database. I don't have MySQL running on this machine/network, so I can't test that.
0
 
katerina-pAuthor Commented:
Link to MySQL 'bug' report which sets out fix
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.