Using Multiple MySQL UPDATE statements in an Access Passthru query fails

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)

UPDATE `TEST_IMP` SET `strTestName` = 'hello' 
WHERE `idItemizedTestList_ID` = '3';

UPDATE `TEST_IMP`  
SET  `strTestName` = '22G' WHERE `idItemizedTestList_ID` = '44';

Open in new window


But when I put it in a passthru query in Access. It fails.

BUT!

If I do each one individually in the pass thru query, the 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. 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.
Access PassThru error
askolitsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
in Access, you can only have One (1) Update statement in a query. (whether Passthru or a regular update query).
0
askolitsAuthor Commented:
Is there a way to do something like:

UPDATE `TEST_IMP`
(SET `strTestName` = 'hel' WHERE `idItemizedTestList_ID` = '3' ,
 SET  `strTestName` = '22G' WHERE `idItemizedTestList_ID` = '44';)

I obviously tried this and it failed. I've been searching for a few hours and haven't been able to find an alternative method.

I did send a bug report to Oracle and referred the ODBC driver, thinking that may be the issue.
Do you think they limit it by design? You can do this within Workbench, wonder why not as a passthru.
0
Rey Obrero (Capricorn1)Commented:
try

UPDATE TEST_IMP
SET strTestName = IIF([idItemizedTestList_ID] = '3','hel',IIF([idItemizedTestList_ID] = '44','22G',[strTestName]))
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

askolitsAuthor Commented:
Well, I'm a bit confused. I don't believe "IIF"s work in pass thru queries because I don't believe they're acceptable for MySQL queries. I pasted your code into a MySQL query (Using MySQL Workbench) and it doesn't like the syntax.
0
askolitsAuthor Commented:
I found the answer. Oracle told me how to do it. Turns out you 'can' do multiple Update statements in a passthru query. It's an option that is not turned on by default within the ODBC setup. See below image that shows how to do it

Or in VBA, you can set a parameter in the connection string. "MULTI_STATEMENTS=1"

Function RunMultStatementMySQLPassThruQuery()
      
      Dim wksp As DAO.Workspace
      Dim dabs As DAO.Database
      Dim strCon As String
      Dim strSQLUpdate As String
      
      'Create the connection string for MySQL
      'Make sure you include the last argument " MULTI_STATEMENTS=1"
10    strCon = "ODBC;DRIVER=MySQL ODBC 5.2 ANSI Driver;SERVER=localhost;UID=root;PWD=mypassword; " _
               & "DATABASE=Check4It_MainDB;PORT=3306;DFLT_BIGINT_BIND_STR=1; MULTI_STATEMENTS=1"

      'Set up workspace and database objects
20    Set wksp = DBEngine(0)
30    Set dabs = wksp.OpenDatabase("", True, True, strCon)

      'Load the SQL String
      'Note: Update statements are separated by a semicolon
      'It can be seen after the ='3' below
40    strSQLUpdate = "UPDATE `tbl_005_002_ITEMIZED_TEST_LIST` SET `strTestName` = 'help' " _
                   & "WHERE `idItemizedTestList_ID` = '3' ; " _
                   & "UPDATE `tbl_005_002_ITEMIZED_TEST_LIST` SET `strTestName` = '22G' " _
                   & "WHERE `idItemizedTestList_ID` = '44' "

     'Execute the query
50    dabs.Execute strSQLUpdate, dbSQLPassThrough


      'Close the database objects
60    dabs.Close
70    Set dabs = Nothing
80    wksp.Close
90    Set wksp = Nothing


End Function

Open in new window


Setting Up MySQL ODBC Multiple statments in Access PassThru
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
askolitsAuthor Commented:
Found the solution myself. I provided code for future visitors to this question.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.