Macro 96: Compacting an Access Database from Excel
During your integrated processes, you may routinely increase or decrease the number of records and
tables in your database. As time goes on, you may notice that your Access database gets bigger. This is
because Access does not release file space. All the space needed for the data you move in and out of your
database is held by your Access file, regardless of whether the data is still there. In that light, it’s critical
that you run Compact and Repair on your Access database regularly. Among other things, running
Compact and Repair defragments your database, releasing any unused space and ensuring your database
does not grow to an unmanageable size. Office automation enables you to Compact and Repair your
databases right from code.
How it works
When you compact and repair an Access database manually, it seems as though Access compresses
your original database; this is not the case. Access is really doing nothing more than creating a copy
of your Access database (minus the empty file space) and deleting the old file.
This macro essentially mimics those actions in order to programmatically Compact and Repair an
Access application.
Note that in order to use this code, you need to set a reference to the Microsoft Access Object
Library. To do so, open the VBE in Excel and select Tools➜References. The Reference dialog box
opens. Scroll down until you find the entry Microsoft Access XX Object Library, where the XX is your
version of Access. Select the check box next to the entry.
Sub Macro96()
‘Step 1: Declare your variables
Dim OriginalFile As String
Dim BackupFile As String
Dim TempFile As String
‘Step 2: Identify the target database assign file paths
OriginalFile = “C:\Temp\MyDatabase.accdb”
BackupFile = “C:\Temp\MyDatabaseBackup.accdb”
TempFile = “C:\Temp\MyDatabaseTemporary.accdb”
‘Step 3: Make a backup copy of database
FileCopy OriginalFile, BackupFile
‘Step 4: Perform the compact and repair
DBEngine.CompactDatabase OriginalFile, TempFile
‘Step 5: Delete the old database
Kill OriginalFile
‘Step 6: Rename the temporary database to the old database name
Name TempFile As OriginalFile
End Sub
1. Step 1 declares three string variables that hold filenames.
2. Step 2 then assigns each of the string variables a filename. The OriginalFile variable is
assigned the file path and name of the target database. The BackupFile variable is
assigned the file path and name of a backup file we will create during this procedure. The
TempFile variable is assigned the file path and name of a temporary file we create during
this procedure.
3. Step 3 uses the FileCopy function to make a backup of the OriginalFile (the target
database). Although this step is not necessary for the Compact and Repair procedure, it’s
generally a good practice to make a backup of your database before running this level of
VBA on it.
4. Step 4 executes the Compact and Repair, specifying the original database and specifying the
file path of the temporary database.
5. At this point, you have two copies of your database: the original database and a second data-
base, which is a copy of your original without the empty file space. Step 5 deletes the original
database, leaving you with the copy.
6. Step 6 simply renames the temporary file, giving it the name of your original database. This
leaves you with a database that is compact and optimized.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code.
