DoCmd.RunSQL to run action queries
DoCmd.RunSQL is used to make changes to your database (action query).
These changes will usually be adding, deleting or updating records.
You can even use it to add or delete tables, but that is an uncommon use (data-definition query). You will not use
DoCmd.RunSQL to view records -
for that you use
Set warnings temporarily to false
DoCmd.RunSQL a warning message appears.
To prevent this turn warnings off before doing RunSQL:
After turning warnings off to run the SQL make absolutely sure that the warnings are in fact turned back on. Failure to do so may cause unwanted record manipulation somewhere else in the application. The only way to be absolutely sure that the warnings are turned back on is to include the statement in a dedicated exit point that will run even if an error occurs when the SQL is run. Below is basic error handling code inserted using Code VBA that does this.
On Error GoTo HandleError
DoCmd.RunSQL SQLStatement:="UPDATE SET Status='Order' FROM Categories WHERE Quantity < 5;"
An alternative is to save the SQL as a query (qryMyUpdateQuery) and run it using
DoCmd.OpenQuery "qryMyUpdateQuery". In this case also
UseTransaction:=True - which is the default - means that all changes to your recordset are written to a Cache/Buffer first, then written to the table after all changes have been cached.
That way the entire results from the Action query can be rolled back in the event of a failure on a single record.
By using a transaction the db engine may still be working on committing the transaction even though RunSQL is considered "Done" and your code proceeds to the next statement.
This may result in program logic issues if later statements rely on the transaction being finished.
CurrentDb.Execute best advantage is the ability to trap and handle errors in the SQL processing, something that you can't do with DoCmd.RunSQL.
To enable this, use the dbFailOnError option when executing the statement:
CurrentDb.Execute Query:="DELETE * FROM MyTable WHERE ID = 5", Options:=dbFailOnError + dbSeeChanges
dbFailOnError: Rolls back updates if an error occurs (Microsoft Access workspaces only).
dbSeeChanges: Generates a run-time error if another user is changing data you are editing (Microsoft Access workspaces only).
You don't get warnings as you would with
DoCmd.RunSQL - which may or may not be an advantage depending on your use.