Access Front Mysql Backend 1

[glenmac]

I have an Access frontend for a MySQL db. I'm trying to run an update query that runs perfectly on an all Access DB. I get and error when trying to run it in my linked Access DB. The main thing I don't understand is what MYSQL does to the code.
This is the code I'm trying to run.
Private Sub Command11_Click()

On Error GoTo Err_Command11_Click

For Each num In List3.ItemsSelected
var = var & List3.ItemData(num) & "---"
Next
If Len(var) > 0 Then
DoCmd.RunSQL "Update Orders set Custom_Ingedients = '" & Left(var, Len(var) - 1) & "' where Order_ID IN (SELECT Max([orders].[Order_ID]) FROM ORDERS);"

End If



Exit_Command11_Click:
Exit Sub

Err_Command11_Click:
If (Err = ERR_OBJNOTEXIST) Or (Err = ERR_OBJNOTSET) Then
Resume Next
End If
MsgBox Err.Description
Resume Exit_Command11_Click

End Sub

This is what I get after running a debug statement.

Update Orders set (Custom_Ingedients)=('test---Salami--') where Order_ID IN (SELECT Max([orders].[Order_ID]) FROM ORDERS);
The above code works great in an Access environment.

This is the error I get trying to run the code on the Linked Access DB.
[TCX][MYODBC]You have an error in yourSQL syntax. Check the manual that corresponds to your MYsQL server version for the right syntax to use near '(SELECT MAX(MS2.Order_ID) FROM orders MS2))'at the line 1 (#1064)

I don't know why the code gets changed with the "MS2" inserted. Can anyone explain to me what's happening here and hopefully help me with a work around. All help will be much appreciated.

 

[swampBoogie]

Mysql does not support subqueries. (It will in version 4.1)

Until then you need to do split this into two queries, one that selects the max value and one update.

 

[glenmac]

Thanks for the help!!