MySQL insert records using MS Access

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

Threaded View

I have a MySQL database linked to an MS Access front-end.

I have an app which works fine with the local copy of the Access DB,
but when run with the linked tables for MySQL, it fails.

I'm trying to copy over related data for products, which is held in
seperate tables.
The main copying of the producy works fine, just not any related
product data.

The part code I have is:


MySql1 = "INSERT INTO Related (product_ID, product_Ref, prod_relate) "
    MySql1 = MySql1 & "SELECT " & NewProductID & " ,
Related.product_Ref, Related.prod_relate FROM Related "
    MySql1 = MySql1 & "WHERE Related.product_ID = " & currentid
    db.Execute MySql1, dbFailOnError


At present it hits the last line and fails ....... what am I doing
wrong ?

I import link the tables via ODBC.
The problem must lie in the SQL syntax ???? for MySQL


Re: MySQL insert records using MS Access

David wrote:
Quoted text here. Click to load it

MySQL cannot INSERT INTO and SELECT FROM the same table at the same
time.  You may want to create a TEMPORARY table, copy some data into
that, and then copy that to the destination table.

Bill K.

Re: MySQL insert records using MS Access

Bill Karwin wrote:
Quoted text here. Click to load it

For example:

Step 1.
   CREATE TEMPORARY TABLE temp_related LIKE Related;

Step 2.
   INSERT INTO temp_related (product_id, product_ref, prod_relate)
     SELECT & NewProductID &, r.product_ref, r.prod_relate
     FROM Related AS r
     WHERE r.product_id = & currentid;

Step 3.
   INSERT INTO Related SELECT * FROM temp_related;

Step 4.
   DROP TABLE temp_related;

I'll let you put in the quotes and stuff.

Bill K.

Site Timeline