MySQL insert records using MS Access

Hi,

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


Thanks

Re: MySQL insert records using MS Access

David wrote:
> INSERT INTO Related ... SELECT ... FROM Related

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.

Regards,
Bill K.

Re: MySQL insert records using MS Access

Bill Karwin wrote:
> David wrote:
>> INSERT INTO Related ... SELECT ... FROM Related
>
> 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.

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.

Regards,
Bill K.