Category: VBA Klassen

Audit Trail – oder wie protokoliert man Datenänderungen

By , 11. Mai 2015

Hallo,
das Thema heute handelt von der Protokollierung von Datensatzänderungen auf Formularbasis, der Fachbegriff dazu lautet Audit Trail. Datensatzänderungen in einer Abfrage oder direkt in einer Tabelle können in Access Jet nicht überwacht werden. Deshalb ist dem Anwender der Zugriff auf beide zu verwehren mit verschiedenen Techniken.

1. Definition und Motivation top

Laut Gabler Wirtschaftslexikon dient ein Audit Trail einerseits der „Prüfung bzw. Überwachung der Handelnden und ihrer Aktivitäten, anderseits kann dadurch im Schadensfall bzw. zwecks Aufdeckung doloser Handlungen die Handlungsfolge rückverfolgt und auch eine System- bzw. Datenwiederherstellung vereinfacht werden. So sind gezielte Prüf- und Korrekturmöglichkeiten von fehlerhaften bzw. inkriminierten Benutzereingaben möglich“

Für uns in der EDV heruntergebrochen dient ein Audit Trail zur Prüfung bzw. Überwachung von Dateneingaben, -Änderungen bzw. -Löschungen um z.b. im Fall eines Schadens die Datenintegrität wieder herzustellen oder auch bei juristischen Auseinandersetzungen Beweise für Fehleingaben (oder eben nicht) zu besitzen.

Die Motivation zur Durchführung des Audit Trails können sehr unterschiedlich sein. Z.B. zur Überwachung einzelner Arbeitsplätze wo gehäuft Fehleingaben im EDV-System vorgefallen sind, oder auch um Datenanalyse durchführen zu können, und nicht zuletzt auch zur Einhaltung der Datenintegrität.
Aber bei aller Liebe zur Technik, ein Audit Trail darf kein Selbstzweck sein, denn es bindet Ressourcen im Unternehmen und kostet somit reales Geld – eventuelle rechtliche Fragen jetzt mal ausgeschlossen.

In diesem Beitrag möchte ich aber nur auf die Technik dazu eingehen, denn das interessiert wohl die meisten Leser.
Fangen wir an beim Formular. Die zu überwachenden Steuerelemente sind im Wesentlichen TextBox, ComboBox, ListBox, CheckBox, Optionsfeld und Umschaltfläche. Um diese zu Überwachen müssen die Steuerelemente an eine Datenquelle, z.B. Abfrage oder Tabelle, gebunden sein.

2. Verwendete Formular-Ereignisse top

BeforeUpdate: Dieses Ereignis tritt ein, wenn nach einer Datenänderung der Datensatz gewechselt wird, oder wenn z.B. durch klicken der Focus in ein Unterformular gesetzt wird. Zur Datensatzänderung zählt hier neben der Änderung auch die Neuanlage. Beide unterscheidet man wie folgt:

Private Sub form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
MsgBox "NeuerDatensatz"
Else
MsgBox "Datensatzänderung"
End If
End Sub

Delete: Dieses Ereignis tritt ein, wenn ein Datensatz gelöscht wurde. Dieses Ereignis verwenden wir um die Löschung zu protokollieren:

Private Sub form_Delete(Cancel As Integer)
'Audit Trail Delete
End Sub

AfterDelConfirm: Dieses Ereignis tritt ein, wenn – bei aktivierter Option „Datensatzänderungen“ – ein Datensatz gelöscht wird und bei dem folgenden Bestätigungsdialog entweder der Löschung zugestimmt wird oder diese abgebrochen wird. Diese Option befindet sich unter Access-Optionen/Clienteinstellungen/Bestätigen. Das Event liefert einen Status, mithilfe man unterscheidet ob der Löschvorgang abgebrochen wurde:

Private Sub form_AfterDelConfirm(Status As Integer)
If Status <> acDeleteOK Then MsgBox "Datensatz wurde gelöscht"
End Sub

