Update Query in Microsoft Access
The Access Update Query is a DML (data manipulation language) command to use to modify the data in your
relational database tables.
The Access Update Query syntax is:
Update TableName Set ColumnName=somevalue Where somecriteria;
Note: The ending semicolon is a required component of all SQL statements.
Now the update query with real column and table names:
Update M_Employees Set Emp_Name="Cheryl Crow", Marital_Status="Available" Where Emp_Name="Cheryl Armstrong";
If you leave the Where Clause out of the SQL update query then all of the records will get updated.
Instructions for creating a MS Access update query (paraphrased from Microsoft Access Instructions):
Create a query with the table(s) containing the data you would like to update,
in our example it is the Employee table. See the image below to view the
query design grid screen.
-
In the Database window, click Queries and then click New on the
Database window toolbar.
-
In the New Query dialog box, click Design View, and then click OK.
3. In the Show Table dialog box, click the tab that lists the tables or
queries whose data you want to work with.
-
Double-click the employee table to add to the query, and
then click Close.
-
Add fields (salary) to the Field row in the design grid, and if you want, specify
criteria and a sort order.
-
To view the query's results, click View on the toolbar.
-
In query Design view, click the arrow next to Query Type on the toolbar,
and then click Update Query.
-
Drag from the Salary field to the query design grid the fields you want to update
or you want to specify criteria for.
-
In the Criteria cell, specify the criteria if necessary.
-
In the Update To cell for the fields you want to update, type the expression
([salary] + 100.00) or
value you want to use to change the fields, as shown in the following
illustration.
-
To see a list of the records that will be updated, click View on the
toolbar. This list won't show the new values. To return to query Design view,
click View on the toolbar again. Make any changes you want in Design view.
-
Click Run on the toolbar to update the records.
More Access Update Query Information & Examples:
| Update M_Employees as A set
A.Salary = (Select Salary * 1.1 from M_Employees Where
M_Employees.Employee_ID = A.Employee_ID) ... |
| RunSQL ("Update Tasks set
Task_Complete=True " & _ " Where Tasks.Task_ID in " & _ " (Select Task_ID
from Action_Items where Task_ID=" & Me.Task_ID & _ ... |
You can also use update query statements in VBA code. The update query is great
for creating audit trails when data changes on a form - in combination with the afterupdate event. Try the Access update query in visual basic using the
docmd.runsql
command.
