Avoid DoCmd.RunSQL in Microsoft Access
If you are just starting out writing VBA in Microsoft Access, you may be tempted to use DoCmd.RunSQL. Don't. There is a better way.
DoCmd.SetWarnings False DoCmd.RunSQL "DELETE * FROM MyTable" DoCmd.SetWarnings True
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 (
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
Image by Here and now, unfortunately, ends my journey on Pixabay from Pixabay