Insert copied records using MS Access

[David]

Hi,

I've been using MS Access as a front end to our remote MySQL db for a
long time.
I've just added a facility for clients to select a record to copy along
with all its sub-records.

The initial record copies accross ok, but I seem to be having a spot of
bother getting the sql syntax to work. I keep getting an ODBC error
3146 ?

___________________

Here is some of the code I have .......... which works fine with an MS
Access back-end but not MySQL

code ...............

Dim MySql1 As String
Dim MySql2 As String
Dim MySql3 As String
Dim MySql4 As String
Dim MySql5 As String
Dim MySql6 As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = DBEngine(0)(0)
'Set db = CurrentDb

NewProductCode = DMax("product_ID", "products")

'------------------ COPY RELATED PRODUCTS OVER
------------------------
------------ perhaps it is failing because no line ID is being created
for each record of the related table ??
'
''''' Grab next related ID ......... trying to test various ideas .....

NewRel = 1 + DMax("related_ID", "Related")
MySql1 = "INSERT INTO Related (product_ID, product_Ref,
prod_relate) "
MySql1 = MySql1 & "SELECT " & NewProductCode & " as NewProductID,
Related.product_Ref, Related.prod_relate FROM Related "
MySql1 = MySql1 & "WHERE Related.product_ID = " & currentid
db.Execute MySql1, dbFailOnError
--------------------------------------

This code keeps failing with error 3146 on the db.Execute line ........
i'm stumped, but seriously need to get this working soon....

Appreciate any help you can offer.

Thanks

David