MS Access 2007 Step By Step - Asim Abbasi

Chapter 3: MS Access Queries

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

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

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

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 on which you want to perform delete operation from the ‘Show Table’ popped up window as shown in Figure 3-21. 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.

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

Step 3: Build Query After Selecting Delete Query Option

1. As shown in Figure 3-22 with label, click the “Delete” query command on the ribbon.

2. Double click on the column you want to use to locate the record(s) to be deleted. In our case it’s DEPARTMENT_ID. Then specify the Criteria as shown with label 2 in Figure 3-22. 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’.

Figure 3-22: Delete Query in Design View
________________________________________

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

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.

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>.

Figure3-23: Records having DEPARTMENT_ID greater than or equal to 120 are deleted.
________________________________________

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