Startseite > Tipps & Tricks > Datenbanken komprimieren
Team-Moeller.de - Datenbankentwicklung und mehr ...

Datenbanken komprimieren

Datenbanken komprimieren

Es gibt verschiedene Wege und auch interessante Aspekte wenn es darum geht, Access-Datenbanken zu komprimieren. Einige möchte ich hier vorstellen:


 

Warum komprimieren?

  Access-Datenbanken werden während der Arbeit größer und größer, sie blähen sich auf. Dies passiert auch, wenn Daten oder Objekte gelöscht werden. Dieser Effekt hängt damit zusammen wie Access intern Daten und Objekte verwaltet.
Wenn Sie ein neues Objekt, z.B. eine Tabelle, erstellen, wird diese von Access gespeichert. Wenn Sie Datensätze eingegeben, werden diese ebenfalls von Access gespeichert. Wenn Sie ein Objekt, z.B. eine Tabelle, löschen oder wenn Sie Datensätze löschen, dann werden diese nicht von Access gelöscht. Access markiert diese nur intern als gelöscht. Physikalisch sind die Daten und Objekte immer noch vorhanden, sie werden nur nicht mehr angezeigt.
Um sich endgültig von den als gelöscht markierten Daten und Objekten zu trennen müssen Sie die Datenbank komprimieren. Dabei werden die als gelöscht markierten Objekte und Daten auch physikalisch gelöscht. Der verwendete Speicherplatz wird wieder freigegeben und die Daten werden neu geordnet. Auf diese Weise schrumpft die Datenbankgröße wieder auf das erforderlich Mass.
Über den Menüpunkt "Extras" / "Datenbank-Dienstprogramme" / "Datenbank komprimieren und reparieren..." wird die aktuell geöffnete Datenbank komprimiert.
zurück nach oben

 

  Die Option "Beim Schließen komprimieren"
  Seit der Version Access 2000 kann in den Optionen einer Datenbank eingestellt werden, dass diese beim Schliessen automatisch komprimiert wird. Dies führt zu einer Art "Selbstwartung" der Datenbank. Bei jedem Schliessen wird die Datenbank automatisch komprimiert. Alle als gelöscht markierte Daten und Objekte werden endgültig gelöscht. Die Datenbankgröße sinkt auf das erforderliche Mass.
Die Option findet sich unter "Extras" / "Optionen" auf der Registerkarte "Allgemein". Die Option wird sofort wirksam, d. h. direkt beim Schliessen der Datenbank.
Übrigens: Wenn die Datenbank durch ein Datenbank-Passwort geschützt ist, dann muss dieses nach dem Schliessen (und komprimieren) der Datenbank erneut eingegeben werden. Sonst wird die komprimierte Datenbank nicht gespeichert.
zurück nach oben

 

  AutoCompact per VBA einstellen
  Die Option "Beim Schliessen komprimieren" kann nicht nur über das Menü eingestellt werden. Die Änderung ist auch per VBA möglich. Dazu reicht die folgende Zeile Code:
    Application.SetOption "Auto Compact", True
Mit dem Parameter "True" wird die Option eingeschaltet, mit "False" wird sie ausgeschaltet.

In alten Artikel in den Newsgroups findet man immer wieder Hinweise auf die Option "Auto Compact Percentage".
    Application.SetOption "Auto Compact Percentage", 20
