Wenn mehrere Benutzer mit den Daten einer Datenbank arbeiten, möchten Sie für die relevanten Tabellen vielleicht nachhalten, wer wann welchen Datensatz angelegt, bearbeitet oder gelöscht hat. Dazu sind zwei Schritte nötig: Das Hinzufügen der Felder zum Speichern dieser Informationen in den relevanten Tabellen und das Eintragen dieser Informationen, wenn es soweit ist. Dieser Beitrag zeigt, wie Sie mit minimalem Aufwand ein solches Protokoll einrichten.
Eine detaillierte Historie der änderungen an Daten samt Archivierung der Daten vor jeder änderung ist aufwendig und wird nur selten benötigt. Häufiger trifft man den Fall an, dass man einfach nur wissen möchte, wer zum Beispiel einen Kunden angeleg oder wer eine Bestellung zuletzt geändert oder gelöscht hat.
Beim Anlegen und Bearbeiten der Daten ist dies gar kein Problem: Sie fügen der Tabelle einfach je zwei Felder zum Speichern des Datums der Aktion und des Mitarbeiters hinzu und sehen an den entsprechenden Stellen, also beispielsweise in den Formularen zur Bearbeitung dieser Daten, die benötigten Codezeilen zum Eintragen der änderungsdaten vor.
Beim Löschen wird dies schon schwieriger: Wo wollen Sie ein Löschdatum und einen Verweis auf den Verantwortlichen unterbringen, wenn der Datensatz anschließend nicht mehr vorhanden ist Ganz einfach: Sie löschen den Datensatz gar nicht, sondern versehen ihn mit einem Marker.
So können Sie auch das Löschdatum und den löschenden Mitarbeiter ganz einfach hinzufügen. Dass der Datensatz ja nun gar nicht physisch gelöscht ist, ist zwar kein Problem, bringt aber ein wenig zusätzlichen Aufwand mit sich: Alle Abfragen, Formulare, Berichte, Steuerelemente und Codeabschnitte, die sich auf die Daten der Tabelle mit den vermeintlich gelöschten Datensätzen beziehen, müssen um eine entsprechende WHERE-Klausel ergänzt werden, die alle Datensätze ausschließt, für die der Wert eines Feldes etwa mit dem Namen GeloeschtAm nicht Null ist.
Im Gegenzug können Sie aber auch später noch auf die Daten zugreifen, die bereits gelöscht wurden – beispielsweise, um diese entgültig zu löschen, zu archivieren oder um Auswertungen auf den gesamten Datenbestand zu fahren.
Tabellen anpassen
Wie üblich möchten wir den Aufwand zum Einbau dieser Lösung möglichst gering halten. Das betrifft zum Beispiel das Hinzufügen der Felder mit den änderungsdaten zu den betroffenen Tabellen.
Normalerweise öffnet man jede dieser Tabellen im Entwurf, fügt die gewünschten Felder hinzu, verknüpft diese gegebenenfalls mit einer Mitarbeitertabelle, um sich per Fremdschlüssel auf die ausführenden Mitarbeiter beziehen zu können, und speichert dann die Tabelle.
Und das erledigt man nicht nur für eine, sondern für alle Tabellen, deren änderungsdaten protokolliert werden sollen.
Puh, das ist aber langweilig – da setzen wir uns doch lieber mal ein paar Minuten hin und programmieren eine kleine Funktion, die uns diese Aufgabe abnimmt.
Voraussetzung für die nachfolgend beschriebene Prozedur ist eine Benutzertabelle und die Anmeldung der Benutzer beim Start der Datenbank. Für Beispielzwecke soll diese möglichst einfach aussehen – zum Beispiel wie in Bild 1.
Bild 1: Diese Tabelle soll in den Feldern zum Eintragen der änderungen per Fremdschlüssel referenziert werden.
Die änderungsfelder legen wir mit einigen SQL-Anweisungen an, die wir mit der Execute-Methode des Database-Objekts ausführen. Werfen wir zunächst einen Blick auf die nackten SQL-Anweisungen. Die erste soll einfach nur ein Datumsfeld namens AngelegtAm hinzufügen und sieht so aus:
ALTER TABLE tblKunden ADD AngelegtAm DATETIME
Sie können diese Anweisung ausprobieren, indem Sie eine neue, leere Abfrage im Entwurf öffnen und dann zur SQL-Ansicht wechseln. Dort tragen Sie den SQL-Ausdruck ein und führen ihn aus.
Wir brauchen noch zwei weitere Anweisungen, von denen die erste das Feld zum Speichern des durchführenden Benutzers anlegt und das zweite die Beziehung von diesem Fremdschlüsselfeld zur Tabelle tblBenutzer:
ALTER TABLE tblKunden ADD AngelegtDurch INTEGER ALTER TABLE tblKunden ADD CONSTRAINT FKAngelegtDurch FOREIGN KEY (AngelegtDurch) REFERENCES tblKunden
Zusammengefasst in einer Funktion sehen die SQL-Anweisungen wie in Listing 1 aus. Der Aufruf für unsere Beispielkonstellation mit einer Tabelle tblBenutzer für die Benutzerdaten und mit einer Tabelle tblKunden, die die Felder zum Eintragen der änderungsdaten erhalten soll, sieht so aus:
Listing 1: Hinzufügen von History-Feldern zu einer Tabelle
Public Function AddHistoryFieldsToTable(strTableChange As String, strTableUsers As String)
''AngelegtAm und AngelegtDurch
dbs.Execute "ALTER TABLE " & strTableChange & " ADD AngelegtAm DATETIME", dbFailOnError
dbs.Execute "ALTER TABLE " & strTableChange & " ADD AngelegtDurch INTEGER", dbFailOnError
dbs.Execute "ALTER TABLE " & strTableChange & " ADD CONSTRAINT FKAngelegtDurch " _
& "FOREIGN KEY (AngelegtDurch) REFERENCES " & strTableUsers, dbFailOnError
''ZuletztGeaendertAm und ZuletztGeaendertDurch
dbs.Execute "ALTER TABLE " & strTableChange & " ADD ZuletztGeaendertAm DATETIME", dbFailOnError
dbs.Execute "ALTER TABLE " & strTableChange & " ADD ZuletztGeaendertDurch INTEGER", dbFailOnError
dbs.Execute "ALTER TABLE " & strTableChange & " ADD CONSTRAINT FKGeaendertDurch " _
& "FOREIGN KEY (GeaendertDurch) REFERENCES " & strTableUsers, dbFailOnError
''GeloeschtAm und GeloeschtDurch
dbs.Execute "ALTER TABLE " & strTableChange & " ADD GeloeschtAm DATETIME", dbFailOnError
dbs.Execute "ALTER TABLE " & strTableChange & " ADD GeloeschtDurch INTEGER", dbFailOnError
dbs.Execute "ALTER TABLE " & strTableChange & " ADD CONSTRAINT FKGeloeschtDurch " _
& "FOREIGN KEY (GeloeschtDurch) REFERENCES " & strTableUsers, dbFailOnError
End Function
AddHistoryFieldsToTable "tblKunden", "tblBenutzer"
Verwenden Sie diesen Aufruf für alle Tabellen, für deren Datensätze Sie änderungsinformationen speichern möchten. Das Ergebnis sieht wie in Bild 2 aus. Was Sie hier nicht erkennen können, sind die Beziehungen, welche die Funktion aus Listing 1 zwischen den Fremdschlüsselfeldern AngelegtDurch, ZuletztGeaendertAm und GeloeschtAm und der Tabelle tblBenutzer anlegt. Abb. 3 liefert den Beweis, dass auch die Beziehungen ordnungsgemäß angelegt wurden.
Bild 2: Eine Beispieltabelle mit Feldern zum Speichern von änderungsinformationen
Bild 3: Diese Beziehungen wurden mit der Funktion aus Listing 1 angelegt.
Alternative Tabellenanpassung
Es gibt noch eine Alternative zur beschriebenen Vorgehensweise: Dabei würden Sie die sechs Felder zum Festhalten der änderungsinformationen nicht direkt in die Tabellen schreiben, sondern in eine separate Tabelle. Diese enthält neben den sechs Feldern noch zwei weitere Felder zum Speichern der Tabelle und des Datensatzes, auf die sich die Informationen beziehen.
Der Nachteil dieser Vorgehensweise ist, dass der Aufwand für das Herausfiltern von als gelöscht markierten Daten relativ hoch ist. Daher sehen wir an dieser Stelle von einer ausführlicheren Beschreibung dieser Variante ab.
änderungsdaten pflegen
Der nächste Schritt ist etwas aufwendiger: Sie müssen prüfen, an welchen Stellen der Datenbank Operationen auf Basis der Daten dieser Tabelle durchgeführt werden. Überall dort müssen Sie per Code oder über entsprechende änderungen an eventuell vorhandenen Aktionsabfragen dafür sorgen, dass bei änderungen auch die nötigen Informationen in die soeben angelegten Felder eingetragen werden.
Leichter haben Sie es, wenn Sie die Anwendung ohnehin gerade erst erstellen – Sie laufen dann weniger Gefahr, die Stellen, an denen die Daten geändert werden, zu übersehen.
Vielleicht ist das Ganze aber auch gar nicht so aufwendig. Gehen wir einmal von der Annahme aus, dass Sie Daten nur über Formulare, über die Execute-Methode des Database-Objekts oder per DAO mit AddNew, Edit und Update ändern. Dann ergeben sich möglicherweise verallgemeinernde Vereinfachungen – aber schauen Sie doch selbst.
änderungsdaten in Formularen
In Formularen gibt es zwei Ereignisse, die beim Speichern eines neuen oder geänderten beziehungsweise beim Löschen eines Datensatzes ausgelöst werden. Das Ereignis Vor Aktualisierung beispielsweise feuert immer beim Speichern eines geänderten Datensatzes, egal, ob es sich um einen neuen oder einen vorhandenen Datensatz handelt.
Zu Beispielzwecken legen Sie nun ein einfaches Formular an, das alle Felder der oben beschriebenen Tabelle tblKunden enthält. Erstellen Sie dann eine Prozedur für die Ereigniseigenschaft Vor Aktualisierung, die wie folgt aussieht:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord = True Then
Me!AngelegtAm = Now
Else
Me!ZuletztGeaendertAm = Now
End If
End Sub
Die Prozedur prüft, ob gerade ein neu angelegter (Me.NewRecord = True) oder ein bestehender Datensatz geändert wurde und nun gespeichert werden soll. Im Falle eines neuen Datensatzes schreibt die Routine noch das aktuelle Datum samt Zeit in das Feld AngelegtAm, bei einem vorhandenen Datensatz ist das Feld ZuletztGeaendertAm das Ziel. Das ist alles – änderungen im Formular werden so zuverlässig protokolliert.
Betrachten wir nun das Löschen eines Datensatzes. Das Löschen löst das Ereignis Beim Löschen aus, wofür Sie die folgende Routine hinterlegen:
Private Sub Form_Delete(Cancel As Integer)
Me!GeloeschtAm = Now
Me.Dirty = False
Cancel = True
End Sub
Die Routine stellt das Löschdatum des Datensatzes auf das aktuelle Datum samt Zeit ein, speichert den Datensatz (Me.Dirty = True) und bricht den Löschvorgang ab, damit der Datensatz nicht wirklich gelöscht wird (sonst hätten wir wohl nichts vom frisch geänderten Löschdatum …).
Wenn Sie dies nun im Formular ausprobieren, werden Sie feststellen, dass diese Routine dummerweise auch das letzte änderungsdatum beeinflusst, denn das Eintragen des Löschdatums und das anschließende Speichern ist natürlich auch eine änderung, die vom Vor Aktualisierung-Ereignis registriert wird.
Wir müssen der Routine Form_BeforeUpdate also irgendwie mitteilen, dass sie das änderungsdatum beim Löschen eines Datensatzes und der damit verbundenen änderung des Datensatzes nicht berücksichtigt. Dazu führen wir die folgende formularmodulweit gültige Variable ein:
Dim bolDelete As Boolean
Sie soll vor dem Speichern des Datensatzes während des Löschvorgangs auf den Wert True und anschließend wieder auf False eingestellt werden:
Private Sub Form_Delete(Cancel As Integer)
Me!GeloeschtAm = Now
bolDelete = True
Me.Dirty = False
bolDelete = False
Cancel = True
End Sub
Damit die Vor Aktualisierung-Ereignisprozedur dies entsprechend verarbeitet, soll sie vor dem ändern des änderungsdatums prüfen, ob nicht vielleicht gerade ein Löschvorgang erfolgt und somit das änderungsdatum beibehalten werden soll:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord = True Then
Me!AngelegtAm = Now
Else
If bolDelete = False Then
Me!ZuletztGeaendertAm = Now
End If
End If
End Sub
Probieren Sie dies nun aus – es funktioniert. Aber auch nur fast: Der gelöschte Datensatz wird zwar durch den entsprechenden Eintrag im Feld GeloeschtAm als gelöscht markiert, verschwindet aber nicht aus der Datenblattansicht.
Das liegt an zwei Dingen: Erstens haben wir die Datenherkunft noch gar nicht entsprechend eingestellt, das heißt, dass einfach alle Datensätze der Tabelle tblKunden angezeigt werden.
Zweitens würde es selbst dann nicht funktionieren, weil die Datenherkunft während des Löschens eines Datensatzes nicht aktualisiert werden kann.
Wir müssen also noch zwei änderungen vornehmen. Die erste bezieht sich auf die Datenherkunft, die von nun an aus der folgenden Abfrage bestehen soll:
SELECT * FROM tblKunden WHERE GeloeschtAm IS NULL
Dies führt dazu, dass nur noch solche Datensätze angezeigt werden, deren Feld GeloeschtAm noch leer ist und die dementsprechend noch nicht gelöscht wurden. Übrigens sollten Sie die Felder mit den änderungsdaten dringend mit einem Index versehen, da sonst schnell die Performance in den Keller geht.
Das zweite Problem ist schwieriger zu lösen. Wie bringen wir dem Formular bei, seine Datenherkunft unabhängig vom Löschvorgang zu aktualisieren Hier hilft nur das gute, alte Timer-Ereignis.
Dieses wird ja normalerweise in regelmäßigen zeitlichen Abständen, die über die Eigenschaft Zeitgeberintervall festgelegt werden, ausgelöst. Hier soll dieses Ereignis nicht von Beginn an feuern, daher behalten Sie den Wert 0 für die Eigenschaft Zeitgeberintervall bei.
Die Eigenschaft soll erst geändert werden, wenn der Löschvorgang läuft. Daher stellen Sie diese innerhalb der Ereignisprozedur, die durch das Ereignis Nach Aktualisierung ausgelöst wird, auf den Wert 100 ein (entspricht einer zehntel Sekunde):
Private Sub Form_AfterUpdate()
Me.TimerInterval = 100
End Sub
Aus der oben bereits beschriebenen Ereignisprozedur Beim Löschen entfernen Sie die Zeile bolDelete = False:
Private Sub Form_Delete(Cancel As Integer)
Me!GeloeschtAm = Now
bolDelete = True
Me.Dirty = False
Cancel = True
End Sub
Schließlich definieren Sie noch eine Prozedur, die durch den Zeitgeber ausgelöst wird. Diese sieht wie folgt aus:
Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...
den kompletten Artikel im PDF-Format mit Beispieldatenbank
diesen und alle anderen Artikel mit dem Jahresabo