469,338 Members | 8,430 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,338 developers. It's quick & easy.

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 2153

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 suresh191 | last post: by
reply views Thread by Purva khokhar | last post: by
1 post views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.