MS Access 2003 Step By Step - Asim Abbasi
CH1: Overview | CH2: MS Access Local & Linked Tables | CH3: MS Access Queries | CH4: MS Access Reports & Forms | CH5: MS Access Modules & Macros | CH6: MS Access Data Import/Export Feature | CH7: MS Access Built-In Functions
Chapter 3: MS Access Queries
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 Append Queries
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.
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
1. Once you have MS Access software up and running. Click on the Queries
object under the Objects group as shown in the screenshot above.
2. Then click the ‘New’ command above the 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 any other query
discussed earlier. The target is to enter into the Query Design View.
Step 2: Add the Table
After performing the step 1, select the table/query having records you
would like to append to another table. 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.
Step 3: Select ‘Append To Table Name’ After Selecting Append Query Option
Make sure you have selected the ‘Append Query’ option from the drop down menu as shown below.
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 on the tool bar to save
it first.
The final step would be to run your query. For this purpose, either
click the Query and then Run from the menu bar or click the Run icon on
the tool bar as shown in the adjacent screenshot with Exclamation (!)
sign.
But just before appending 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 clicking the Tables object
under the Objects group.
2. Double click on HR_DEPARTMENTS linked table. This will open the table
as shown in the slide above.
The slide shows screenshot of the table and it clearly depicts 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
CH1: Overview | CH2: MS Access Local & Linked Tables | CH3: MS Access Queries | CH4: MS Access Reports & Forms | CH5: MS Access Modules & Macros | CH6: MS Access Data Import/Export Feature | CH7: MS Access Built-In Functions
Share with others: