Getting Ready for Microsoft Access 2010Access 2010 Data Macros |
 |
MS Access Data Macro Overview
MS Access 2010 Data Macros are macros that are triggered when a record is added, changed, or deleted in a table. What makes a Data Macro different than a normal macro, or VB procedure for that matter, is that it is tied to the table itself. A normal macro (now called a UI Macros) can only be called from a form event, another UI Macro, a report event, or VB code. Data Macros are stored with the table itself, so you won't see a separate macro object like you do for UI Macros in prior versions of Access. And the Data Macro logic works only with local tables, not linked tables (there are ways around this by placing Data Macro logic in the table in it's native database, the logic will fire even when an update is being initiated by a linked database).
Simplify Your Databases, Improve Data Quality
So how can Data Macros help you? In three major ways. First, they can vastly simplify your current database. Instead of adding an "AfterInsert" event to each form where a user can create a new Order record to send out an email notification, you can create a single Data Macro that is tied to the Orders table. That data macro can send the email notification, and it will not matter from which form in your database, or even an append query for that matter, that the record creation was initiated from.
Second, if you plan on using the new Access Services for SharePoint to publish your databases to the web in Access 2010, Data Macros are supported on SharePoint. In fact, this is one of the essential ways to introduce programming logic on SharePoint.
Finally, you can increase your control over maintaining data quality in your Access database. Imagine you want to make sure that any time an Order status is changed from "Quote" to "Complete" an audit record is created, and your accounting tables are updated to record a completed order. If your users are able to access tables directly, or you allow users to run queries in your database, you may find that your intended process is circumvented. While it may be ideal to eliminate access to tables and queries and restrict users to a controlled process, the reality is that isn't always possible for various reasons. Adding Access 2010 Data Macros to the table to perform actions ensures that they will occur even if the user makes a change to a record outside of the forms you create.
Data Macro Events and Potential Uses
Microsoft has created a short video that demonstrates how to create a data macro:
MS Access 2010 Data Macros are macros that are triggered when a record is added, changed, or deleted in a table. What makes a Data Macro different than a normal macro, or VB procedure for that matter, is that it is tied to the table itself. A normal macro (now called a UI Macros) can only be called from a form event, another UI Macro, a report event, or VB code. Data Macros are stored with the table itself, so you won't see a separate macro object like you do for UI Macros in prior versions of Access. And the Data Macro logic works only with local tables, not linked tables (there are ways around this by placing Data Macro logic in the table in it's native database, the logic will fire even when an update is being initiated by a linked database).
Simplify Your Databases, Improve Data Quality
So how can Data Macros help you? In three major ways. First, they can vastly simplify your current database. Instead of adding an "AfterInsert" event to each form where a user can create a new Order record to send out an email notification, you can create a single Data Macro that is tied to the Orders table. That data macro can send the email notification, and it will not matter from which form in your database, or even an append query for that matter, that the record creation was initiated from.
Second, if you plan on using the new Access Services for SharePoint to publish your databases to the web in Access 2010, Data Macros are supported on SharePoint. In fact, this is one of the essential ways to introduce programming logic on SharePoint.
Finally, you can increase your control over maintaining data quality in your Access database. Imagine you want to make sure that any time an Order status is changed from "Quote" to "Complete" an audit record is created, and your accounting tables are updated to record a completed order. If your users are able to access tables directly, or you allow users to run queries in your database, you may find that your intended process is circumvented. While it may be ideal to eliminate access to tables and queries and restrict users to a controlled process, the reality is that isn't always possible for various reasons. Adding Access 2010 Data Macros to the table to perform actions ensures that they will occur even if the user makes a change to a record outside of the forms you create.
Data Macro Events and Potential Uses
| Data Macro Event | Uses and Notes |
| BeforeChange | Before a record is inserted or changed, you may want
to:
Note: Occurs for both inserts and changes. You have to use the IsInsert property to detect if it is a new record. |
| BeforeDelete | Before a record is deleted you may want to:
|
| AfterUpdate, AfterInsert, AfterDelete | After a record is changed, inserted, or deleted, you
can:
|
Microsoft has created a short video that demonstrates how to create a data macro: