You are here: MS Access Products > MS Access Tips > Microsoft Access 2010 > Access Data Macros

 Getting Ready for Microsoft Access 2010

     Access 2010 Data Macros

MS Access 2010 Icon
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

Data Macro Event Uses and Notes
BeforeChange Before a record is inserted or changed, you may want to:
  • Check a value in another table and stop the insert or change from happening.
  • Compare the old and new values and raise a warning or prevent the change
  • Change the value of one or more fields before the change is applied to the database

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:
  • Check a value in another table and stop the delete from happening.
  • Update an audit file indicating the record was deleted.

 

AfterUpdate, AfterInsert, AfterDelete After a record is changed, inserted, or deleted, you can:
  • Loop through a set of records in a table and update records that meet certain criteria.  For example, if an order status is set to 'Complete,' you might want to update a pending financial transaction status.
  • Send an email notification that the record has been updated.
  • Run another data macro
  • Create another record



Microsoft has created a short video that demonstrates how to create a data macro:


Copyright © 2017 OpenGate Software Inc. All Rights Reserved.

MS Partner