Die Aktivierungsreihenfolge der Ereignisse ist: Delete, AfterDelConfirm.
Doch wozu benötigen wir AfterDelConfirm wenn im Ereignis Delete bereits das Löschen protokolliert wurde? Nun, es könnte sein, dass der Nutzer im Bestätigungsdialog das Löschen rückgängig macht, dann müssen wir dafür sorgen dass die Log-Datei bereinigt wird, denn es steht ja das Löschen bereits drin.

Das waren die Ereignisse (Events), welche wir für das Audit Trail benötigen. Was im wesentlichen noch fehlt ist eine Funktion die die Log schreibt, und eine Funktion die ggf. die Log bereinigt.
Jetzt gehen wir aber nicht hin und „müllen“ unsere Formularmodule mit redundantem Code zu, sondern machen das ganz elegant per Klassenmodul, wo wir auch die Events der Formulare abbilden werden.
Der interessierte Leser weiss ja, dass ich auf OOP abfahre und alles möglichst Objektorientiert programmiere.

3. Implementierung im Formular top

Zuerst benötigen wir eine Referenzierung auf die Klasse um davon ein Objekt ableiten zu können:

Dim myAudit As clsAuditTrail
Private Sub Form_Load()
Set myAudit = New clsAuditTrail
Set myAudit.FormObj = Me
End Sub
Private Sub Form_Unload(Cancel As Integer)
Set myAudit = Nothing
End Sub

Es wird ein Objekt erstellt „myAudit“, und die Eigenschaft „FormObj“ mit dem Formular-Objekt des aktuellen Formulares belegt.
Verwenden Sie Unterformulare? Dann können Sie auch dort das Audit Trail einsetzen, indem sie das Formular öffnen und dort obigen Code hinein kopieren. Nicht vergessen die Marke für die zu überwachenden Datenfelder zu setzen. Wenn Sie allerdings innerhalb des Unterformular-Steuerelementes eine Abfrage oder Tabelle einsetzen, dann können Sie keine Änderungen protokollieren da weder Abfragen noch Tabellen Eventhandler besitzen.

4. Funktionsweise der Klasse top

Werfen wir mal einen kurzen Blick in die Klasse.
Um nun innerhalb der Klasse auf die oben genannten Formular-Ereignisse reagieren zu können, muss zuerst das Form-Object übernommen und in einer Variablen gespeichert werden, und danach die Eventhandler definiert werden:

Public Property Set FormObj(ByRef FRM_ As Access.Form)
    Set m_frm = FRM_
    m_IdFieldName = getIDField(FRM_)
    m_frm.BeforeUpdate = "[Event Procedure]"
    m_frm.OnDelete = "[Event Procedure]"
    m_frm.AfterDelConfirm = "[Event Procedure]"
End Property

In der Variable m_IdFieldName wird der Name des Formulars gespeichert, da dieser an späterer Stelle benötigt wird. Die Eventhandler werden auf einfachste Weise definiert, indem man dem möglichen Ereignis den Textstring „[Event Procedure]“ zuweist. Ohne diese Zuweisung werden keine Events gefeuert.
Die Programmierung der Formular-Ereignisse in der Klasse geschieht auf identische Weise wie im Formular, nur haben wir hier nicht das Form-Object sondern seinen Stellvertreter m_frm.
Ein Beispiel:

Private Sub m_frm_AfterDelConfirm(Status As Integer)
    If Status <> acDeleteOK Then Call DataRedoDelete
End Sub

Verwendet man innerhalb der Formulare ebenfalls die gleichen Eventhandler, so ist zu bedenken dass immer zuerst der Eventhandler des Formulars feuert, und dann erst der in der Klasse. Das kann funktionierten, kann aber auch zu problemen führen.

