Check whether Docmd.RunSQL or OpenQuery is successfull

[Guest]

Is there a chance to check, whether a Docmd.RunSQL or OpenQuery was
successfull?

thanks in advance

jokobe

 

[Allen Browne]

No. These approaches are both of limited use, as you have no idea
programmatically whether things succeeded or not.

If you use the Execute method instead, you can know whether it errored, and
how many records were affected.

Example:
Dim db As DAO.Database
Dim strSql As String

Set db = dbEngine(0)(0)
strSql = "DELETE FROM Table1 WHERE MyField = 99;"

db.Execute strSql, dbFailOnError
MsgBox db.RecordsAffected & " record(s) deleted."

The dbFailOnError switch drops you to your error handler, where you can take
whatever action you need.

If an error occurs part way through (e.g. it deleted the first 100 records,
but could not delete #101 because someone else was editing it), the first
100 are still gone. If you want to avoid that, the Execute can be wrapped in
a transaction, so you can roll it back for an all-or-nothing result. For
more info and an example, see:
Archive: Move records to another table
at:
http://allenbrowne.com/ser-37.html

 

[Guest]

Thanks for your fast help Allen.


Jokobe

No. These approaches are both of limited use, as you have no idea
programmatically whether things succeeded or not.

If you use the Execute method instead, you can know whether it errored, and
how many records were affected.

Example:
Dim db As DAO.Database
Dim strSql As String

Set db = dbEngine(0)(0)
strSql = "DELETE FROM Table1 WHERE MyField = 99;"

db.Execute strSql, dbFailOnError
MsgBox db.RecordsAffected & " record(s) deleted."

The dbFailOnError switch drops you to your error handler, where you can take
whatever action you need.

If an error occurs part way through (e.g. it deleted the first 100 records,
but could not delete #101 because someone else was editing it), the first
100 are still gone. If you want to avoid that, the Execute can be wrapped in
a transaction, so you can roll it back for an all-or-nothing result. For
more info and an example, see:
Archive: Move records to another table
at:
http://allenbrowne.com/ser-37.html