Update Query In MS Access – A Little Trick Using The Access VBA Immediate Window

Update Query In MS Access – A Little Trick Using The Access VBA Immediate Window

Here is one my free weekly tips (No. 86) for you that I wanted to share today because one particular client recently asked how you can disable warnings when using an Update query in MS Access?

Within the application, you can in fact switch off the warning prompts when running one of the four ACTION queries which are used for many different data processing tasks including how to archive data by combining two or more types of queries.

These options can be found in the Tools, Options section to versions 2003 (or earlier) or via the back-stage section under the Options, Client Settings section for later versions. There are three ‘Confirm‘ options that can be set including the ‘Action Queries‘.

update query in ms accessHowever, here’s a little trick; my free weekly tip for you which can be used instead and as a manual override thus having to not worry about switching on and off this option - as you shouldn’t disable them!

Update Query In MS Access – A Little Trick Using The Access VBA Immediate Window

  1. In your opened database, switch to the Visual Basic Editor view. You can use the quick short-cut key: ALT + F11 which toggles the two views.
  2. Now open the Immediate Window view again using the quick short-cut key: CTRL + G which displays a pop-up or window pane view usually to the bottom area of the VBE window.
  3. Type the following VBA command and SQL statement (an example only):
    CurrentDB.Execute “UPDATE MainSuppliers SET Fax = ‘N/A’ WHERE (Fax Is Null);”
    and then press the Enter key to run.

The above example, will update a field called ‘Fax‘ with the value “N/A” if this field was empty (as a Null value) to the table called ‘MainSuppliers‘.

No prompt to confirm the changes will appear no matter what was set in the current database application’s environment and the changes are immediately actioned. 

Of course you will need to know some SQL code but remember, you can always use an  Update query in MS Access to create this type of code and copy it to the Immediate Window if you are not too familiar with the SQL language.

Want to learn more about queries? I have two eBooks which takes you from the beginning to the more advanced including Action queries plus other eBooks on offer. They all come with a money back guarantee and if you by the 6 eBook bundle, this is a further great saving here too! Go to the eBooks link on the menu bar above now. 

This entry was posted in MS Access, Queries, Utilities, VBA and tagged , , , , . Bookmark the permalink.

3 Responses to Update Query In MS Access – A Little Trick Using The Access VBA Immediate Window

  1. Ben says:

    Yes, you are correct. The reason for using the Immediate Window is aimed more for the developer and database designer within Access which can can be a better way for testing and debugging data processing at the same time.Not so much the programmer but the general database administrator may have access to the VBA GUI but have little knowledge of VBA code can use this technique to carry out some simple data updates and avoid having to unlock (if protected) the Access environment or write specific routines or functions.When distributing your Access database to end-users, you should either use a macro or VBA code to wrap around and switch off and then back on the set warnings for any action query.There are several ways to work with this type of task.

  2. Lugh says:

    If you’re going to go to all the trouble of opening the VBA window, why not just use DoCmd.SetWarnings to turn off the warnings, run the query, then turn warnings on again? That’s what I do with 90% of my update queries.

  3. Ben Beitler says:

    As mentioned, this is weekly tip number 86.
    What about the previous 85 weeks?
    You will need to subscribe to my FREE weekly tips service (located at the top right hand side of this page).
    You never know, there may be just something new and useful for you to learn even if you have been using Access for a while now.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Confirm you are human.