Kommen wir nun zum eigentlichen Protokollieren. Dieser Algorithmus ist eigentlich ziemlich einfach.
Man prüft alle Steuerelemente des Formulars in einer Schleife ob die Eigenschaft Value <> der Eigenschaft OldValue ist. Trift dies zu hat man eine Datensatzänderung. Da nur Steuerelemente die Eigenschaft Value haben, die auch einen Steuerelementinhalt besitzen, ist es ratsam einfach alle zu überwachenden Steuerelemente mit einer Marke auszustatten. Ich habe im Beispiel als Marke den Text Audit verwendet.

Da man nicht nur die Änderung sondern auch Wer Wann und Wo geändert hat, benötigen wir dazu noch ein paar Werte. Die Vaiable lngInsertedID wird benötigt, um beim Löschen die ID des eingefügten Datensatzes in der Log zu behalten. Wird das Löschen nämlich abgebrochen werden wir anhand dieser Variable den zu entfernenden Datensatz aus der Log identifizieren.

Private Sub DataChanges()
    Dim CTL As Control
    Dim lngInsertedID As Long

    dtmCurrentDateTime = Now()
    strUserName = Environ("USERNAME")
    Select Case UserAction
    Case "EDIT"
        For Each CTL In m_frm.Controls
            If CTL.Tag = "Audit" Then
                If Nz(CTL.Value) <> Nz(CTL.OldValue) Then
                    WriteEditLog CTL
                End If
            End If
        Next CTL
    Case Else
        lngInsertedID = WriteNewDeleteLog
        If UserAction = "DELETE" Then lastIdentifier = lngInsertedID
    End Select
End Sub

Die Prozedur WriteEditLog übernimmt das Steuerelement-Objekt CTL, welches sich geändert hat. Aus diesem Objekt erhalten wir die Werte für Value, OldValue und ControlSource, also der Name des Tabellenfeldes das an dieses Control gebunden ist:

Private Sub WriteEditLog(ByRef CTL_ As Access.Control)
    Dim strSQL As String
    strSQL = "Insert into tblAuditTrailLog (AuditTime, UserName, FormName, FieldName, ActionType, RecordID, OldValue, NewValue) Values (" & _
             "'" & dtmCurrentDateTime & "', " & _
             "'" & strUserName & "', " & _
             "'" & m_frm.Name & "', " & _
             "'" & CTL_.ControlSource & "', " & _
             "'" & UserAction & "', " & _
             "'" & m_frm.Controls(m_IdFieldName).Value & "', " & _
             "'" & CTL_.OldValue & "', " & _
             "'" & CTL_.Value & "' " & _
             ")"
    CurrentProject.Connection.Execute strSQL
End Sub

Bei der Löschung / Neuanlage ist es ähnlich, nur benötigen wir dort die Felder AuditTime, UserName, FormName, ActionType, RecordID. Was zum Schluss noch fehlt sind ein paar Properties, Hilfsfunktionen und Variablendeklarationen.

5. Setup und Initialisierung top

Was noch fehlt ist ein Setup, welches beim erstmaligen Verwenden der Klasse die Log-Tabelle für das Audit Trail anlegt.
Um diese Tabellenerstellungs-Abfrage nur 1-mal auszuführen, muss geprüft werden ob die Tabelle bereits existiert. Dazu greifen wir per Dlookup() auf die System-Tabelle MSysObjects zu, in der alle angelegten Access-Objekte gespeichert werden. Ausgeführt wird das beim Klassen-Ereignis „Initialize“:

Private Sub Class_Initialize()
    Dim strSQL As String
    strSQL = "Create Table tblAuditTrailLog (" & _
             "ID COUNTER CONSTRAINT ID PRIMARY KEY, " & _
             "AuditTime date, " & _
             "UserName Text, " & _
             "FormName Text, " & _
             "FieldName Text, " & _
             "ActionType Text, " & _
             "RecordID Text, " & _
             "OldValue Text, " & _
             "NewValue Text" & _
             ")"
    If Not AuditTabExists Then
        CurrentProject.Connection.Execute strSQL
    End If
End Sub

