By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,731 Members | 1,055 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,731 IT Pros & Developers. It's quick & easy.

MySQL insert records using MS Access

P: n/a
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

Jul 17 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.
Jul 17 '06 #2

P: n/a
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.
Jul 18 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.