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 DoCmd.OpenQuery.

Set warnings temporarily to false

If you DoCmd.RunSQL a warning message appears.

To prevent this turn warnings off before doing RunSQL: DoCmd.SetWarnings False

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.

Sub UpdateToOrder()
    On Error GoTo HandleError
    DoCmd.SetWarnings False
    DoCmd.RunSQL SQLStatement:="UPDATE SET Status='Order'" & _ 
                    "FROM Categories WHERE Quantity < 5;"
HandleExit:
    DoCmd.SetWarnings True
    Exit Sub
HandleError:
    MsgBox Err.Description
    Resume HandleExit
End Sub

UseTransaction argument

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.

Alternative: CurrentDb.Execute

The 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.