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
Nicholas Workshop Newsletter
Join the newsletter to receive the latest updates in your inbox.