In der Variable strSQL wird die Tabellenerstellungs-Abfrage zusammengestellt. Man beachte dass ein AutoWert-Feld als COUNTER CONSTRAINT definiert wird. Die Funktion AuditTabExists prüft in MSysObjects ob bereits ein Eintrag mit Namen „tblAuditTrailLog“ vorhanden ist, falls nicht wird die Tabelle erstellt.

6. Die Klasse clsAuditTrail top

Die fertige Klasse clsAuditTrail sieht nun so aus:

Option Explicit
Option Compare Text

' ================================================
' Code by Andreas Vogt Email: info@accessblog.de
' ================================================

Private WithEvents m_frm As Form

Private m_Identifier As Long
Private m_IdFieldName As String
Private m_UserAction As String
Private m_AuditTabExists As Boolean

Private dtmCurrentDateTime As Date
Private strUserName As String

Public Property Set FormObj(ByRef FRM_ As Access.Form)
    Set m_frm = FRM_
    m_IdFieldName = getIDField(FRM_)
    m_frm.BeforeUpdate = "[Event Procedure]"
    m_frm.AfterUpdate = "[Event Procedure]"
    m_frm.OnDelete = "[Event Procedure]"
    m_frm.AfterDelConfirm = "[Event Procedure]"
End Property

Private Property Get AuditTabExists() As Boolean
    If Not m_AuditTabExists Then
        m_AuditTabExists = IsNull(DLookup("[Name]", "MSysObjects", "[Name] = 'tblAuditTrailLog' AND (Type = 1 Or Type = 6)")) = False
    End If
    AuditTabExists = m_AuditTabExists
End Property

Private Property Get UserAction() As String
    UserAction = m_UserAction
End Property

Private Property Let UserAction(ByVal UserAction_ As String)
    m_UserAction = UserAction_
End Property

Private Property Get lastIdentifier() As Long
    lastIdentifier = m_Identifier
End Property

Private Property Let lastIdentifier(ByVal lastident As Long)
    m_Identifier = lastident
End Property

Private Sub Class_Initialize()
    Dim strSQL As String
    strSQL = "Create Table tblAuditTrailLog (" & _
             "ID COUNTER CONSTRAINT ID PRIMARY KEY, " & _
             "AuditTime date, " & _
             "UserName Text, " & _
             "FormName Text, " & _
             "FieldName Text, " & _
             "ActionType Text, " & _
             "RecordID Text, " & _
             "OldValue Text, " & _
             "NewValue Text" & _
             ")"
    If Not AuditTabExists Then
        CurrentProject.Connection.Execute strSQL
    End If
End Sub

Private Sub Class_Terminate()
    Set m_frm = Nothing
End Sub

Private Sub m_frm_Delete(Cancel As Integer)
    UserAction = "DELETE"
    DataChanges
End Sub

Private Sub m_frm_AfterDelConfirm(Status As Integer)
    If Status <> acDeleteOK Then Call DataRedoDelete
End Sub

Private Sub m_frm_BeforeUpdate(Cancel As Integer)
    If m_frm.NewRecord Then
        UserAction = "NEW"
        DataChanges
    Else
        UserAction = "EDIT"
        DataChanges
    End If
End Sub

Private Sub DataChanges()
    Dim CTL As Control
    Dim lngInsertedID As Long

    dtmCurrentDateTime = Now()
    strUserName = Environ("USERNAME")
    Select Case UserAction
    Case "EDIT"
        For Each CTL In m_frm.Controls
            If CTL.Tag = "Audit" Then
                If Nz(CTL.Value) <> Nz(CTL.OldValue) Then
                    WriteEditLog CTL
                End If
            End If
        Next CTL
    Case Else
        lngInsertedID = WriteNewDeleteLog
        If UserAction = "DELETE" Then lastIdentifier = lngInsertedID
    End Select
End Sub

