This page requires general cleanup in formatting or text to better fit the UA Wiki Guidelines

Related Content:
    RunSQL vs Execute

***For Access versions 2000 - 2003 (probably applies to 97 as well)***

While there are several methods for running action queries, they behave quite differently in Access and VBA.

The methods are:

The first three methods will present the user with a warning: "You are about to update n rows, Do you want to continue?" To disable this warning you can set the SetWarnings property:

DoCmd.SetWarnings False 'Disable the warnings while running this query

Don't forget to restore the warnings, or you'll lose a lot of functionality in Access (like being prompted to save after you create a query -- you won't have a choice -- Access will demand you provide a name for your query whether you want to save it or not):

DoCmd.SetWarnings True 'Restore the warnings for the rest of our database operations

1) Create and save a query and call it from code:

DoCmd.SetWarnings False DoCmd.OpenQuery "qryMyUpdateQuery" DoCmd.SetWarnings True

2) Create and save an Action query from within VBA code:

Dim qdf As QueryDef Set qdf = CurrentDB.CreateQueryDef("MyQuery","UPDATE tblMyTable SET Value1 = MyValue")

DoCmd.SetWarnings False qdf.Execute, dbFailOnError DoCmd.SetWarnings True

Note that you'll have to delete or destroy this query if you intend to use this procedure repeatedly -- because you'll get an error saying the query already exists each time you use the CreateQueryDef method.

3) Use the DoCmd.RunSQL command.

DoCmd.SetWarnings False DoCmd.RunSQL "Update tblMyTable SET Value1 = MyValue" DoCmd.SetWarnings True

4) The execute command of the CurrentDB:

CurrentDB.Execute "DELETE * FROM MyTable WHERE ID = 5", dbFailOnError

Note that this last method does not require you to disable warnings - they're not given.

Also, note the options specified after the Execute methods. They're listed in the online Help for these commands. The dbFailOnError option will cause an error message if anything goes wrong. Without it, if the query fails, you might not be notified, and the whole procedure that depends upon this query may not produce the intended results.

Finally...
Enclose your SQL statements in string variables!
There is one additional way of using these action queries that will save you hours of time and many headaches, to boot. Enclose your SQL strings in a string variable ...

Here's why...

When you run an SQL command, if the Database Engine encounters an error, you'll be told what that error is. You won't necessarily be told, however, where in your SQL statement that error occured. If only you could find out exactly which field is misspelled, or which right paren ) is the extra one. Well, you can. Access gives you a great tool for doing just that. By entering SQL text into the query designer in Access, a syntax check will usually highlight the location within your SQL statement where the error is, where an invalid field name exists, or if you omitted or added an extra ] or ).

By enclosing your SQL statements in a string variable, you can print the value of this variable (your SQL statement) paste it into the Access query designer, and have Access indicate the location of the error for you.

Looking at method Number 4 above, we would create an action query (Update query) like so:

CODE

Now, we can set a breakpoint at the CurrentDB.Execute line, and through our Immediate (Debug) Window, print the value of our strSQL string variable. We can then copy this value, paste it into a new query in the Access Query Designer, and see if and where it throws an error.

Hopefully these examples give you some ideas about ways to disable notifications.

This page was originally ported from the UtterAccess Forums. It is based heavily or in part on a post by kkeydel.
Original Post: Ways to call Action Queries