Insert copied records using MS Access

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!


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.



Site Timeline