Table Level Events

 

Table Events

Table events were added the Microsoft Access with version 2010.   These events provide a database-wide method to perform procedures in one central location.  Therefore for many events you don’t need to put them in the form code which may have to be called as a procedure from several forms.

The only drawback that we see with the table level events is that you must code them using the Macro language.  Note that your database must be in .accdb format and not in .mdb format!

Here is an image of the table design screen for table events:

Access Table Level Triggers

Here is the event macro for a little audit trail on the Meter Value field:

before update change delete event

Access monitors when the Meter_Value field has changed as part of the cascading events in the system.  Once the value changes the database macro sets up the audit fields with the values needed.  The source of the data for each of these fields varies and includes fixed text values, such as the table name “M_Equip_Meter_Reads”, a special field value which is the field value before it gets updated ( [Old] ), also a special user define function which determines the current user on the computer.  Here is the function code:

I tried putting Environ function directly in the macro but it didn’t work.

Here is the table M_Equip_Meter_Reads that we are going to update:

event macros for microsoft access

When we change a meter value in the table it generates an audit record in the audit table showing the original (old) value and the new value:

after update before update event

 We have recently create an example and tutorial on how to create an audit trail in Access Forms.   See out forms based audit trail.

Form Triggers
Table Triggers
Form Flags
Table Triggers
Record Flags
Record Events

Microsoft Office:
MS Access 2003
Access 2007
Access 2010
Access 2013
Access 2016

 

Microsoft Office VBA, MS Access 2003, 2007, 2010, 2013, 2016