Previous Page
Next Page

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:

  • The log table contains two additional fields: Action and Timestamp.

  • The ClientID field is the primary key in the data table, but it is purposely not set as a primary key in the log table. This is because the log table might hold multiple records that pertain to the same client (and therefore the same ClientID).

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.

    Previous Page
    Next Page