MySQL Bugs: #70479: Using Multiple MySQL UPDATE statements in an Access Passthru query fails

Bug #70479	Using Multiple MySQL UPDATE statements in an Access Passthru query fails
Submitted:	1 Oct 2013 17:04	Modified:	3 Oct 2013 12:27
Reporter:	John Skolits	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.5.32	OS:	Windows
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any
Tags:	access, ODBC, updates

Description:
Asked by: askolits

MySQL ODBC Driver 5.2
Windows 7 64 bit
MySQL 5.5.32 Ubunto 0.13.04.1

I'm trying to create a passthru query in Access against a MySQL database.

The following works if I use MySQL directly (using a query window in MySQL Workbench 6.0)

UPDATE `TEST_IMP` SET `strTestName` = 'help' WHERE `idItemizedTestList_ID` = '3';
UPDATE `TEST_IMP` SET `strTestName` = '22G' WHERE `idItemizedTestList_ID` = '44'
But when I put it in a passthru query in Access. It fails.

YET!

If I do each one individually in the pass thru query, the individual update works. It only happens when I do two simultaneously.

I could always run separate passthrus for each Update, but I may have a few hundred updates and there will be a performance issue.

The ODBC driver reports an error on Line 4. (Syntax error) Is there special syntax needed to have two UPDATES in a passthru?

Remember, the full statement works when using Workbench. So, I know the syntax should be correct.

How to repeat:
Using Access 2010, create a new blank database.
Create a Pass Thru Query.
In the properties box, click the builder (...) for the ODBC connection.

Create a new 'Machine Data Source' - Wizard starts
Select 'System Data Source'
Pick MySQL ODBC Driver 5.2 (Ansi or unicode, doesn't matter)
Hit finish.
Log onto your MySQL DB
Save passowrd when requested by the Access prompt.

run something similar to the following code:
UPDATE `TEST_IMP` SET `strTestName` = 'help' WHERE `idItemizedTestList_ID` = '3';
UPDATE `TEST_IMP` SET `strTestName` = '22G' WHERE `idItemizedTestList_ID` = '44'

Execute Query

Hi John,

Thank you for your interest in MySQL software.
I do not think it is a bug in MySQL Connector/ODBC.
You are trying to execute multiple queries, but this function is not enabled in the driver by default.

You have to enable the multiple query execution through the Driver Setup dialog:

Details -> Connection -> Allow multiple statements [x]

Or add MULTI_STATEMENTS=1 to the connection string.

Note that MS Access could save the old DSN (without the multiple query option enabled), so it is better to delect/create objects in Access to make it working.

Both those suggestions worked!

Trust me, I spent hours on this and much of it on many web sites, but no-one knew the answer.

Thanks for your help!!

According last comment.