Private Sub WriteEditLog(ByRef CTL_ As Access.Control)
    Dim strSQL As String
    strSQL = "Insert into tblAuditTrailLog (AuditTime, UserName, FormName, FieldName, ActionType, RecordID, OldValue, NewValue) Values (" & _
             "'" & dtmCurrentDateTime & "', " & _
             "'" & strUserName & "', " & _
             "'" & m_frm.Name & "', " & _
             "'" & CTL_.ControlSource & "', " & _
             "'" & UserAction & "', " & _
             "'" & m_frm.Controls(m_IdFieldName).Value & "', " & _
             "'" & CTL_.OldValue & "', " & _
             "'" & CTL_.Value & "' " & _
             ")"
    CurrentProject.Connection.Execute strSQL
    
End Sub

Private Function WriteNewDeleteLog() As Long
    Dim strSQL As String
    Dim RecordsAffected As Long
    Dim cnn As New ADODB.Connection
    Dim rstTemp As ADODB.Recordset

    strSQL = "Insert into tblAuditTrailLog (AuditTime, UserName, FormName, ActionType, RecordID) Values (" & _
             "'" & dtmCurrentDateTime & "', " & _
             "'" & strUserName & "', " & _
             "'" & m_frm.Name & "', " & _
             "'" & UserAction & "', " & _
             "'" & m_frm.Recordset.Fields(m_IdFieldName).Value & "')"
    Set cnn = CurrentProject.Connection
    cnn.Execute strSQL, RecordsAffected
    If RecordsAffected > 0 Then
        Set rstTemp = cnn.Execute("SELECT @@IDENTITY")
        WriteNewDeleteLog = rstTemp(0)
        rstTemp.Close
    End If

    Set rstTemp = Nothing
    Set cnn = Nothing
End Function

Private Sub DataRedoDelete()
    Dim rstTemp As ADODB.Recordset

    Set rstTemp = New ADODB.Recordset
    With rstTemp
        .Open "SELECT * FROM tblAuditTrailLog", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
        .Find "ID = " & lastIdentifier
        If Not .EOF Then
            .Delete
        End If
    End With

    rstTemp.Close
    Set rstTemp = Nothing
End Sub

Private Function getIDField(FRM_ As Access.Form) As String
    Dim i As Long
    With FRM_.Recordset
        For i = 0 To .Fields.Count - 1
            If .Fields(i).Type = 4 Then
                getIDField = .Fields(i).Name
                Exit For
            End If
        Next i
    End With
End Function

Bis dahin
© 2015 Andreas Vogt

Arbeiten mit Interface-Klassen

By , 28. Februar 2014

Dass man mit Access auch objektorientiert entwickeln kann – wenn auch nicht völlig – dürfte hinlänglich bekannt sein. Klassenmodul erstellen, Methoden und Eigenschaften in als Prozeduren und Properties definieren, alles längst bekannt. Weniger bekannt aber ist die Verwendung von Interfaces bzw. Interface-Klassen.

Man stelle sich vor, man hat eine Klasse Auto, eine Klasse Motorrad und eine Klasse Fahrrad. 3 verschiedene Klassen, und doch werden die Methoden und Eigenschaften dieser Klassen in vielen Punkten identisch sein. Die Objekte aller 3 Klassen haben sicherlich die gemeinsamen Methoden „Fahren“, „Bremsen“ und „Lenken“. Außerdem die gemeinsamen Eigenschaften „Geschwindigkeit“, „Richtung“ und „Farbe“, um ein paar Beispiele zu nennen.

Diese Zusammenhänge lassen sich durch ein Interface abbilden, und das geschieht einfacher als man denkt. Ein Interface ist zuerst einmal nichts anderes als ein Klassenmodul, das man speziell benennt. Eingebürgert hat sich, dass man vor den Interface-Namen ein „i“ setzt. Wir erstellen also ein Klassenmodul, und benennen es „iFahrzeug“. Um jetzt die Struktur für Fahrzeuge in diesem Interface abzubilden erstellen wir darin alle Properties und Prozeduren wie oben benannt, aber ohne weiteren Code. Unser Interface sieht jetzt wie folgt aus:

