Access Hacks

Hack #4. Optimize Data Changes

Avoid having to propagate data changes manually throughout related tables by establishing cascading updates and deletes.

The one constant you can count on is change. Why not plan for this eventuality in your applications? Take a real example: a customer changes her name. If you are in business long enough, this is something you will need to accommodate in your database.

Data changes need to be propagated in two ways. If the data isn't used as a table key or a foreign key, you need to change the data in all the places it resides in your tables. Hopefully, your data resides in only one place! A correctly modeled database holds a piece of data, such as a customer name, in just one place. If you do have such a piece of data in a few places, however, you presumably have a reason for doing this. Applications grow over time and often are handled by a succession of developers. It happens.

If you have database applications in which the same data is found all over the place, a brush up on data modeling is in order.

What about data that exists in table keys? This can be a frustrating change to propagate if many child tables use the data as the foreign key. That is, it will be frustrating unless you plan your relationships with cascading updates.

When creating relationships between tables, one option is to establish cascading updates. Figure 1-9 shows two tables of data. The tblCustomers table on top has customer information. The values in the key field, CustomerID, are the initials of the actual company names. In the lower tblInvoices table, the CustomerID serves as the foreign key.

Figure 1-9. Related tables

Figure 1-10 confirms the relationship between the tables. A line leads from the CustomerID field in tblCustomers to the CustomerID field in tblInvoices. The number 1 on the tblCustomers table side of the line indicates that tblCustomers is the parent table. The infinity symbol (∞) above where the line meets the tblInvoices table indicates that tblInvoices is the child table. This is a one-to-many relationship. The tblInvoices table has other relationships as well.

Figure 1-10. The Relationships window

The Edit Relationships dialog box, shown in Figure 1-11, is where you set relationships. To open the dialog in the Relationships window, double-click on a line that connects two tables. Note the Cascade Update Related Fields checkbox. When this box is checked, changing the value in the key field of the parent table automatically changes the values in the related field in the child table. This is a good thing! When a customer changes her name, all you have to do is change the value in the key. All the occurrences of the value in the child table automatically change to the new value.

In the example shown in Figure 1-9, if Best Equipment changes its name to Best Tools, the CustomerID value should be changed to BT. Making this change once in the tblCustomers table automatically updates all related records in the tblInvoices table.

Tip

Another option in the Edit Relationships dialog box (Figure 1-11) is to establish cascading deletes. The Cascade Delete Related Records setting ensures that when you delete a record in the parent table, all related records in child tables are also deleted. When this option isn't set, you have to delete the records in the child table first, and then you can delete the records in the parent table.

Figure 1-11. Selecting to use cascading updates and deletes

If the option to have cascading updates isn't set, you have to change each table's records separately. You can do that only if you remove the relationship first because Access doesn't let you change the values in the key field in either table if there are any related records. Trying to make an update in that way is quite messy.