• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 636
  • Last Modified:

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';

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

Open in new window

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


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
  • 4
  • 2
1 Solution
Rey Obrero (Capricorn1)Commented:
in Access, you can only have One (1) Update statement in a query. (whether Passthru or a regular update query).
askolitsAuthor Commented:
Is there a way to do something like:

(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.
Rey Obrero (Capricorn1)Commented:

SET strTestName = IIF([idItemizedTestList_ID] = '3','hel',IIF([idItemizedTestList_ID] = '44','22G',[strTestName]))
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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.
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; " _

      '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
askolitsAuthor Commented:
Found the solution myself. I provided code for future visitors to this question.
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.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now