Access 2010 provides various types of data macro actions that you can attach to table events to automate your application. With data macros, you can:
Verify that an invoice is balanced with the invoice detail line items before saving the record.
Mark an employee as inactive after you create a termination record.
Prevent a record from being saved if it violates a composite key index.
Send an email to the database administrator if an unexpected error occurs within the application.
Prevent any data from being edited, added, or deleted from a table.
Email individual employee schedule details to all employees.
Create new schedule records based on the previous week’s schedule or a labor plan template.
Delete all schedule records within a specific time frame.
As you’ll learn in Chapter 20, and Chapter 21, Access 2010 supports user interface macros to control application flow in your forms and reports and to respond to user actions. You can also utilize user interface macros, as well as Visual Basic, to enforce complex business logic that might not be covered by table relationships, unique properties, validation rules, and required properties. The potential problem with using user interface macros and Visual Basic to enforce complex business logic, however, is that you don’t always have complete control over how users interact with the data in your tables. For example, users can add, update, and delete data through queries. Users can also link to the tables in one Access database file from another Access file and add, update, and delete data from that database. In both of these examples, users can bypass your complex business logic rules normally stored in user interface macros and Visual Basic code. (You’ll learn all about queries in Part III, and linking Access files in Chapter 8.)
Access 2010 introduces a new type of macro, called data macros, to provide a place for Access developers to centralize all their business logic and rules. Data macros are similar to triggers in Microsoft SQL Server because they allow you to attach business logic directly to table events. However, unlike triggers in SQL Server, data macros are not performed within a transactional context—each operation is separate. Data macros respond to data modifications, so no matter how users edit data in the database, Access enforces those rules. This means you can write business logic in one place, and all the data entry forms and Visual Basic code that update those tables inherit that logic from the data layer. Once you create a data macro for a table event, Access runs the data macro no matter how you change the data.
Data macros in Access 2010 can be used in both client and web databases. In fact, the events, actions, and properties that you can use in data macros are identical between client and web databases, so you can easily reuse data macros that you defined in a client database for use in a web database. The Access database engine enforces data macros when you work with a client database. When you publish a web database to a Microsoft SharePoint 2010 server, Access Services enforces data macros on the server through the use of SharePoint Workflow actions.
When you’re adding data macro logic to your client and web tables, you need to be aware of these important issues:
You cannot call Visual Basic code from a data macro; however, you can call a named data macro from Visual Basic and pass in parameters.
If you upsize your database to SQL Server, Access cannot upsize the data macro logic.
Data macros cannot process data from multi-valued fields or attachment fields.
Access 2007 Service Pack 1 can read but not write data in linked Access 2010 tables that include data macros because the Access 2007 data engine can’t execute them.
You cannot create data macros on linked tables; you must create the data macro logic in the ACCDB that contains the local tables.