Access 2010 data macros (similar to triggers)

Last week we outlined improvements to the Access 2010 macro designer, parameter support, and IntelliSense. These improvements build upon Access 2007 macro improvements of macro sandbox, embedded macros, TempVars, debugging, and support for error handling.

The natural next step in macros is to provide a model for business rules. Data macros allow developers to attach logic to record/table events (similar to SQL triggers). This means you write logic in one place and all forms and code that updates those tables inherit that logic. Here are a few data macro scenarios you might find in a typical Donations Management database:

• Validate that a contributor doesn’t have outstanding donations before accepting a new donation.
• Keep a history of any changes to a donation record.
• Send a “Thank You” email when a contributor makes a donation greater than $1,000.
• Maintain a de-normalized total of all donations and last donation date on a contributors table to increase database performance.

A demo of creating a data macro is available here.

There were three primary design goals for data macros:

• Robust programming model that focuses on data centric scenarios.
• Reduce developer errors and increase code maintainability through centralized logic.
• Portable, secure and scalable for a server/service environment.

Table events fire on the BeforeChange (fires for inserts and updates), BeforeDelete, AfterInsert, AfterUpdate, and AfterDelete. You can create what we call Named macros that are associated with a table. They can be called from other data macros or UI macros. Named macros are essential for code reuse.

A key concept about data macros is they don’t have any UI. You can call Named data macros passing parameters from a UI macro but data macro can’t open a form or invoke a message box. They return errors that UI macros can catch and display to the user. These errors are also logged to the UsysApplicationLog table. This is a special table that keeps a history of data macro failures and other useful debug information you choose to write to it.

Before* data macro events

The BeforeChange and BeforeDelete events are designed to be fast, light-weight operations. You can look at old and new values in the current record and (after Beta1) compare them with a record in other tables using LookupRecord.  You will also be able to use SetField to alter data before the change is committed (but on the incoming row of data only, not on the row returned from LookupRecord).  They can prevent a record from being saved and raise custom error messages to the UI layer. Because they are designed to be fast and light-weight—you can’t iterate over a collection of records.  Also, since the BeforeChange event fires for both inserts and updates, there is an IsInsert property that returns True for inserts and False for updates, in case your logic needs to handle them differently.

After* data macro events

The AfterUpdate, AfterInsert, and AfterDelete events are designed for more extensive operations that require iteration and could take longer to run. Macros invoked from these events can look at and modify other records in the table or other tables. Old and updated values are available.

The After* events support the following program flow constructs:

• Comment
• If, Else, Else If
• Group (simply for logical grouping)
• Call other named macros passing parameters
• For Each Record (iterate over a recordset or SQL query)
• Lookup Record (returns a single record)
• Create Record (you won’t be able to call this inside of a For Each Record after beta 1)
• Edit Record

The After* events support the following actions:

• Set Field
• Cancel Record Change (breaks out of Create and Edit Record changes)
• Delete Record
• Send Email
• Run Data Macro
• Set Local Variable (scope to the current instance of a macro)
• On Error
• Raise Error
• Clear Macro Error
• Stop Macro
• Stop All Macros
• Exit For Each Record

When a data macro runs, for example an AfterUpdate event, it is possible that the current record could be modified, which would prompt another AfterUpdate event. Data macros are limited to 10 levels of recursion, but we've provided an Updated("FieldName")  function that returns True or False to help you trap any recursion manually by only reacting to changes in fields you care about (more details are below in the FAQ).

Finally, data macros cannot process multi-valued or attachment data-types.

FAQ

Link Tables. Data macros are not supported on link tables; however, you can associate logic on a backend ACCDB database for typical front-end/back-end applications.

SQL Server. Data macros are not upsized to SQL Server. You might see third party tools fill this gap.

VBA Support. You cannot call VBA from a data macro—this would not support the goal of portability to other platforms. However, you can call a named data macro from VBA--including one with parameters.

Transactions Support. Data macros do not support transactions this release. Each operation is atomic.

Backwards compatibility. We made changes to Access 2007 SP1 so that it can read but not write data in tables with data macros.

Power Tips

Where is the log table? You can get to the USysApplicationLog table through right click in the navigation pane. Choose Navigation Options. Check Show System Objects. It is available in the status bar and Backstage in beta 2.

Old and Updated syntax. The way to see if field in a record has changed in a BeforeChange, BeforeDelete, AfterUpdate, and AfterDelete event is to use the “Updated” function. Here is a simple example how you would log that inventory has changed:

If Old.FieldName

Products.AfterUpdate
If Updated(“InventoryCount”) is true
LogEvent =Products.InventoryCount – Old.InventoryCount & “ items were added to the inventory for product: ” & ProductID
End If

Updated the post with minor details on 8/27/2009.

Removed outdated video, provided link to current video 11/22/2010.