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
Advertisements
1 comment
  1. Alaa said:

    Thanks for this post .. helps me a lot

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: