Microsoft Access 2010 Bible

Part II: Programming Microsoft Access

518

Note
Notice that you don’t follow the Delete method with Update. As soon as the Delete method executes, the
record is permanently removed from the recordset.

Deleting records using ADO doesn’t trigger the deletion confirmation dialog box. Generally speak-

ing, changes made to data with ADO code are not confirmed because confirmation would interrupt

the user’s workflow. This means that, as the developer, you’re responsible for making sure that

deletions are appropriate before proceeding. Once the record is deleted, there is no way to undo

the change to the underlying table.

Deleting related records in multiple tables

When you write ADO code to delete records, you need to be aware of the application’s relation-

ships. The table containing the record that you’re deleting may be participating in a one-to-many

relationship with another table.

Take a look at the relationships diagram (see Figure 13.20) for the tables used in the frmSales

example. tblSales has two dependent tables associated with it: tblSalesLineItems and

tblSalesPayments.

FIGURE 13.20

Examining the tables of a one-to-many relationship

Primary key Foreign key Enforce Referential Integrity

Cascade Update Related Fields

Cascade Delete Related Records

The Edit Relationships dialog box shows how the relationship is set up between tblSales and

tblSalesLineItems. The relationship type is a one-to-many (1:M) and referential integrity is

enforced. A one-to-many relationship means that each record in the parent table (tblSales) may

have one or more records in the child table (tblSalesLineItems). Each record in the parent

table must be unique — you can’t have two sales records with exactly the same InvoiceNumber,

SalesDate, and other information.