Auch in der Online-Hilfe von Access 2000 wird diese Option genannt. Diese Option soll festlegen, dass die Datenbank beim Schliessen nur dann komprimiert wird, wenn die Datenbankgröße dabei um mindestens x % abnimmt. Leider funktioniert diese Option nicht. (Siehe auch MS Knowledge Base)
zurück nach oben

 

  Verschiedene Strategien für AutoCompact
  Die Nutzung der AutoCompact-Option führt dazu, dass die Datenbank bei jedem Schliessen komprimiert wird. Dies ist für den Anwender lästig und bringt keinen großen Mehrwert. Um die Datenbank trotzdem regelmäßig automatisch beim Schliessen zu komprimieren sind folgenden Strategien möglich:
  1. Komprimierung an bestimmtem Wochentag/Tag des Monats

    Binden Sie den folgenden Code in das Form_Open-Ereignis des Startformulars ein:
      Private Sub Form_Open(Cancel As Integer)
        'Immer am Freitag
        If Weekday(Date, vbMonday) = vbFriday Then
          SetOption "Auto Compact", True
        Else
          SetOption "AutoCompact", False
        End If
      End Sub

    Er sorgt dafür, dass die Anwendung an jedem Freitag komprimiert wird.

    Wenn statt dessen immer am 15. des Monats komprimiert werden soll kann die Prüfung auf den Wochentag durch die folgende Zeile ersetzt werden:

      If Day(Date) = 15 Then 
  2. Komprimierung nach Ablauf einer Zeitspanne

    Für diese Variante muss im Front-End eine Tabelle mit dem Namen "tbl_Compact" vorhanden sein. Darin befindet sich eine Feld "LastTime" vom Datentyp Datum. In dieses Feld schreiben Sie das aktuelle Datum.
    Binden Sie den folgenden Code in das Form_Open-Ereignis des Startformulars ein:
      Private Sub Form_Open(Cancel As Integer)
        Dim dLetzteKompr As Date
        dLetzteKompr = DLookup("LastTime", "tbl_Compact")
        If DateDiff("d", CDate(dLetzteKompr), Date) >= 10 Then
          SetOption "Auto Compact", True
          CurrentDb.Execute "UPDATE tbl_Compact SET LastTime = #" & _
            Format(Date, "m\/d\/yyyy") & "#;"
        Else
          SetOption "Auto Compact", False
        End If
      End Sub 
    Mit diesem Code wird die Datenbank alle 10 Tage komprimiert.
     
  3. Komprimierung nach Größenzuwachs

    Für diese Variante muss im Front-End eine Tabelle mit dem Namen "tbl_Compact" vorhanden sein. Darin befindet sich eine Feld "Groesse" vom Datentyp Long. In dieses Feld schreiben Sie das aktuelle die aktuelle Datenbankgröße.
    Binden Sie den folgenden Code in das Form_Open-Ereignis des Startformulars ein:
      Private Sub Form_Open(Cancel As Integer)
        Dim lngOldSize As Long
        Dim lngNewSize As Long
        Dim fso As Object
        Set fso = CreateObject("Scripting.FileSystemObject")
        lngNewSize = fso.GetFile(CurrentDb.Name).Size
        lngOldSize = DLookup("Groesse", "tbl_Kompr")
        If ((lngNewSize - lngOldSize) / lngOldSize) * 100 >= 20 Then
          SetOption "Auto Compact", True
        Else
          If GetOption("Auto Compact") = True Then
            CurrentDb.Execute "UPDATE tbl_Compact SET Groesse = " & lngNewSize & ";"
          End If
          SetOption "Auto Compact", False
        End If
      End Sub
    Mit diesem Code wird die Datenbank immer dann komprimiert, wenn die Datenbankgröße um 20 % oder mehr gestiegen ist.
     
  4. Komprimierung nach Anzahl Datenbankstarts

    Für diese Variante muss im Front-End eine Tabelle mit dem Namen "tbl_Compact" vorhanden sein. Darin befindet sich eine Feld "Anzahl" vom Datentyp Long. In dieses Feld schreiben Sie den Wert "0".
    Binden Sie den folgenden Code in das Form_Open-Ereignis des Startformulars ein:
      Private Sub Form_Open(Cancel As Integer)
        Dim lngAnz As Long
        lngAnz = DLookup("Anzahl", "tbl_Compact")
        If lngAnz >= 10 Then
          SetOption "Auto Compact", True
          CurrentDb.Execute "UPDATE tbl_Compact SET Anzahl = 0;"
        Else
          SetOption "Auto Compact", False
          CurrentDb.Execute "UPDATE tbl_Compact SET Anzahl = " & lngAnz + 1 & ";"
        End If
      End Sub
    Mit diesem Code wird die Datenbank nach jedem 10. Öffnen komprimiert.
zurück nach oben

 

  Die DBEngine-Methode
  Eine nicht geöffnete Datenbank kann per VBA über die CompactDatabase-Methode des DBEngine-Objekts komprimiert werden:

Public Function fCompactJet(ByVal strDB As String,
                            Optional ByVal strPWD As String = vbNullString) As Boolean
