asked on

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!

jimyX

> 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).

katerina-p

ASKER

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?

jimyX

> 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.

katerina-p

ASKER

> 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.

Scott McDaniel (EE MVE )

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.

jimyX

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.

Dale Fye

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').

katerina-p

ASKER

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

Dale Fye

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.

katerina-p

ASKER

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.

Dale Fye

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.

katerina-p

ASKER

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.

jimyX

> 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.

Scott McDaniel (EE MVE )

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.

ASKER CERTIFIED SOLUTION

katerina-p

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

katerina-p

ASKER

Link to MySQL 'bug' report which sets out fix