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)"