In vielen Datenbankanwendungen fallen temporäre Daten an, also Daten, die in Tabellen geschrieben und in der gleichen Session wieder gelöscht werden. Das kann sowohl in nicht aufgeteilten Datenbanken als auch in aufgeteilten Datenbanken mit Frontend und Backend geschehen. Man könnte denken, die Größe der Datenbankdatei würde nach dem Anfügen und Löschen von Daten einigermaßen konstant sein. Das ist jedoch nicht der Fall: Gelöschte Daten sind zwar nicht mehr in den Tabellen zu finden, allerdings benötigt die Datenbank anschließend ungefähr genauso viel Speicherplatz wie vor dem Löschen. Wie das zu erklären ist und wie wir durch die Komprimierung einer Datenbank dennoch dafür sorgen können, dass eine Datenbank sich durch die Verwendung temporärer Daten nicht übermäßig aufbläht, beschreiben wir in diesem Beitrag.
Beispieldatenbank erzeugen
Wenn wir das Verhalten von Access-Datenbanken beim Anlegen und Löschen von Daten und beim Komprimieren der Datenbank untersuchen wollen, brauchen wir die Möglichkeit, schnell Daten hinzuzufügen und zu entfernen – und natürlich eine Tabelle, in der wir die Daten speichern.
Die Tabelle soll tblTexte heißen und neben dem Primärschlüsselfeld ID ein Textfeld namens Beispieltext mit einer Feldgröße von 255 Zeichen enthalten (siehe Bild 1).
Bild 1: Tabelle für Beispieldaten
Zum Anlegen einer relevanten Menge von Daten verwenden wir die folgende Prozedur:
Public Sub TabelleFuellen() Dim db As DAO.Database Dim i As Long Set db = CurrentDb For i = 1 To 10000 db.Execute "INSERT INTO tblTexte(Beispieltext) VALUES(''1234567890...12345'')", dbFailOnError Next i End Sub
Diese legt 10.000 Datensätze mit jeweils 255 Zeichen an. Um die Datensätze schnell wieder zu löschen, verwenden wir die folgende Prozedur:
Public Sub TabelleLeeren() Dim db As DAO.Database Set db = CurrentDb db.Execute "DELETE FROM tblTexte", dbFailOnError End Sub
Führen wir die erste Prozedur für eine bis dahin leere Datenbank aus, erhalten wir eine Datenbankgröße von ungefähr 3.264 KB.
Untersuchen der Datenbankgröße nach dem Löschen der Daten
Anschließend führen wir die zweite Prozedur aus und löschen damit alle Datensätze der Tabelle tblTexte. Schließen wir danach die Datenbank, bleibt die Dateigröße erhalten – obwohl die Tabelle keine Daten mehr enthält.
Platz für gelöschte Daten wird erst beim Komprimieren freigegeben
Der Grund für dieses Verhalten ist, dass der Speicherplatz erst nach dem Komprimieren einer Datenbank freigegeben wird. Also öffnen wir die Datenbank mit der leeren Tabelle erneut und rufen für diese den Befehl Datenbank komprimieren und reparieren auf (unter Access 365 beispielsweise unter Datei|Informationen zu finden).
Betrachten wir anschließend erneut die Dateigröße, weist diese wieder um die 400 KB aus. Daraus können wir ableiten: Der Speicherplatz für gelöschte Datensätze wird erst nach dem Komprimieren der Datenbank freigegeben.
Automatische Komprimierung aktivieren
Für monolithische Access-Anwendungen, also solche, die nur aus einer einzigen Datenbankdatei und nicht aus Frontend und Backend bestehen, können wir in den Access-Optionen die Option Beim Schließen komprimieren aktivieren (siehe Bild 2).
Bild 2: Aktivieren der automatischen Komprimierung
Füllen wir die Tabelle wieder mit Daten und löschen diese erneut, finden wir gleich nach dem nächsten Schließen wieder die geringe Dateigröße von rund 400 KB vor.
Komprimierung bei aufgeteilten Datenbanken
Nun teilen wir die Beispieldatenbank in Frontend und Backend auf, was am schnellsten mit dem Menübefehl Datenbanktools|Daten verschieben|Access-Datenbank gelingt. Hier brauchen wir nur den Pfad zur neuen Backenddatenbank anzugeben. Das Ergebnis ist eine neue Backenddatenbank, in welche die einzige Tabelle der bisher verwendeten Datenbank verschoben wurde, sowie eine Verknüpfung zu dieser Tabelle in der aktuellen Datenbankdatei.
Erstere nennen wir nun BackendKomprimieren_FE.accdb, letztere BackendKomprimieren_BE.accdb.
Anlegen und Löschen in Frontend und Backend
Die Frontenddatenbank mit der Tabellenverknüpfung und das Backend mit der leeren Tabelle haben wir nun zunächst komprimiert. Die Größen lauteten danach:
- Frontend: 432 KB
- Backend: 348 KB
Anschließend rufen wir die Prozedur zum Anlegen der 10.000 Datensätze in der Tabelle tblTexte auf. Dabei fällt zuerst auf, dass der Vorgang viel länger dauert als in der monolithischen .accdb-Datei. Noch viel interessanter ist die Beobachtung des benötigten Speicherplatzes für die beiden Dateien:
- Frontend: 40.412 KB
- Backend: 3.208 KB
Der Teil mit der Tabelle ist also ähnlich gewachsen wie zuvor, aber das Frontend ist massiv größer geworden – es wächst um mehr als den zehnfachen Speicherplatz der eigentlich erzeugten Daten. Obwohl es noch nicht einmal eine von uns angelegte Tabelle mit Daten enthält. Die Größe ändert sich auch nach dem Schließen nicht.
Anschließend haben wir die Prozedur zum Schreiben der Daten erneut aufgerufen, um zu prüfen, ob wir das Frontend so bis zur Grenze von 2 GB aufblähen können. Das ist jedoch nicht der Fall: Beim zweiten Durchlauf wird die Frontenddatenbank nur noch unwesentlich vergrößert, während das Backend seine Größe wie erwartet um ca. 2.800 KB ändert.
Wachstum des Frontends
Zum Wachstum der Größe des Frontends noch folgende Anmerkung: Dies geschieht nur, wenn wir die Daten per VBA in die Tabelle tblTexte des Backends einfügen. Wir haben in einem zweiten Versuch manuell mehrere tausend Datensätze dort eingefügt und in diesem Fall wurde zwar das Backend vergrößert, nicht jedoch das Frontend.
Komprimieren des Frontends
Komprimieren wir nun das Frontend, wird dieses wieder auf die ursprüngliche Größe geschrumpft. An der Größe des Backends ändert sich zunächst nichts, was auch nicht zu erwarten war, da wir die enthaltenen Daten nicht gelöscht haben. Also öffnen wir das Frontend erneut und löschen die Daten im Backend mit der Prozedur TabelleLeeren.
Die Größe des Backends bleibt erhalten. Nun rufen wir in der Frontenddatenbank den Befehl Komprimieren und Reparieren auf. Obwohl wir die Daten im Backend gelöscht haben, ändert sich dessen Speicherbedarf nicht.
Wenn wir das Frontend einer Kombination aus Frontend und Backend komprimieren, wird also nur der Speicherplatz im Frontend freigegeben, der für das Schreiben von Datensätzen im Backend angefallene Daten verwendet wurde.
Backend komprimieren
Wie also können wir nun dafür sorgen, dass der Speicherplatz für im laufenden Betrieb angelegte und wieder gelöschte Daten im Backend wieder freigegeben wird? Immerhin darf das Backend nicht größer als 2 GB werden, was bei solchen Operationen schnell geschehen kann.
Öffnen wir das Backend ohne das Frontend und komprimieren es nach dem Löschen der Daten mit dem dafür vorgesehen Befehl Komprimieren und Reparieren, erhält es wieder seine ursprüngliche Dateigröße.
Backend beim Schließen komprimieren
Die Vermutung liegt nahe, dass wir nur für das Backend die Option Beim Schließen komprimieren einstellen müssen, damit dieses beim Schließen des Frontends automatisch komprimiert wird.
Dies führt allerdings nicht zum Erfolg. Nach dem Einstellen dieser Option und dem Schreiben und Löschen der Daten über das Frontend erhält das Backend nicht wieder die Größe der Datenbank mit der geleerten Tabelle.
Woran liegt das? Offensichtlich findet beim Zugriff auf das Backend über die Tabellenverknüpfungen kein Öffnen und Schließen der Backenddatenbank in dem Sinne statt, dass dabei die für das Schließen aktivierte Komprimierung ausgelöst wird.
Wir müssen also einen anderen Weg finden, um das Backend zu komprimieren.
Backend per VBA komprimieren
VBA bietet eine Methode zum Komprimieren einer Datenbank. Diese heißt CompactDatabase und erwartet unter anderem die folgenden beiden Parameter:
- Pfad der zu komprimierenden Datenbankdatei
- Pfad der komprimierten Datenbankdatei
Wir können also nicht einfach die Datenbank komprimieren, sondern erstellen eine neue Datenbank, welche der komprimierten Version der Datenbank entspricht. Um diese Datenbank später wieder unter dem ursprünglichen Namen nutzen zu können, benötigen wir also noch ein paar weitere Anweisungen.
Wir haben diese in einer Funktion namens BackendKomprimieren zusammengefasst (siehe Listing 1).
Public Function BackendKomprimieren(strBackendtabelle As String) As Boolean Dim strBackend As String Dim strBackendverzeichnis As String Dim strBackendTemp As String Dim strBackendOld As String Dim strBackendendung As String strBackend = DLookup("Database", "MSysObjects", "Name = ''" & strBackendtabelle & "''") If Not Len(Dir(strBackend)) = 0 Then strBackendverzeichnis = Mid(strBackend, 1, InStrRev(strBackend, "\")) strBackendendung = Mid(strBackend, InStrRev(strBackend, ".")) strBackendTemp = strBackendverzeichnis & "BackendTemp_" & Format(Now, "yyyymmdd_hhnnss") & strBackendendung ''Ohne Kennwortschutz: On Error Resume Next DBEngine.CompactDatabase strBackend, strBackendTemp ''Mit Kennwortschutz: ''DBEngine.CompactDatabase strBackend, strBackendTemp , , , ";pwd=ami" If Err.Number = 3704 Then MsgBox "Das Backend kann nicht komprimiert werden, da noch Tabellen im Frontend geöffnet sind.", _ vbOKOnly + vbExclamation, "Fehler beim Komprimieren" Exit Function End If On Error GoTo 0 strBackendOld = strBackend & "_Old_" & Format(Now, "yyyymmdd_hhnnss") & strBackendendung Name strBackend As strBackendOld Name strBackendTemp As strBackend If Not Len(Dir(strBackend)) = 0 Then Kill strBackendOld BackendKomprimieren = True End If End If End Function
Listing 1: Funktion zum Komprimieren einer Backenddatenbank
Diese Funktion erwartet den Namen einer der Tabellenverknüpfungen als Parameter. In unserem Beispiel lautet der Aufruf also beispielsweise:
Debug.Print BackendKomprimieren("tblTexte")
Dieser Aufruf gibt gleich das Ergebnis der Funktion im Direktbereich aus.
Die Funktion deklariert einige Variablen und ermittelt dann den Pfad zu der zu komprimierenden Backenddatenbank. Dazu nutzt sie die Tatsache, dass der Pfad einer per Verknüpfung eingebundenen Access-Tabelle im Feld Database der Systemtabelle MSysObjects gespeichert wird. Der Pfad landet anschließend in der Variablen strBackend.
Nach einer Prüfung, ob die Backenddatenbank vorhanden ist, ermittelt sie das Verzeichnis, in dem sich die Backenddatenbank befindet, sowie die Dateiendung (in der Regel .accdb), und schreibt beide in die Variablen strBackendverzeichnis und strBackendendung.
In der Variablen strBackendTemp erstellt sie einen neuen Pfad, der aus dem Verzeichnis und einem Dateinamen inklusive der aktuellen Zeit wie in folgendem Beispiel erstellt:
C:\...\BackendTemp_20230529_120406.accdb
Dann erfolgt das eigentliche Komprimieren mit der Methode CompactDatabase der DBEngine-Klasse. Diese komprimiert die Datei aus strBackend in die neue Datei strBackendTemp. Falls das Backend durch ein Kennwort geschützt ist, kann man dieses wie in der auskommentierten nachfolgenden Version des Aufrufs angeben.
Beim Aufruf der Methode CompactDatabase können Fehler auftreten, zum Beispiel der Fehler 3704 (Sie haben versucht, eine Datenbank zu öffnen, die bereits von Benutzer ”[Benutzername]” auf Computer ”[Computername]” geöffnet ist. Versuchen Sie es erneut, wenn die Datenbank verfügbar ist.).
Dieser tritt auf, wenn noch eine der Tabellen des Backends im Frontend geöffnet ist.
In diesem Fall gibt die Funktion eine Meldung aus und bricht die Funktion ab.
War die Komprimierung jedoch erfolgreich, wird zunächst die Originalversion des Backends aus strBackend temporär umbenannt. Der Dateiname lautet dann beispielsweise:
BackendKomprimieren_BE.accdb_Old_20230529_120936.accdb
Anschließend prüft die Funktion, ob unter dem ursprünglichen Namen aus strBackend eine Datenbankdatei vorliegt. In diesem Fall wird das vorherige Backend gelöscht und die Funktion gibt den Wert True als Ergebnis zurück.
Damit erhalten wir also eine Möglichkeit, die Backenddatenbank zu komprimieren, wenn alle Tabellenverknüpfungen auf dieses Backend geschlossen sind.
Nun benötigen wir noch eine Möglichkeit, diese Funktion ähnlich wie bei der Option Beim Schließen komprimieren erst beim Schließen der Datenbank aufzurufen.
Komprimieren per VBA beim Schließen
Dazu können wir eine Methode nutzen, die wir bereits im Beitrag Code beim Schließen der Anwendung ausführen (www.access-im-unternehmen.de/1370) vorgestellt haben. Hier setzen wir ein Formular ein, dass wir beim Öffnen der Datenbank automatisch öffnen und das dann direkt unsichtbar gemacht wird. Dadurch sieht der Benutzer dieses Formular nicht. Es wird jedoch, wenn der Benutzer die Datenbankanwendung beendet, automatisch geschlossen. Hier sorgen wir mit einer Ereignisprozedur für das Ereignis Beim Entladen dafür, dass wir beim Schließen der Datenbank die Funktion BackendKomprimieren aufrufen können.
Das Autoexec-Makro, mit dem wir das noch zu erstellende Formular frmStart öffnen, sieht wie in Bild 3 aus.
Bild 3: Autoexec-Makro zum Öffnen eines Formulars
Das Formular frmStart enthält keine Elemente, aber eine Ereignisprozedur (siehe Bild 4). Dieses wird beim Entladen ausgelöst und führt die folgende Prozedur aus:
Bild 4: Entwurf des Formulars frmStart
Private Sub Form_Unload(Cancel As Integer) BackendKomprimieren "tblTexte" End Sub
Sie soll also lediglich die Funktion BackendKomprimieren für die einzige verknüpfte Tabelle dieser Datenbank aufrufen. Damit sorgt sie dafür, dass beim Schließen der Anwendung die Komprimierung des Backends erfolgt.
Im Mehrbenutzerbetrieb
Wenn mehrere Benutzer die Datenbank verwenden und einer der Benutzer sein Frontend schließt, wird das Backend nicht komprimiert, wenn noch eine seiner Tabellen in einer anderen Datenbank geöffnet ist.
In diesem Fall kommt es auf das übliche Nutzerverhalten an, wie man für eine Komprimierung der Datenbank sorgt. Wenn alle Frontends zu bestimmten Zeitpunkten geschlossen sind, beispielsweise weil nachts niemand mit der Datenbank arbeitet, wird das Backend beim Schließen des letzten geöffneten Frontends komprimiert.
Hier gibt es aber auch noch Fallstricke, denn es kann passieren, dass das Formular frmStart nicht als letztes Formular geschlossen wird. In diesem Fall ist vielleicht noch ein weiteres Formular geöffnet, das an eine der verknüpften Tabellen gebunden ist und somit das Komprimieren des Backends verhindert.
Wenn nicht ausgeschlossen werden kann, dass Benutzer die Anwendung durchgängig geöffnet lassen und dabei auch verknüpfte Tabellen anzeigen, müsste man noch einen Schritt weitergehen und zu bestimmten Zeitpunkten dafür sorgen, dass die Verbindungen zum Backend getrennt werden. Dies würde jedoch den Rahmen des vorliegenden Beitrags sprengen.
Zusammenfassung und Ausblick
Dieser Beitrag zeigt, wie sich das Komprimieren in verschiedenen Szenarien auswirkt und dass ein automatisches Komprimieren des Backends einer Datenbank nicht ohne VBA erfolgen kann.
Dazu muss die Bedingung erfüllt sein, dass aktuell keine Verbindungen zu den Tabellen bestehen.
Downloads zu diesem Beitrag
Enthaltene Beispieldateien:
BackendKomprimieren.accdb
BackendKomprimieren_BE.accdb
BackendKomprimieren_FE.accdb
