Access Hacks

Hack #17. Simulate Table Triggers

Incorporate the same functionality as SQL Server or Oracle in your Access application.

Access 2003 and earlier versions don't support table events. A trigger is a table event that you can fire on an insert, an edit, or a delete action—a valuable function. A useful example is to catch an edit before it completes and to store the original data—that is, store the original record somewhere else, such as in a backup table. This leaves you with a data audit trail. If for some reason the edited data is problematic, you can recall the original data.

This logic applies to deletes as well. Using triggers, you can hook into a delete and archive the data instead of just discarding it. In the case of inserts (such as new records being added to a table), data can be validated before being allowed into the table.

Unfortunately, Access doesn't let you do any of this directly from the point of view of the table itself. But you can do all of this when working through forms. Forms have plenty of events to hook into, and you can handle similar functionality as traditional triggers by working through forms instead of tables.

Setting Up an Audit Log

To demonstrate how all this works, let's add a new table to a database to mirror an existing data table and create an audit log of changes to the data table. We'll do this by using two additional fields: one to store the type of operation and one to store a timestamp. Figure 2-17 displays two tables: the data table (tblClients) ...