Compact and repair database in Access through VBA
Normally, we need to go through bunch of clicks to do one action in MS Access. For example, “Compact and Repair Database”. This function can compact your database and make the file smaller. If you never used this, it may take some time to find it. Try it. LOL.
But here I would like to show you how to program it and open this function by click on a button by VBA. Like the screenshot. To achieve that, you should add a module (so that it is easier to be resused), and add the following codes.
Public Sub CompactDatabase()
Alternatively, you can add a question box to prompt comfirmation from user.
Public Sub CompactDatabaseNoticeable()
If MsgBox( _
"Are you sure to compact this database?" & vbNewLine & _
"There is good to compact the database always with no harm!" & vbNewLine & _
"But this will restart this analyser.", _
vbYesNoCancel + vbExclamation) = vbYes Then
Also, you need to add a function to close all the forms so that your applications can focus on the main database window and perform the action.
Public Sub CloseAllForms()
Dim intIndex As Integer
For intIndex = Forms.Count – 1 To 0 Step -1
DoCmd.Close acForm, Forms(intIndex).name, acSaveNo
After all, you can just add the commands to the click event of your button.
Private Sub CompactButton_Click()