Test for success of "DoCmd.RunSQL strSQL" Insert Statement.

[Robeen]

How can I Test for success of "DoCmd.RunSQL strSQL" Insert Statement.
I am archiving records to an external Access DB - and it is working.
Is there a 'Best Practice' for verifying that the Insert to the external DB was successful before I Delete the Archived row[s] from the Production DB?

I'd appreciate if you could point me in the right direction. I have not been able to find the answer on GoogleIsMyFriend yet!!
Currently, I am turning the SetWarnings Off before the Insert and On after.



Thanks in advance.

[pbaldy]

Use Execute:

  Dim db                      As DAO.database
  Dim intAppendCount          As Integer  

  Set db = CurrentDb

  db.Execute "qryAppendAutoGas"
  intAppendCount = db.RecordsAffected

[John_G]

Just to add to pbaldy's note -

You should use

db.Execute "qryAppendAutoGas", dbFailonerror

This will cause a runtime error to be generated if the db.Execute command causes an error.

John

[pbaldy]

Sorry, I should have added that. It's optional, and in the app I cut that from I didn't want errors to be raised (automated import routine). Most of the time you would.