Option Explicit

Property Get Geschwindigkeit() As Long
End Property
Property Let Geschwindigkeit(ByVal lSpeed As Long)
End Property

Property Get Richtung() As String
End Property
Property Let Richtung(ByVal cRichtung As String)
End Property

Property Get Farbe() As Long
End Property
Property Let Farbe(ByVal lColor As Long)
End Property

Sub Fahren()
End Sub

Sub Bremsen()
End Sub

Sub Lenken()
End Sub

Die Prozeduren und Properties dürfen nicht als private deklariert werden, sonst sind diese bei der Verwendung nicht verfügbar.
Interfaces können nur innerhalb Klassenmodulen und Formularmodulen (Formulare sind praktischer Weise ja auch Klassen) verwendet werden, nicht in Standard-Modulen. Die Einbindung in ein Klassenmodul findet mit dem Schlüsselwort „Implements“ statt. Dies erfolgt in der Klasse direkt nach den Optionen-Deklaration, in unserem Fall also: Implements iFahrzeug.

Sobald man das getan hat steht das Interface zur Nutzung bereit. Dies macht sich durch den Eintrag von iFahrzeug in der linken oberen Auswahlliste bemerkbar (Unterhalb der Symbolleiste). Wählt man darin iFahrzeug aus, wird die erste Property bereits in die Klasse eingefügt. Klickt man anschließend auf die rechte obere Auswahlliste, so sind die restlichen Properties und Prozeduren auswählbar, und können einfach in die Klasse eingefügt werden. Spätestens jetzt sollte die Verwendung von Interfaces klar geworden sein.

Mittels einem Interface wird definiert welche Methoden und Eigenschaften eine Klasse mindestens haben muss!. Das Bedeutet alle in dem Interface definierten Prozeduren und Properties müssen auch in die Klasse übernommen werden. Macht man das nicht so erhält man einen Fehler wenn man das Projekt Kompelliert.

Fügt man die restlichen Properties und Methoden ein, sollte die Klasse jetzt wie folgt aussehen:

Option Compare Database
Option Explicit

Implements iFahrzeug

Private Sub iFahrzeug_Fahren()
End Sub

Private Sub iFahrzeug_Bremsen()
End Sub

Private Sub iFahrzeug_Lenken()
End Sub

Private Property Get iFahrzeug_Farbe() As Long
End Property

Private Property Let iFahrzeug_Farbe(ByVal RHS As Long)
End Property

Private Property Let iFahrzeug_Geschwindigkeit(ByVal RHS As Long)
End Property

Private Property Get iFahrzeug_Geschwindigkeit() As Long
End Property

Private Property Let iFahrzeug_Richtung(ByVal RHS As String)
End Property

Private Property Get iFahrzeug_Richtung() As String
End Property

Jetzt kann man relativ einfach jede Klasse (Auto, Motorrad, Fahrrad) nach dieser „Vorschrift“ erstellen, auscoden und nach Bedarf erweitern.

Fassen wir mal zusammen:

  • Interfaces sind Daten- und Zugriffsstrukturen, welche den Zugriff und die Steuerung von Objekten regeln
  • Vereinfacht gesagt ist ein Interface eine Struktur an Methoden und Eigenschaften.
  • Interfaces sind bestimmte Klassenmodule, die nach einem Schema benannt werden.
  • Interfaces enthalten nur die Rümpfe der Prozeduren und Properties. Code darin würde ignoriert werden.
  • Eingebunden in Klassenmodulen (oder Formular-Code) werden diese mit dem Schlüsselwort „Implements“
  • Objektorientiert Entwickeln ist Cool 😉

Und nun viel Spass beim Experimentieren mit OOP und Interfaces.

Bis dahin
© 2014 Andreas Vogt

OfficeFolders theme by Themocracy