MS Access 2007 Step By Step - Asim Abbasi

Chapter 3: MS Access Queries

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

Append Query is used to insert new records at the end the table. Like we mentioned before, the table could be either Local or External (Oracle) Linked table. MS Access treats local and linked tables on the same lines. If you encounter any error running the Append Query against Linked (external Oracle) tables in MS Access, make sure you have appropriate privileges on those tables in Oracle database server.

Let’s have a look on the design and implementation of Append Query.

Creating Update Query Scenario

To better understand the design and implementation of MS Access Append Query, we are building a scenario. We have created a table having the following name.

Figure 3-24: Append Query Scenario
________________________________________

‘HR New Department To Append’

This table contains all the records we deleted previously (in the Delete Query section). We want to put those records back in the HR_DEPARTMENTS linked (external Oracle) table.

Step 1: Entering Into Queries Design View

Figure 3-25: 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-25.

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

After performing the step 1, select the table/query having records you would like to append to another table as shown in Figure 3-26. In our case its ‘HR New Department To Append’.

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

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

Step 3: Select ‘Append To Table Name’ After Selecting Append Query Option

Click on “Append” command on the ribbon as shown in Figure 3-27.

Figure 3-27: Append Query in Design View.
________________________________________

Once you select this option another window will popup asking you to locate the table where you want the records to be appended. In our case its HR_DEPARTMENTS. Click the <OK> button once done.

Step 4: Run Update Query & Click Yes

Double click on the fields you would like to include in your append query from ‘HR New Department To Append’. All these fields will appear at the bottom and MS Access will choose the most appropriate corresponding field from the ‘Append To’ table i.e. HR_DEPARTMENTS.

You can change the default selection made by MS Access by clicking on the field name in the ‘Append To’ row and it will get converted into drop-down menu with all the field names of the table which will be expecting new records.

Remember that data type of corresponding fields has to match otherwise there is a greater probability for your query to end up in an error e.g. you would not be able to append Date data-type column values in Currency data-type column.

Before running the query, click the save button and the final step would be to run your query. For this purpose, either click the Run icon on the ribbon bar as shown in Figure 3-28 with Exclamation (!) sign.

Figure 3-28: Location of Run command on the ribbon.
________________________________________

But just before appending records, MS Access will not only inform you about the number of records that will get appended but also give you the opportunity to make sure whether you want this operation to happen or not. If you are sure click <Yes> button otherwise <No>.

Step 5: Verify the Append Query Execution

1. You can verify the append process by first expanding the Tables object under “All Access Objects” group.

2. Double click on HR_DEPARTMENTS linked table. This will open the table as shown in Figure 3-29 with label 2.

Figure 3-29: All the deleted records appended back to HR_DEPARTMENTS table.
________________________________________

From Figure 3-29, it is clearly evident that all the records which we deleted in the Delete Query section of this chapter (i.e. where DEPARTMENT_ID was greater than or equal to 120) are now back in the table.

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