MS Access 2007 Step By Step - Asim Abbasi

Chapter 3: MS Access Queries

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

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 Query Scenario

Figure 3-15: Update Query Scenario
________________________________________

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

1. As shown in Figure 3-15, we basically 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

Figure 3-16: Query Design View available under “Create” command.
________________________________________

1. Once you have MS Access software up and running. Click the “Create” command as shown in Figure 3-3.

2. Then “Query Design” on the ribbon bar.

Most of the time we create queries in Design View and these 2 clicks at the appropriate spot and order will take you in the Design View of query builder.

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 as shown in Figure 3-17. 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.

Figure 3-17: List of tables under the Tables tab.
________________________________________

Step 3: Build Query after Selecting Update Query Option

1. As shown in the Figure 3-18 with label 1, click on the ‘Update Query’ command.

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 as shown in Figure 3-18 label 3.

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 let’s 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.

Figure 3-18: Update Query in Design View
________________________________________

Step 4: Run Update Query & Click Yes

Make sure you save your query first by clicking the Save icon as shown below.

Finally Run the query like you had ran any other query e.g. Select Query, Make-Table Query etc. by clicking the Run command (Exclamation ! icon) on the ribbon.

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.

Figure 3-19: ‘Human Resources’ changed to ‘HR’
________________________________________

Chapter 3: MS Access Queries

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

Share with others:

products

books

FREE VB, ASP Projects

MS Access 2007 Step By Step - Asim Abbasi