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.

[sourcecode language=”vb”]
Public Sub CompactDatabase()
CloseAllForms
SendKeys "%tdc"
End Sub
[/sourcecode]

Alternatively, you can add a question box to prompt comfirmation from user.

[sourcecode language=”vb”]
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
[/sourcecode]

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.

[sourcecode language=”vb”]
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
[/sourcecode]

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

[sourcecode language=”vb”]
Private Sub CompactButton_Click()
CompactDatabaseNoticeable
End Sub
[/sourcecode]

One response to “Compact and repair database in Access through VBA

Leave a Reply

%d bloggers like this: