For an example of using transactions to roll back after an error, see: Archive: Move records to another table.For optional criteria, build the SQL statement as demonstrated in this Search database.For help with getting the quote marks right around text fields, see: Quotation marks within quotes.To get the SQL statement, you can mock up a query using sample values, and switch it to SQL View.Add Error Handling to trap any error triggered by dbFailOnError.The other difference is that Execute does not display the progress meter in the status bar (at the bottom of the Access window.) Further suggestions: MsgBox db.RecordsAffected & " record(s) were unpicked." StrSql = "UPDATE Table1 SET IsPicked = False WHERE IsPicked = True "
#Microsoft access update query code
The code typically looks like this example, which resets a Yes/No field to No for all records: Function UnpickAll() It is also much more flexible: you can build the SQL string from only those boxes where the user entered a value, instead of trying handle all the possible cases.
#Microsoft access update query portable
You end up with fewer saved queries in the Database window, and your code is more portable and reliable. It is possible to assign values to the parameters and execute the query, but it is just as easy to execute a string instead of a saved query. The ES is not available in the Execute context, so the code gives an error about "parameters expected." If you run that query directly from the Database Window or via RunSQL, the Expression Service (ES) in Access resolves those names and the query works. However, Execute is not as easy to use if the action query has parameters such as. You can also use a transaction and rollback on error. If something goes wrong, using dbFailOnError generates an error you can trap. MsgBox DBEngine(0)(0).RecordsAffected & " record(s) affected." If you do want to show the results, the next line is: The query runs without the dialogs, so SetWarnings is not needed. In a module, you can run an action query like this:ĭBEngine(0)(0).Execute "Query1", dbFailOnError The Execute method provides a much more powerful solution if you don't mind using code instead of a macro. That leaves you with no idea whether the action completed as expected, partially, or not at all. The SetWarnings action in your macro will suppress these dialogs. When you run an action query like this, Access pops up two dialogs: Using OpenQuery also works (just like double-clicking an action query on the Query tab of the Database window), but it is a little less clear what the macro is doing. In a macro or in code, you can use RunSQL to run an action query. This article recommends Execute in preference to RunSQL.
There are multiple ways to run the query through macros or code.
Last updated May 2009.Īction queries: suppressing dialogs, while knowing resultsĪction queries change your data: inserting, deleting, or updating records. Microsoft Access tips: Action queries - suppressing dialogs, while knowing results Microsoft Access Tips for Serious Users
0 kommentar(er)
