Im Beitrag “Datenhistorie per Trigger” haben wir gezeigt, welche Anweisungen nötig sind, um eine Archivtabelle anzulegen und der Originaltabelle einen Trigger hinzuzufügen, der beim Ändern oder Löschen eines Datensatzes die vorherige Version in einer Archivtabelle speichert. Im vorliegenden Beitrag wollen wir eine VBA-Prozedur entwickeln, mit der Sie zu einer Tabelle mit einem einfachen Aufruf die Archivtabelle und den Trigger in einem Rutsch anlegen können. Damit sichern Sie Ihre Daten bei Änderungen auch für mehrere Tabellen ganz schnell ab.
Voraussetzungen
In diesem Beitrag kommen die bereits im Beitrag Datenhistorie per Trigger (siehe www.access-im-unternehmen.de/1211) erwähnten Access-SQL-Tools zum Einsatz. Diese haben wir wiederum bereits im Beitrag SQL Server-Tools vorgestellt (siehe www.access-im-unternehmen.de/1061).
Ziel des Beitrags
Das Ziel ist es nun, eine Prozedur zu entwickeln, der Sie nur den Namen der Tabelle übergeben, für welche eine Archivtabelle erstellt werden soll und die einen Trigger erhalten soll, der beim Ändern oder Löschen von Datensätzen aus dieser Tabelle die vorherige Version des Datensatzes in der Archivtabelle speichert.
Dazu haben wir zunächst alle Elemente der SQL Server-Tools in die Beispieldatenbank integriert.
Prozedur zum Erstellen der Archivtabelle
Im oben genannten Beitrag Datenhistorie per Trigger haben wir eine Archivtabelle mit einer SQL-Anweisung wie der folgenden erstellt:
CREATE TABLE [dbo].[tblKunden_Archiv]( [ArchivID] [int] IDENTITY(1,1) NOT NULL, [KundeID] [int] NULL, ... [Loeschdatum] [datetime] NULL, [Aenderungsdatum] [datetime] NULL CONSTRAINT [tblKunden_Archiv$PrimaryKey] PRIMARY KEY CLUSTERED ( [ArchivID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
Diese Anweisung wollen wir nun parametrisieren, damit wir diese für beliebige Tabellennamen erstellen können. Dazu benötigen wir etwas Wissen über die Tabelle, für die wir die Archivtabelle anlegen wollen. Da wir nicht davon ausgehen können, dass es eine per ODBC erstellte Verknüpfung auf diese Tabelle gibt, wollen wir die Felder und ihre Eigenschaften direkt vom SQL Server beziehen. Um die Feldnamen und die Datentypen zu ermitteln, können wir die folgende Abfrage nutzen, die wir direkt an den SQL Server absetzen:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tblKunden'
Im SQL Server sieht das Ergebnis für die Tabelle tblKunden wie in Bild 1 aus.
Bild 1: Ausgabe der Abfrage der Eigenschaften der Felder einer Tabelle
Funktion zum Einlesen der Feldliste für CREATE TABLE
Wir benötigen eine Funktion, mit der wir die in der Archivtabelle anzulegenden Felder ermitteln.
Diese heißt FeldlisteZusammenstellen und sieht wie in Listing 1 aus.
Public Function FeldlisteZusammenstellen(strTabelle As String, strVerbindungszeichenfolge As String) As String Dim rst As DAO.Recordset Dim strFeldliste As String Set rst = mdlToolsSQLServer.SQLRecordset("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" _ & strTabelle & "'", strVerbindungszeichenfolge) strFeldliste = " [ArchivID] [int] IDENTITY(1,1) NOT NULL," & vbCrLf Do While Not rst.EOF If Not rst!DATA_TYPE = "Timestamp" Then strFeldliste = strFeldliste & " [" & rst!COLUMN_NAME & "]" strFeldliste = strFeldliste & " [" & rst!DATA_TYPE & "]" If Not IsNull(rst!Character_maximum_Length) Then If Not rst!Character_maximum_Length = -1 Then strFeldliste = strFeldliste & "(" & rst!Character_maximum_Length & ")" Else strFeldliste = strFeldliste & "(max)" End If End If If rst!IS_NULLABLE = "YES" Then strFeldliste = strFeldliste & " NULL" Else strFeldliste = strFeldliste & " NOT NULL" End If strFeldliste = strFeldliste & "," & vbCrLf End If rst.MoveNext Loop strFeldliste = strFeldliste & " [Loeschdatum] [datetime] NULL," & vbCrLf strFeldliste = strFeldliste & " [Aenderungsdatum] [datetime] NULL" FeldlisteZusammenstellen = strFeldliste End Function
Listing 1: Funktion zum Ermitteln der Feldliste
Die Funktion nimmt den Namen der zu untersuchenden Tabelle sowie die Verbindungszeichenfolge der SQL Server-Datenbank als Parameter entgegen und liefert einen String als Ergebnis zurück. Die Funktion verwendet die Funktion SQLRecordset des Moduls mdlToolsSQLServer, um die Datensätze der Tabelle INFORMATION_SCHEMA.COLUMNS zurückzuliefern.
Dabei wollen wir nach dem Feld TABLE_NAME filtern, das die Datensätze auf diejenigen einschränken soll, deren Tabellenname dem Wert des Parameters strTabelle entspricht. Auch diese Funktion nimmt wieder die Verbindungszeichenfolge als Parameter entgegen.
Danach stellt die Funktion FeldlisteZusammenstellen in der Variablen strFeldliste die Liste der anzulegenden Felder für die Archivtabelle zusammen. Bevor wir die Datensätze des Recordsets rst durchlaufen, fügen wir dort bereits als ersten Eintrag den folgenden hinzu:
[ArchivID] [int] IDENTITY(1,1) NOT NULL,
Dies entspricht dem Primärschlüsselfeld für die Archivtabelle. Das Komma am Ende deutet bereits an, dass nun weitere Felder folgen. Diese stellen wir in einer Do While-Schleife über alle Datensätze der Tabelle zusammen. Ein Feld wird nur hinzugefügt, wenn es nicht den Datentyp Timestamp hat, was wir in einer If…Then-Bedingung prüfen.
Ist das nicht der Fall, berücksichtigen wir als Erstes den Inhalt des Feldes COLUMN_NAME und damit den Feldnamen des anzulegenden Feldes, den wir in eckige Klammern einfassen.
Dann folgt der Datentyp, den wir aus dem Feld DATA_TYPE des Recordsets entnehmen und den wir durch ein Leerzeichen vom Feldnamen getrennt ebenfalls in eckige Klammern einfassen. Dann prüfen wir, ob das Feld CHAR-ACTER_MAXIMUM_LENGTH einen Wert enthält. Dieses liefert die Anzahl der Zeichen für Textfelder. Liegt ein Wert vor, fügen wir diesen in runde Klammern eingefasst zum Ausdruck in strFeldListe ein. Aber es kann auch sein, dass der Wert -1 beträgt. Dieser tritt auf, wenn der eigentliche Wert max lautet, was auf ein langes Textfeld hinweist. In diesem Fall wollen wir nicht -1, sondern max in runden Klammern zur Felddefinition hinzufügen.
Schließlich folgt das Feld ISNULLABLE, das mit den Werten YES und NO angibt, ob das Feld Nullwerte aufnehmen darf oder nicht. Im Falle von YES fügen wir NULL zur Felddefinition hinzu, anderenfalls NOT NULL. Anschließend hängen wir noch ein abschließendes Komma an die Beschreibung dieses Feldes an und gehen dann in die nächste Runde der Do While-Schleife.
Sind alle Felder durchlaufen, fehlen noch die beiden Felder Loeschdatum und Aenderungsdatum, die beide mit dem Datentyp datetime angelegt werden sollen und Nullwerte enthalten dürfen.
Jeder Felddefinition fügen wir außerdem noch einen Zeilenumbruch hinzu, sodass das Ergebnis der Funktion beispielsweise für die Tabelle tblKunden wie folgt aussieht:
[ArchivID] [int] IDENTITY(1,1) NOT NULL, [KundeID] [int] NOT NULL, [KundenCode] [nvarchar](5) NULL, [Firma] [nvarchar](40) NOT NULL, [AnredeID] [int] NULL, [Vorname] [nvarchar](255) NULL, [Nachname] [nvarchar](255) NULL, [Position] [nvarchar](30) NULL, [PLZ] [nvarchar](10) NULL, [Strasse] [nvarchar](255) NULL, [Ort] [nvarchar](15) NULL, [Region] [nvarchar](15) NULL, [Land] [nvarchar](15) NULL, [Telefon] [nvarchar](24) NULL, [Telefax] [nvarchar](24) NULL, [Loeschdatum] [datetime] NULL, [Aenderungsdatum] [datetime] NULL
Diese Zeichenkette wird dann als Ergebnis der Funktion zurückgegeben. Den Aufruf der Funktion zum Testen können Sie so gestalten:
Public Sub FieldlisteCreate_Test() Dim strVerbindungszeichenfolge As String strVerbindungszeichenfolge = VerbindungszeichenfolgeNachID(10) Debug.Print FieldlistCreate("tblKunden", strVerbindungszeichenfolge) End Sub
Hier nutzen wir die Funktion VerbindungszeichenfolgeNachID, um die Verbindungszeichenfolge zu ermitteln. Die SQL Server-Tools enthalten eine Tabelle namens tblVerbindungszeichenfolgen, welche die Informationen für die Verbindungszeichenfolgen speichert.
Mit dem Formular frmVerbindungszeichenfolgen können Sie die Verbindungszeichenfolge zusammenstellen und auch testen. Merken Sie sich die ID der dabei gespeicherten Verbindungszeichenfolge und übergeben Sie diese der Funktion VerbindungszeichenfolgeNachID als Parameter.
Funktion CreateTableZusammenstellen
Die Funktion aus Listing 2 stellt die aus der Funktion FeldlisteZusammenstellen ermittelten Felder mit einigen weiteren Zeilen zu der kompletten CREATE TABLE-Anweisung zusammen. Diese Funktion erwartet die gleichen Parameter wie die Funktion FeldlisteZusammenstellen.
Public Function CreateTableZusammenstellen(strTabelle As String, strVerbindungszeichenfolge As String) As String Dim strCreateTable As String strCreateTable = "CREATE TABLE [dbo].[" & strTabelle & "_Archiv](" & vbCrLf strCreateTable = strCreateTable & FeldlisteZusammenstellen(strTabelle, strVerbindungszeichenfolge) & vbCrLf strCreateTable = strCreateTable & "CONSTRAINT [" & strTabelle & "_Archiv$PrimaryKey] PRIMARY KEY CLUSTERED" _ & vbCrLf strCreateTable = strCreateTable & "(" & vbCrLf strCreateTable = strCreateTable & " [ArchivID] Asc" & vbCrLf strCreateTable = strCreateTable & ")WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, " _ & "ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]" & vbCrLf strCreateTable = strCreateTable & ") ON [PRIMARY]" CreateTableZusammenstellen = strCreateTable End Function
Listing 2: Funktion zum Ermitteln der Definition der Archivtabelle
Archivtabelle erstellen
Mit dem Ergebnis können wir nun bereits die Archivtabelle erstellen. Während wir die hier zusammengestellte CREATE TABLE-Anweisung normalerweise im SQL Server Management Studio ausführen würden, wollen wir dies auch von Access aus erledigen. Dazu liefern die SQL Server Tools auch eine Funktion. Diese heißt SQLAktionsabfrageOhneErgebnis und erwartet die auszuführende Abfrage sowie die Verbindungszeichenfolge als Parameter und einen optionalen Parameter für die Rückgabe einer eventuellen Fehlermeldung.
Diese Funktion rufen wir von einer anderen Funktion namens ArchivtabelleErstellen auf, die Sie in Listing 3 finden. Diese ermittelt zunächst die Verbindungszeichenfolge und mit der Funktion CreateTableZusammenstellen die SQL-Anweisung zum Erstellen der Archivtabelle. Dann ruft sie die Funktion SQLAktionsabfrageOhneErgebnis auf und speichert das Ergebnis in der Variablen lngErgebnis. Außerdem verwendet sie als Rückgabeparameter die Variable strFehlermeldung. Liefert SQLAktionsabfrageOhneErgebnis einen Wert ungleich 0, können Sie der Variablen strFehlermeldung den Text der Meldung des aufgetretenen Fehlers entnehmen.
Public Function ArchivtabelleErstellen() As Boolean Dim strTabelle As String Dim strCreateTable As String Dim strVerbindungszeichenfolge As String Dim strFehlermeldung As String Dim lngErgebnis As Long strTabelle = "tblKunden" strVerbindungszeichenfolge = VerbindungszeichenfolgeNachID(10) strCreateTable = CREATETABLEZusammenstellen(strTabelle, strVerbindungszeichenfolge) lngErgebnis = SQLAktionsabfrageOhneErgebnis(strCreateTable, strVerbindungszeichenfolge, strFehlermeldung) If Not lngErgebnis = 0 Then Debug.Print lngErgebnis, strFehlermeldung Else ArchivtabelleErstellen = True End If End Function