'Fehlerbehandlung definieren
On Error GoTo Err_fCompactJet

  'Variablen deklarieren
  Dim strTemp As String

  'Initialisieren
  fCompactJet = False
  strTemp = strDB & ".bak"

  'Komprimieren
  If strPWD = vbNullString Then
    DBEngine.CompactDatabase strDB, strTemp
  Else
    DBEngine.CompactDatabase strDB, strTemp, , , ";pwd=" & strPWD
  End If

  If Err.Number <> 0 Then Exit Function

  Kill strDB
  Name strTemp As strDB

  'Fertig
  fCompactJet = True

'Ende
Exit_fCompactJet:
  On Error Resume Next
  Exit Function

'Fehlerbehandlung
Err_fCompactJet:
  MsgBox Err.Decripstion, vbCritical, Err.Number
  Resume Exit_fCompactJet

End Function

Die CompactDatabase-Methode komprimiert und repariert die Datenbank. Dabei werden auch die Autowertfelder zurückgesetzt. Über weitere Parameter kann die Datenbank verschlüsselt gespeichert werden. Außerdem kann die Sortierreihenfolge geändert werden.
Leider werden nur die Objekte, die von der Jet-Engine verwaltet werden, berücksichtigt. Dies sind Tabellen, Abfragen und StoredProcedures. Die anderen Objekte kennt Jet nicht und kann Sie deswegen auch nicht komprimieren.
zurück nach oben

 

  PrivDBEngine bei geschützten DB's
  Die CompactDatabase-Methode des DBEngine-Objekts kann nur funktionieren, wenn die zu komprimierende Datenbank mit der selben MDW-Datei geschützt ist, mit der auch die bestehende Access-Instanz geöffnet wurde. Wenn die Datenbank mit einer anderen MDW-Datei geschützt ist, hilft die >PrivDBEngine<:

Public Function fCompactPrivDbEngine(ByVal strDB As String, _
                                     ByVal strSystemDB As String, _
                                     ByVal strUser As String, _
                                     ByVal strPassword As String) As Boolean
'Fehlerbehandlung definieren
On Error GoTo Err_fCompactPrivDbEngine

  'Variablen deklarieren
  Dim pdbe As DAO.PrivDBEngine
  Dim strTemp As String

  'Initialisieren
  fCompactPrivDbEngine = False
  Set pdbe = New PrivDBEngine
  strTemp = strDB & ".temp.mdb"

  'Komprimieren
  With pdbe
    .SystemDB = strSystemDB
    .DefaultUser = strUser
    .DefaultPassword = strPassword
    .CompactDatabase strDB, strTemp
  End With

  If Err.Number <> 0 Then Exit Function

  Kill strDB
  Name strTemp As strDB

  'Fertig
  fCompactPrivDbEngine = True

'Ende
Exit_fCompactPrivDbEngine:
  On Error Resume Next
  Exit Function

'Fehlerbehandlung
Err_fCompactPrivDbEngine:
  MsgBox Err.Decripstion, vbCritical, Err.Number
  Resume Exit_fCompactPrivDbEngine

End Function

Auch diese Fehlerbehandlungsroutine könnte noch ausgebaut werden.

Für die PrivDBEngine gelten die selben Anmerkungen wie für die CompactDatabase-Methode.
zurück nach oben

 

  Der Windows Scripting Host
  Eine Lösung des Problems, dass Access mit der CompactDatabase-Methode nur Tabellen aber nicht die restlichen Objekte komprimiert, ist es Access mit dem Kommandozeilenparameter "/COMPACT" aufzurufen. Hierbei kann der WSH helfen:
