472,111 Members | 1,840 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,111 software developers and data experts.

Insert copied records using MS Access


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.



Jul 13 '06 #1
0 2223

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

10 posts views Thread by shank | last post: by
5 posts views Thread by Paul Shaw | last post: by
8 posts views Thread by jking482 | last post: by
6 posts views Thread by rn5a | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.