Why do I NOT get a duplicate key error ?

[mike-davies]

I have a simple table called "dummy" with one field that is the primary unique key. The following code does NOT produce a duplicate key error. Why is that ?

Private Sub Form_Load()
Dim dbsCurrent As dao.Database

Set dbsCurrent = CurrentDb()
On Error GoTo ooops

dbsCurrent.Execute "insert into dummy (mykey) values (1)"
dbsCurrent.Execute "insert into dummy (mykey) values (1)"

MsgBox ("I should have a duplicate key error by now." & vbCrLf & Error(err))
GoTo ok
ooops:
MsgBox (Error(err))
ok:
End Sub

 

[gemma-the-husky]

i think its because execute never fails! even if it fails! - have a look at the help - it mentions dbfailonerror as a parameter that might do something useful

tricky though - if you instead

DoCmd.RunSQL "insert into dummy (mykey) values (1)"

you get the normal access confirms etc, but if you suppress those you also don't get the oops!

i suppose the only way is to actually use a recordset and try
rst.addnew
etc, then i am sure you will get an error!

 

[mike-davies]

Thanks.

Having read the blurb it talks about using transactions too. But that is just plain daft, because how do you know to issue an explicit rollback if you can't detect the error.

Hmmm, I think I'll experiment with .addnew

Thanks for the prompt to read the blurb.