Compact and Repair Database

MS-Access files tend to grow over time, for a variety of reasons such as

• adding many new records
• large number of user edits
• running action queries (insert, update, delete)
• design changes, especially to forms & reports
• temp tables that are emptied & repopulated
• using the Name AutoCorrect options
• Access replication
• WorkGroup Security with encryption

The theoretical design limit of an Access mdb file is 2 GB, but as you approach 80-100 MB the performance & stability will begin to suffer; above 250 MB the file can become barely useable. This is especially significant when the file is located on the network & you have multiple users accessing that file. Plus, many anti-virus programs will aggressively scan mdb files.

NOTE: It is essential for all users to exit the database, and to make a backup copy of the file before performing the following...

One solution is to do regular maintenance on the database. In Access versions thru 2003, you can use

Tools ~ Database Utilities ~ Compact & Repair Database

which will in most cases shrink the file considerably. The typical reduction can be 10-20%, but can much greater if the Compact has not been done for a long time. In fact, I've seen one case where there was 100:1 reduction for a file that had never been compacted.

An alternate solution is to turn on the option for Auto-Compact, using

Tools ~ Options ~ General, and check the box Compact on Close

...however this option may crash the app if you are running on Terminal Server and/or using Access replication and/or the Access runtime version.

So, how often to perform this task? Good question. It depends on the list of reasons above. You could actually write some code to check the file size, compare it to some arbitrary limit, and then alert the user if the size exceeds that limit. Here is some sample code to check the file size:

Function GetMyFileSize() As Long
    ' this requires a reference to
    '   Microsoft Scripting Runtime
    '   c:\windows\system32\scrrun.dll
    Dim fso As FileSystemObject
    Dim oFolder As Folder, oFile As File
    '
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set oFolder = fso.GetFolder(CurrentProject.Path)
    Set oFile = oFolder.Files(CurrentProject.Name)
    GetMyFileSize = oFile.Size
    Set oFile = Nothing
    Set oFolder = Nothing
    Set fso = Nothing
End Function