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

DoCmd.RunSQL vs rs.Open

P: n/a
Hello,

I am running an sql statement that INSERTS INTO a table. If I run the
query using docmd.runSQL, it works fine - new records are added to the
table and duplicate records are disregared ( I use DoCmd.SetWarnings
False so the users don't see the warning about duplicate records).

If I run the same query using this:

rs.Open qryPolePosition1, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

I get a message saying The changes I request can't be made because
they'll cause duplicate values...

Even if I wanted to live with the warning message, the new records are
not being added to the table. It seems like as soon as the error
occurs, processing stops.

I can use the docmd.runSQL but it is noticeably slower than the
rs.open method.

I'm including my sql statement. I was wondering if anyone could tell
me why this query works one way and not the other.

Thanks, Andy
qryPolePosition1 = "INSERT INTO tblPoleMaintDetails " & _
"(fldPoleDataIDfk, fldPolePos, fldPoleCode,
fldPoleStatus, fldImmAtt) " & _
"SELECT tblPoleData.fldId, '1'," & _
"tblPoleData.Code1, tblPoleData.Status1,
tblPoleData.ImmAttn1 " & _
"FROM tblPoleData " & _
"WHERE (tblPoleData.PoleSize1 Is Not Null) OR
(tblPoleData.Code1 " & _
"Is Not Null) OR (tblPoleData.Status1 Is Not
Null) OR (tblPoleData.ImmAttn1 <>0)"
Nov 12 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.