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()
	CloseAllForms
    SendKeys "%tdc"
End Sub

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
        CloseAllForms
        SendKeys "%tdc"
    End If
    End Sub

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  
        Next intIndex  
End Sub  

After all, you can just add the commands to the click event of your button.

Private Sub CompactButton_Click()  
    CompactDatabaseNoticeable  
End Sub