Avoid DoCmd.RunSQL in Microsoft Access

TL;DR

Instead of...

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM MyTable"
DoCmd.SetWarnings True

...use...

CurrentDb.Execute "DELETE * FROM MyTable", dbFailOnError

...or better yet...

Dim Db As DAO.Database
Set Db = CurrentDb
Db.Execute "DELETE * FROM MyTable", dbFailOnError
Debug.Print Db.RecordsAffected

What's Wrong With DoCmd.RunSQL?

When you call DoCmd.RunSQL, Microsoft Access is going to show a confirmation window before executing the query.  Here's an example from a DELETE query:

If the user clicks [Yes], the Delete query will execute and the records will be removed from the table.  If the user clicks [No], Access will raise a runtime error:

To avoid these prompts, a quick Google search will likely lead you to the most expedient solution: the DoCmd.SetWarnings method.  Simply turn the warnings off, execute the RunSQL method, then turn the warnings back on.

The problem with this approach is that it's all or nothing.  Turning off the warnings hides the confirmation messages and the action canceled errors.  But it also hides errors you may not be expecting.

For example, when you execute an INSERT INTO via the .RunSQL method, Access will check for the following potential issues:

• Type conversion failures
• Key violations
• Lock violations
• Validation rule violations

If you SetWarnings to False, then you will not see the above error message.  You would have no way of knowing that one of the records you tried to append failed due to a key violation.

Logic errors are very bad

Ignoring these kinds of warnings amounts to a logic error.  

Setting the Warnings to False will not interrupt the flow of the program.  It won't show extra messages to the user.  It won't cause the Access Runtime to crash without explanation.  

Instead, it will do something much worse.

It will silently create bad data in your system.  Like all logic errors, this could go undetected for months or even years.  And that is NOT a good situation to find yourself in one day.

The DAO .Execute Method

The DAO Execute method can be used in place of the RunSQL function.  As the name indicates, it executes an SQL action query (UPDATE, INSERT INTO, DELETE).  

You should ALWAYS include the dbFailOnError option when using the Execute method.  From the official documentation:

In a Microsoft Access workspace, if you provide a syntactically correct SQL statement and have the appropriate permissions, the Execute method won't fail — even if not a single row can be modified or deleted. Therefore, always use the dbFailOnError option when using the Execute method to run an update or delete query. This option generates a run-time error and rolls back all successful changes if any of the records affected are locked and can't be updated or deleted.

Leaving off the dbFailOnError option is the equivalent of turning off all warnings on the DoCmd.RunSQL method.

With dbFailOnError, this is an example of the error that would be raised if you did not properly handle duplicate values:

The CurrentDb Method

The Application.CurrentDb method returns a reference to the currently loaded database object in Microsoft Access (i.e., the front-end file).  The key thing to understand about this function is that it returns a new reference every time it is called.

Thus, you can't use the CurrentDb method for DAO Database properties that rely on methods being called on the same instance of the database object, such as RecordsAffected.  Case in point, calling the RecordsAffected method on the CurrentDb method will always return zero.

Thus, it's a good habit to always assign the return value of the CurrentDb method to a DAO.Database object variable.  Then use the object variable when calling the .Execute method.  

Here's a quick example:

'--== BAD ==--'
'There are five records in MyTable
CurrentDb.Execute "DELETE * FROM MyTable", dbFailOnError

?CurrentDb.RecordsAffected
 0 


'--== GOOD ==--'
Dim Db As DAO.Database
Set Db = CurrentDB

'There are five records in MyTable
Db.Execute "DELETE * FROM MyTable", dbFailOnError

?Db.RecordsAffected
 5

External references

Referenced articles

Image by Here and now, unfortunately, ends my journey on Pixabay from Pixabay