UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Closed TopicStart new topic
> Ways to call Action Queries    
 
   
kkeydel
post Apr 9 2005, 11:45 PM
Post#1



Posts: 297
Joined: 21-October 03
From: Seattle, WA


***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
Dim strSQL As String
strSQL = "UPDATE tblMyTable SET ID = MyNewID, Name = 'Mr. NewClient', Address = '203 Elm Street',Returning Customer = False, "
strSQL = strSQL & "Discount = False, FreeDelivery = True, PostalCode = 03801"
CurrentDB.Execute strSQL, dbFailOnError

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.
Thought I'd pass these along, and hope they give you some ideas about ways to disable notifications.
Kurt
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    17th December 2017 - 10:21 PM