MS Access 2003 Step By Step - Asim Abbasi

Chapter 3: MS Access Queries

Page #: 1 | 2 | 3 | 4 | 5 | 6 | 7

Update query is used to update record(s) inside the table of a MS Access database. The table to be updated could be either Local MS Access table or Linked MS Access table, in either case upon successful execution of Update Query; the information gets updated inside the table.

Using this query we can update Oracle table merely with a click and drag operation with little to no knowledge about SQL syntax.

We will use update query whenever we want to update/change already existing information in the table.

Creating Update Queries

Before learning how we implement the Update Query lets first define a real world scenario.

1. As shown in the screenshot above, we want to change the ‘Human Resources’ to just ‘HR’ where the DEPARTMENT_ID is 40.

Now we will learn in a step by step manner, how to achieve this goal using the Update Query.

Step 1: Entering Into Queries Design View

1. Once you have MS Access software up and running. Click the Queries object under the Objects group as shown in the screenshot above.

2. Then click the ‘New’ command above objects group.

3. Select the Design View option from the popped up window.

4. Finally click the <OK> button.

The first four clicks are exactly the same as in case of Select query or any other query as discussed. The target is to enter into the Query Design View.

Step 2: Add the Table to Update

After performing step 1, select the table you want to update from the ‘Show Table’ popped up window. Then click the <Add> button. Once done click the <Close> button. In our case it’s HR_DEPARTMENTS.

As a memory refresher, HR_DEPARTMENTS table is basically the Oracle table linked via ODBC DSN in the MS Access environment. Once the external Oracle table gets linked to MS Access then it really doesn’t matter whether it’s a local table or linked table, Update Query works equally good for both. If you are unable to update any of Oracle table make sure you have appropriate privileges on those tables in Oracle Database Server.

Step 3: Build Query After Selecting Update Query Option

1. As shown in the screenshot above, from the drop down menu select the ‘Update Query’ option.

2. Select the DEPARTMENT_NAME field by double clicking on it. Once you double click it, it will automatically appear on the bottom row where it says ‘Field’. The ‘Field’ row contains all the fields including in your query.

3. Similarly, double click the DEPARTMENT_ID field.

4. Where it says ‘Update to’ write HR under DEPARTMENT_NAME field.

5. And where it says ‘Criteria’ write 40 under DEPARTMENT_ID field.

Now lets try to read our goal we mentioned earlier, we want to update ‘Human Resources’ DEPARTMENT_NAME to ‘HR’ where DEPARTMENT_ID is 40. As shown below, we are saying to MS Access Update Query that ‘Update To’ HR under DEPARTMENT_NAME where DEPARTMENT_ID is 40.

Step 4: Run Update Query & Click Yes

Make sure you save your query first by clicking the Save icon on the tool bar as shown below or by clicking File and then Save.

Finally Run the query like you have ran any other query e.g. Select Query, Make-Table Query etc. by clicking the Run icon on the tool bar as shown below or via Menu bar by clicking Query and then Run.

After you press the Run icon on the tool bar and just before implementing the update query, MS Access will not only inform you about the number of records that will get updated but it will also ask you whether you are sure or not about implementing this update. If you don’t want to implement the update click the <No> button otherwise click the <Yes> button.

Chapter 3: MS Access Queries

Page #: 1 | 2 | 3 | 4 | 5 | 6 | 7

Share with others:

products

books

FREE VB, ASP Projects

MS Access 2003 Step By Step - Asim Abbasi