Public Function fCompactWSH(ByVal strDB As String) As Boolean
  'Fehlerbehandlung definieren
  On Error GoTo Err_fCompactWSH

  'Variablen deklarieren
  Dim strAccPfad As String
  Dim strShell As String
  Dim oWSH As Object

  'Initialisieren
  fCompactWSH = False
  Set oWSH = CreateObject("WScript.Shell")

  'String für Shell
  strAccPfad = """" & Application.SysCmd(acSysCmdAccessDir)
  strShell = strAccPfad & "msaccess.exe"" """ & strDB & """ /COMPACT"

  'Komprimieren
  oWSH.Run strShell, 0, True

  'Fertig
  fCompactWSH = True

'Ende
Exit_fCompactWSH:
  On Error Resume Next
  Exit Function

'Fehlerbehandlung
Err_fCompactWSH:
  MsgBox Err.Description, vbCritical, Err.Number
  Resume Exit_fCompactWSH

End Function
Die Fehlerbehandlungsroutine könnte natürlich noch ausgebaut werden.
zurück nach oben

 

  Der "geheime" SysCmd-Befehl
  Ein ähnliches Vorgehen ergibt sich, wenn man den "geheimen" SysCmd-Befehl verwendet:
Public Function fCompactSys(strDB As String) As Boolean
'Fehlerbehandlung definieren
On Error GoTo Err_fCompactSys

  'Variablen deklarieren
  Dim appAcc As Access.Application
  Dim strTemp As String

  'Initialisieren
  fCompactSys= False
  Set appAcc = New Access.Application
  strTemp = strDB & ".temp.mdb"

  'Komprimieren
  appAcc.SysCmd 602, strDB, strTemp
  Kill strDB
  Name strTemp As strDB

  'Fertig
  fCompactSys= True

'Ende
Exit_fCompactSys:
  On Error Resume Next
  Set appAcc = Nothing
  Exit Function

'Fehlerbehandlung
Err_fCompactSys:
  MsgBox Err.Description, vbCritical, Err.Number
  Resume Exit_fCompactSys

End Function

Auch diese Fehlerbehandlungsroutine könnte noch ausgebaut werden.

Der SysCmd-Befehl ist zwar bekannt, er ist jedoch von MS nicht dokumentiert. Das bedeutet, dass die Benutzung auf eigene Gefahr hin erfolgt.

Bei meinen Test habe ich festgestellt, dass der Befehl unter Acc 2000 und unter Acc 2002 nicht funktioniert.

zurück nach oben

 

  Menüpunkte fernsteuern
  Basierend auf einem Hinweis aus der FAQ von Karl Donaubauer kann man das Komprimieren auch über einen Menübefehl ausführen:
Public Function fCompactMenu(strDB As String) As Boolean
'Fehlerbehandlung definieren
On Error GoTo Err_fCompactMenu

  'Variablen deklarieren
  Dim appAcc As Access.Application

  'Initialisieren
  fCompactMenu = False
  Set appAcc = New Access.Application
  appAcc.OpenCurrentDatabase strDB

  'Komprimieren
  appAcc.CommandBars("Menu Bar"). _
  Controls("Extras"). _
  Controls("Datenbank-Dienstprogramme"). _
  Controls("Datenbank komprimieren und reparieren..."). _
  accDoDefaultAction

  'Fertig
  appAcc.Quit
  fCompactMenu = True

'Ende
Exit_fCompactMenu:
  On Error Resume Next
  Set appAcc = Nothing
  Exit Function

'Fehlerbehandlung
Err_fCompactMenu:
  MsgBox Err.Description, vbCritical, Err.Number
  Resume Exit_fCompactMenu:

End Function

Auch diese Fehlerbehandlungsroutine könnte noch ausgebaut werden.

Bei dieser Variante wird beim öffnen der Datenbank ggfs. ein vorhandenes AutoExec-Makro gestartet. Wie man dieses durch programmatisches drücken der Shift-Taste verhindert zeigt Dev Ashish. (API: Bypassing Autoexec)

zurück nach oben

 

  Ergänzende Informationen und Links
  Artikel aus der KnowledgeBase:
ACC2000: Auto Compact Percentage String in SetOption Does Not Function as Expected

FAQ von Karl Donaubauer:
http://www.donkarl.com Punkt 6.6 und 6.7

Compactor von Dev Ashish:
http://www.mvps.org/access/modules/mdl0030.htm

AutoExec umgehen:
http://www.mvps.org/access/api/api0068.htm

zurück nach oben

Microsoft MVP - Thomas Möller


06.05.2017:
TM-VBALineNumbers
Version 1.29.01 bereitgestellt.


27.03.2016:
TM VBA-Inspector:
Installationsroutine überarbeitet.


23.05.2014:
TM-RebuildDatabase
Version 1.75 bereitgestellt.


07.12.2014:
TM-DatenKlassenGenerator
Version 3.55 bereitgestellt.


Powered by CMSimple - Template by CMSimple-Styles.com - Autor:Thomas Möller