Compact your database with VBA

Have you experienced the problem with increasing size of your MS Access files? Do you need to compact your databases from time to time, especially when they reach specific file size?  Then we have a solution build in VBA that could be easily implemented to any MS Access database (this code applies to Access Version 2000 and later).

1) Go to Modules iny our main menu, create a new blank module and put the following code into it:

Public Function CompactCurrentFile()
    Dim fs, currentfile, currentsize, filespec
    Dim strProjectPath As String, strProjectName As String
    strProjectPath = Application.CurrentProject.path
    strProjectName = Application.CurrentProject.Name
    filespec = strProjectPath & "\" & strProjectName
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set currentfile = fs.GetFile(filespec)
    currentsize = CLng(currentfile.Size / 1000000)
    If currentsize > 49 Then
    'compact current file
        Application.SetOption ("Auto Compact"), 1
    Else
    'do nothing
        Application.SetOption ("Auto Compact"), 0
    End If
End Function

2) Save the module with any name you want.

3) Now go to your switchboard form and place a new button into it.

4) As soon as you place the enw button in your form a button wizard will appear. Choose "Application" - > Quit Application. This will generate a new button with the code behind it that will close your MS Access DB once it is clicked.

5) Now we need to edit the code behind the button. Double click the button > go to Event > On Click Event. This will open the Visual Basic Editor,

6) Place the following code before the row "Docmd.Quit" statement:

CompactCurrentFile

7) Save the visual Editor and close it

8) Save your form

From now on every time the size of your MS Access file reaches 50 MB limit it will call Compact function upon closing.