Insert query run in VB

[dapiaoen]

I'm parsing several fields that have been imported with comma delimited data into several tables. When I finish, there is some data I need to append to a table. I run a query in Access and it works fine, but if I run the same query in VB after the code parsing all the other data, using:

DoCmd.RunSQL "insNewRecords"

it won't run. The query is something like this:
---------
Insert into table1(filename, labelname) where labelname not in (select labelname from qryFindLabels).
---------------
[This may not be correct, but the query is okay from Access, so it works (my database is in a secure area, so I don't have the code handy).]

Several recordsets are open when I try the RunSQL, including the table to which I want to append.

Any help? Thanks,

Chris

 

[Fornatian]

If it's a saved query you are trying to run you are using the wrong syntax. Use:

Docmd.SetWarnings False
Docmd.OpenQuery "YourQuery"
Docmd.SetWarnings True

 

[dapiaoen]

Insert query from VB

Thanks, Fornation.

I'll try it. I searched a bit more and found the following

CurrentProject.VBcode.execute 'query' (syntax is at work and I can't find it again).

Anyway, it ran without error -- exept for the fact that the tables did not update with the new records.

Appreciate the help.

 

[Calvin]

Both are options, just have to figure out which is best for you.

Fornation's method is good for saved (canned) querries, but if you need to modify data on the fly as the data changes every instance it is run. then the following would be ideal.

CurrentDB.Execute "INSERT INTO table1 (field1, field2) VALUES (data1, data2) WHERE field1 NOT IN (SELECT * FROM query1)"

or something to that effect, the syntex with the IN SELECT may need some cleaning up.

then you can concatinate field or table or what ever data you need.

 

[dapiaoen]

Insert Query from DB

Found the answer. Easy of course. One has to do an rst.Save. Update does not work. If MS Access documentation had any coherence to it, I would have found it first try instead of by accident.

Thanks,