Hack 17. Simulate Table Triggers
Incorporate the same functionality as SQL Server or Oracle in your Access application.
Access 2003 and earlier versions don't support table events. A trigger is a table event that you can fire on an insert, an edit, or a delete actiona valuable function. A useful example is to catch an edit before it completes and to store the original datathat is, store the original record somewhere else, such as in a backup table. This leaves you with a data audit trail. If for some reason the edited data is problematic, you can recall the original data.
This logic applies to deletes as well. Using triggers, you can hook into a delete and archive the data instead of just discarding it. In the case of inserts (such as new records being added to a table), data can be validated before being allowed into the table.
Unfortunately, Access doesn't let you do any of this directly from the point of view of the table itself. But you can do all of this when working through forms. Forms have plenty of events to hook into, and you can handle similar functionality as traditional triggers by working through forms instead of tables.
2.6.1. Setting Up an Audit Log
To demonstrate how all this works, let's add a new table to a database to mirror an existing data table and create an audit log of changes to the data table. We'll do this by using two additional fields: one to store the type of operation and one to store a timestamp. Figure 2-17 displays two tables: the data table (tblClients) and a table to store records from the first table just prior to them being edited or deleted (tblClientsAuditLog).
Here are a couple of points to consider:
2.6.2. Checking Out the Form Events
Now you can use a standard form to view, add, edit, and delete records from the data table. Figure 2-18 shows a typical form based on the tblClients table.
Figure 2-17. Using an audit log table to store records
Figure 2-18. Inserts, updates, and deletes, done with a form
Of course, there is some code behind this form. Two events are tapped: theBefore Update event and the Delete event. Before Update handles both inserts and updates, and Delete handles deletes. In particular, when an insert is made, the Before Update event validates the data (i.e., it checks to see if there is a last name). If the validation fails, the Cancel property is set to true, which causes the event to abort.
When an update (an edit) is made, the record receiving the change is written to the log table, prior to the change. This means the original data is kept intact. When a delete is made, the record that is to be deleted is also written to the log table, prior to the change.
2.6.3. The Code
Here is the code behind the form. The Action field in the log table receives one of two values: Update or Delete. The two event routines use a common function (build_sql):
Private Sub Form_BeforeUpdate(Cancel As Integer) On Error GoTo err_end Dim ssql As String Dim conn As ADODB.Connection Set conn = CurrentProject.Connection If NewRecord = False Then ssql = build_sql(ClientID, "Update") conn.Execute ssql conn.Close Set conn = Nothing Else If IsNull(ClientLastName) Or ClientLastName = "" Then MsgBox "Must provide name" Cancel = True End If End If Exit Sub err_end: MsgBox Err.Description End Sub Private Sub Form_Delete(Cancel As Integer) On Error GoTo err_end Dim ssql As String Dim conn As ADODB.Connection Set conn = CurrentProject.Connection ssql = build_sql(ClientID, "Delete") conn.Execute ssql Exit Sub err_end: MsgBox Err.Description End Sub Function build_sql(client_id As Long, operation As String) As String build_sql = "Insert Into tblClientsAuditLog Values (" build_sql = build_sql & ClientID & ", " build_sql = build_sql & "'" & _ DLookup("ClientFirstName", "tblClients", "ClientID=" & _ client_id) & "', " build_sql = build_sql & "'" & _ DLookup("ClientLastName", "tblClients", "ClientID=" & _ client_id) & "', " build_sql = build_sql & "'" & _ DLookup("ClientAddress1", "tblClients", "ClientID=" & _ client_id) & "', " build_sql = build_sql & "'" & _ DLookup("ClientState", "tblClients", "ClientID=" & _ client_id) & "', " build_sql = build_sql & "'" & _ DLookup("ClientCity", "tblClients", "ClientID=" & _ client_id) & "', " build_sql = build_sql & "'" & _ DLookup("ClientZip", "tblClients", "ClientID=" & _ client_id) & "', " build_sql = build_sql & "'" & _ DLookup("ClientPhone", "tblClients", "ClientID=" & _ client_id) & "', " build_sql = build_sql & "'" & operation & "', " build_sql = build_sql & "#" & Now() & "#)" End Function
2.6.4. Running the Code
The code runs when inserts, updates, and deletes are made using the form. No particular additional action, such as clicking a button, is required. The log table fills up with records as users do their thing. The log table keeps track of all the changes and even stores multiple changes per client. The build_sql function creates an Insert SQL statement. The statement will include either Update or Delete as one of the values being written, the difference being which routine called the function (and passed the word Update or Delete as an argument). The SQL string is handed back to the calling routine, from where the insert is run.
This certainly can be useful in the real world. For example, say a client moves (an address edit), gets married (a name edit), starts buying from your competitor (a delete!), and so on. Figure 2-19 shows the resulting log table with some records. Each record displays the action and the timestamp.
The methods used in this hack simulate what SQL Server, Oracle, and other database products provide in the way of triggers. Let's not allow the elite of the database world to believe Access isn't up to snuff!
Between the client table and the log table are multiple records, and there is a sequence to the records, thanks to the wonderful timestamp.
Figure 2-19. Records copied to the audit log table before being updated or deleted
2.6.5. Hacking the Hack
This hack was written with the advantage of knowing the table structure and field types. Therefore, I knew ahead of time to place single quotes around text values in the build_sql function. When adapting this hack, you will probably know ahead of time what type of data to expect, but if you don't, you can tap into the ADOX library to determine datatypes.
ADOX provides a way to read through each field in a table and determine its type (as well as other properties). The following is a basic routine to read through a single table and have the name and type returned for each field:
Sub get_fields() Dim cat As ADOX.Catalog Set cat = New ADOX.Catalog Dim fld As ADOX.Column cat.ActiveConnection = CurrentProject.Connection For Each fld In cat.Tables("tblClients").Columns Debug.Print fld.Name & " " & fld.Type Next Set cat = Nothing End Sub
Note that to use the ADOX library, you must set a reference. As with other references, go to the VB Editor and use the Tools References menu to display the References dialog box, shown in Figure 2-20. The library is named Microsoft ADO Ext. 2.7 for DDL and Security. Check the appropriate checkbox, and click OK to close the dialog.
The routine returns a numerical constant for each field type. For example, a value of 202 indicates a Text field type (although properly noted as a variable-width character field in ADOX lingo). Use the Object Browser to view the DataTypeEnum constants to see what the numbers represent. This collection of datatype constants is available once the reference to ADOX is set. Figure 2-21 shows the Object Browser zeroed in on the list of datatype constants. For any constant, you can see the numerical representation at the bottom of the Object Browser.
Figure 2-20. Setting a reference to ADOX
Figure 2-21. Reviewing datatype constants
Using Select Case or a set of If statements interspersed with the ADOX code in this section, you can write a routine that doesn't rely on knowing the field types ahead of time.