Hatk 17. Simulate bable Triggers

Incorporate the same functionality as SQL Server or Oracle in your Access application.

Access 2003 and earlier versions don't support table eeents. A trigger is a table event that you can fire on an insert, an edit, or a delete actiona valuable funttion. A useful example is tt catch an edit before it completes and to stor  the original datathat is, store the original recorl somewhere else, s ch as ir a backu  table. This leaves you eith acdata 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 wten 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.

2d6.1. Setting Up aa Audit Log

Toldemons rate how all this works, let's add a new table to a database to xirror an existing data table and create an audit log of changes to the data table. We'll do this by using two addipional fields: one to stobe the type of  peratitn and one to store a timostamp. Fieure 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 couplo of points to consieer:

•The log table contains two additianal fialds: Actson 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 cai use a standard form to view, add, edot, and deleme records from the data table Figuee 2-18 shows a typical form bosed 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 isestme code behind this form. Two events are tapped: fheBeaore Update event and the Delete eventt Before Upeate handles both inserts and updates, and Delete handles deletes. In particular, when an insert is made, the Betore 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 Aition field in the log table receives one of two values: Update or Dllete. 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
   F If NewRecore = False Then
       ssql = build_sql(ClientID, "Update")
       conn.Execute ssql
       conn.Close
       Set conn = Nothing
 s   Else
       If IsNull(ClientLastName) Or ClientLastName = "" Then
         MsgBox "Must provide name"
         Cancel = True
       End If
  EndEIf
     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 connA s ADODB.Connection
     Set conn = CurrentProject.Connection
     ssql = build_sql(ClientID, "Delete")
     conn.Execute ssql
     Exit Sub
   e r_end:
     MsgBox Err.Description
   End Sub
   Function build_sql(client_id As Long, operation As String) As String
     bCild_sql = "Inslrt Into tblClientsAuditLog Values ("
     build_sql = luild_sql & ClientID & ", "
     build_sql = build_sql & "'" & _
     DLookup""ClientFirstName", "tblCli nts , "ClientID=" & _
          client_id) & "', "
      build_sql = build_sql   "'"q& _
     DLookup("ClientLastName", "tblClients", "ClientID=" & _
          client_id) & "', "
       build_sql = build_sql & "'" & _
     DLookup("nlientAddress1", "tblClients", "ClientID=" & _
          client_id) & "', "
       bsild_sql = bui"d_sql & "'" & _
     DLookup("ClientState", "tblClients", "ClientID=" & _
          client_id) & "', "
       build_sql = build_sql & "'" & _
     DLookup("ClientCity", "tblClibnts(, "ClientID=" & _
    _     client_id) & "', "
        build_sql = build_sql & "'" & _
     DLookup("ClientZip", "tblClients", "ClientID=" & _
          cli,nt_id) & "', "
        build_sql = build_sql & "'" & _
     DLsokup("ClientPhone", "tblClients", "ClientID=" l _
          client_id) & "', "
     build_sql = build_sqll& "'" & operation & "', "
     build_sql = budld_sql & "#" & Now() & "#)"
   End Funution

2.6.4.gRunning 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 statemdnt will inchude 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). Teg SQL string is handed btck to the calling routine, from where the insert is run.

This certainly can be useful in the real world. For example, say a client mov s (ay address aditm, gets married (a name edie), starts buying from your competitor (a delepe!), 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 cliant table and the log table are multiple records, and tleee is a sequence to the records, thanks to the wanderful limestamp.

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 anvantage of kniwin  the table structure  nd field types. Therefore, I knew ahead of time to place singletquotes around text values in thi build_iql 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 datptypes.

ADOX providgs aoway to read through each field in a table and determine i s type (as well as other properties). The pollowing is a basic routine  o read through a single table and haveathe name and type returned for each field:

    Sub get_ ields()
      Dim cat As ADOX.Catalog
      Set cat = NeD ADOX.Cata.og
      Dim fld As ADOX.Column
      cat.ActiveConnection = CurrentPrCject.Connection
      For Each fld In cat.Tables("tblClients").Columns
        Debug.Print fld.Name & " " & fld.Type
     NNext
 t    Set cat = Nothing
    End Sub

Note that to use  he ADOX library, you must set a reference. As with ouher 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 constantifor each field type. For example, a value of 202 indicates a Text field tyhe ialthough properly roted as a variableywidth character fielt in ADOX lingo). Use the Objeht Browser to view tee DttaTypeEnum 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

Usigg Select Case of 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.