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
The Delete Query is used to delete records from the table in the MS
Access database. MS Access makes no difference in executing Delete Query
against Local Tables or Linked External (Oracle) Tables.
If you want to delete records from Oracle table based on some logic, you
need to write SQL code to have it done. On the other hand, MS Access
provides the complete visual environment and same goal can be achieved
via MS Access just by click and drag operation.
Let’s have a look on the design and implementation of Delete Query in MS
Access.
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
discussed earlier. The target is to enter into the Query Design View.
Step 2: Add the Table to Update
After performing the step 1, select the table on which you want to
perform delete operation 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 table.
As a memory refresher, 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: Build Query After Selecting Delete Query Option
1. As shown in the screenshot above, select the option Delete Query from
the drop down menu.
2. Double click on the column you want to use to locate the record to be
deleted. In our case it’s DEPARTMENT_ID. Then specify the Criteria as
shown in the screenshot above. In our case its >= 120 (i.e. greater than
and equal to 120)
3. Even if you are in Design View and you want to bring the ‘Show Table’
window just right click on the empty gray area next to table and from
the popup window select the option ‘Show Table’.
Let’s now interpret just by looking at the bottom section of Query
Design View as shown below.
Once you run this query, MS Access will delete all the records from
HR_DEPARTMENTS table where DEPARTMENT_ID is greater than and equal to
120. But just before deleting MS Access will not only inform you about
the number of records that will get deleted 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 4: Run Delete Query & Click Yes
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 below as well as on the slide above.
But before deleting MS Access will not only inform you about the number of records that will get deleted 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>.
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: