Updating Records in MS Access - Updating data using the Update action query
(Page 3 of 4 )
MS Access provides action queries to modify data. Action queries are used to make changes to a large number of records with a single click. These queries can be created using the query designer tool. Creating an update query using the designer is really very simple. First create a select query as shown.
Right click the border between the design and grid to pop open the window with several menu items as shown. Click on the menu item labeled Update Query.
The grid changes somewhat, allowing to choose which column or columns you need in the table, and showing you where you can type in the changes as shown. Updating the query requires the table, the criterion for which the changes have to be made and the changes themselves.
You may also look at the SQL View of the query which is shown in the next paragraph.
UPDATE TestProducts
SET TestProducts.ProductName = "Milk Chocolate",
TestProducts.UnitsInStock = 500
WHERE (((TestProducts.ProdID)=48));
The value that exists before the Update Query is executed is shown in the next picture .
After making entries, when you try to close the query editor, you will be asked whether you want to save and you may give a name to the query.
Queries in the main screen of the access file can be easily identified as they are shown with different types of icons. You may run the Update Query you created by double clicking its icon. This immediately brings up the message window, letting you know about the changes that are going to be made.
If you click on the Show Help button you will reveal the following text.
The query you are about to run is an update query. Unlike most
queries that display data in Datasheet view, an update query
updates one or more fields in a specific table in a single
operation. If your intention is to perform a bulk update
operation, click Yes. To avoid being prompted when you run such
queries, on the Tools menu, click Options, then
click the Edit/Find tab. Under Confirm, clear the Action
queries check box. To exit without running the query, click No.
To find out more about what this query does, click No to close
this message, then open the query in Design view (in the
Database window, select the query, and click Design). Then do
one or more of the following: To see which fields will be
updated by the query, see the names in the Field row in the
design grid. The Table row shows the name of the table that
contains the field. To see how the values will be changed,
look at the expression in the Update To row. To see the
criteria, if any, for selecting records for the bulk operation,
see the Criteria and the or row, and any additional rows below
the or row that display selection criteria. If these rows are
empty, all rows in the table listed in the Table row will be
updated. To preview the records that will be updated, click View
on the toolbar. The data as it exists before the update operation
will be displayed in Datasheet view.
When you click on the Yes button, you will get another message from the program letting you know how many rows are going to be modified as shown.
Now when you click on the Yes button one more time the changes will be made to the table as shown in the next picture. As you can see, you can modify one or more columns at the same time. This was also seen in the SQL View earlier.
Next: Updating the column using ADO >>
More Microsoft Access Articles
More By Jayaram Krishnaswamy
