Streamlining Code Database Compacting Utility ~ LEARN MS-ACCESS TIPS AND TRICKS

Streamlining Form Module Code in Standalone Class Module.

The Database Compact/Repair Utility.

The 'Compact on Close' Option in Microsoft Access. When enabled under File --> Current Database --> Application Options, this feature automatically compacts the database every time you open and close it. Additionally, you can manually select the Compact/Repair option from the File menu to Compact and Repair the active database. If you need to compact an external file, you can choose the Compact and Repair option from the Database Tools Menu.

If you prefer to refrain from performing the Compact and Repair process daily, you can run the Compact/Repair Utility for multiple Databases together periodically, such as weekly or fortnightly. In this case, you can conveniently select those Databases from the Disk and add them to a ListBox, as illustrated in the form image below.

The ListBox is enabled with the Multi-select option and you may select the required databases from the list and run from the Compact/Repair Command Button. The Selected Databases will be Compacted individually and their File Size will be updated in the second column of the ListBox in Kilobytes.

The FileBrowser Control.

The 'Add Databases' Command Button opens the File Browser Dialog Control and you can select one or more Databases from your disk and add them to the ListBox.

The Compact/Repair Function Running.

The selected files from the Disk are added to the DirectoryList Table, the Source Table of the ListBox. As per your Compact/Repair schedule, you may open this Compact/Repair Utility, select the required files from the ListBox then Click on the 'Compact/Repair' Command Button.

Preparing for Compact/Repair.

In the Compact/Repair Utility Program, the Database is first backed up to a temporary location. The default Backup Path shown in the TextBox above the Command Buttons is D:\Tmp\. It is defined in the Default Value Property of the TextBox and used by the Compact/Repair Utility Program.

If you would like to take any Database Backup to a different location then change the path, like C:\Backup\ in the TextBox before running the Compact/Repair option.

If you prefer a different location permanently then open the Form in Design View, display the Property Sheet of the TextBox, and change the Default Value Property to your preferred location like D:\Backup\ and see that the last character in the path is a backslash.

Note: When the same database is compacted again the old backup file will be replaced with the new one. Till that time the Backup File will remain safe in that location.

The Compacting Procedure goes through the following steps:

The Source File is Copied to the backup location, to keep a copy of the Database safe, before the Source file is Compacted.
The DBEngine.CompactDatabase() command is executed to perform Compact and Repair operations on the database and repairs the data if Database corruption is detected. In the event of data corruption, there is a potential risk of data loss, and the specific information regarding the errors encountered is preserved in the System Table MSysCompactErrors. To mitigate such situations, it is advisable to restore the data from previously created database backups, if available.
All objects from the source database, including both user-created and system objects, are transferred into a new database. This new temporary database name is "db1.accdb" (or "db1.mdb" depending on the file format) and is located in the designated backup path: D:\tmp\db1.accdb. The replication process excludes the system's temporary work files, ensuring a comprehensive transfer of objects while omitting non-essential temporary data.
Deletes the Source File from its home location.
The Compacted D:\tmp\db1.accdb file is transferred to the home location with its original Database name.

Note: The Access System goes through the same procedure, when you run the Compacting operation directly from the Access System, except the Database Backup procedure.

Streamlined VBA Coding in Standalone Class Module.

Having gained insights into the utility highlighted in the previous introduction, it is now opportune to explore the streamlined event subroutine coding procedure implemented in this specific project. The necessary VBA codes for Event Subroutines are meticulously crafted within a standalone class module, enhancing code maintenance and debugging processes. This organized code structure within the standalone Class Module facilitates easy transportation to other projects, safeguarding valuable work from being entangled with less critical code in form modules. By adopting this streamlined coding approach, the practice of reusing identical code segments across controls of the same type within the form is encouraged, eliminating the need for duplicative coding efforts.

The Command Button Wrapper Class.

There exists a singular Wrapper Class designated for the Command Button Controls, along with an additional Class Module dedicated to the Intermediary or Interface Class. The Interface Class is responsible for generating instances of the Command Button Wrapper Class and facilitating the Command Button Click Events. Customarily, a Collection Object is employed to manage all Command Button Wrapper Class instances. This arrangement allows for the monitoring and capturing of Command Button Click Events triggered within the Compact_Repair Form.

The Command Button Wrapper Class VBA Code.

Option Compare Database
Option Explicit

Private cmdfrm As Form
Private WithEvents cmd As CommandButton

Private strPath As String
Private bkupPath As String
Dim lst As ListBox
Dim lstcount As Integer
Dim xtn As String

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Database Compact/Repair Utility
'Author: a.p.r. pillai
'Date  : 20/02/2024
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------

'Form's Property GET/SET Procedures
Public Property Get cmd_Frm() As Form
    Set cmd_Frm = cmdfrm
End Property

Public Property Set cmd_Frm(ByRef cfrm As Form)
    Set cmdfrm = cfrm
End Property

'Command Button Property GET/SET Procedures
Public Property Get c_cmd() As CommandButton
    Set c_cmd = cmd
End Property

Public Property Set c_cmd(ByRef pcmd As CommandButton)
    Set cmd = pcmd
    
    Call DefaultPath
    
End Property

'The Click Event Subroutines
Private Sub cmd_Click()
On Error GoTo cmd_Click_Err
Select Case cmd.Name
  Case "cmdQuit"
    If MsgBox("Close Compact_Repair Form?", vbOKCancel + vbQuestion, "cmd_Click") = vbOK Then
        DoCmd.Close acForm, cmdfrm.Name
        Exit Sub
    End If

    Case "cmdFileDialog"
        Call FileDialog 'Display selected Path & files
        cmdfrm.dbList.Requery
        
    Case "cmdCompact"
        Call DBPrepare
        
    Case "cmdDelete"
        Call DBDelete
        
End Select

cmd_Click_Exit:
Exit Sub

cmd_Click_Err:
MsgBox Err & " : " & Err.Description, , "cmd_Click()"
Resume cmd_Click_Exit
End Sub

Private Sub DBDelete()
'Delete the selected Items from the DirectoryList Table
Dim delCount As Integer
Dim j As Integer
Dim k As Integer
Dim DB As Database
Dim dbName As String
Dim msg As String
Dim Rst As Recordset
Dim opt As Integer

On Error GoTo DBDelete_Err
opt = 0

msg = "1. Delete Selected." & vbCr & vbCr _
& "2. Delete All from List." & vbCr & vbCr _
& "3. Cancel Deletion."
While opt < 1 Or opt > 3
    opt = InputBox(msg, "Select Option.", 3)
Wend
Select Case opt
    Case 1
        GoTo Selected
    Case 2
      msg = "Empty the Database List...?"
      If MsgBox(msg, vbYesNo + vbCritical, "DeleteList()") = vbNo Then
        Exit Sub
      Else
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "DeleteAll_ListQ"
        DoCmd.SetWarnings True
        cmdfrm.dbList.Requery
        cmdfrm.cmdDelete.eabled = False
        Exit Sub
      End If
    Case 3
        Exit Sub
End Select

Selected:
delCount = CheckList()
If delCount > 0 Then
    msg = "Delete " & delCount & " Items." & vbCr & vbCr & "Proceed...?"
    If MsgBox(msg, vbYesNo, "DBDelete()") = vbNo Then
        Exit Sub
    End If
    
    Set DB = CurrentDb
    Set Rst = DB.OpenRecordset("DirectoryList", dbOpenDynaset)
    Set lst = cmdfrm.dbList
    
For j = 0 To lstcount
    If lst.Selected(j) Then
      dbName = lst.Column(0, j)
      Rst.FindFirst "Path = '" & dbName & "'"
      If Not Rst.NoMatch Then
        Rst.Delete
        Rst.Requery
      End If
    End If
Next
Rst.Close
Set Rst = Nothing
Set DB = Nothing

lst.Requery
    MsgBox delCount & " Item(s) Deleted From List.", , "DBDelete()"
Else
    MsgBox delCount & " Item(s) Selected for Deletion!", , "DBDelete()"
End If

DBDelete_Exit:
Exit Sub

DBDelete_Err:
MsgBox Err & " : " & Err.Description, , "DBDelete()"
Resume DBDelete_Exit
End Sub

Private Sub DBPrepare()
'Preparatory Procedure for Compacting
'the selected Databases individually
Dim xselcount As Integer
Dim dbName As String
Dim ldbName As String
Dim strTmp As String
Dim i As Integer
Dim j As Integer
Dim timr As Double
Dim fs, f
Dim lockfile As String
Dim msg As String

bkupPath = cmdfrm!BackupPath
'create a Backup Folder
On Error Resume Next
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(bkupPath)
    If Err = 76 Or Err > 0 Then
       Err.Clear
       fs.createfolder (bkupPath)
    End If

On Error GoTo DBPrepare_Err

'Remove existing workfiles from backup location
xselcount = CheckList()
If xselcount = 0 Then
msg = "Select Database(s) from List for Compacting!"
MsgBox msg, , "DBPrepare()"
    Exit Sub
End If

'Ensure selected database is not active
msg = "Ensure that Selected Databases are not in Use. " _
& vbCrLf & "Proceed...?"

If MsgBox(msg, vbYesNo + vbDefaultButton2 + vbQuestion, _
"DBPrepare()") = vbNo Then
   Exit Sub
End If

'Check the selected database is active or not
'if inactive then submit it to DBCompact() Program.
For j = 0 To lstcount
    If lst.Selected(j) Then
       dbName = Trim(lst.Column(0, j))
       i = InStrRev(dbName, ".")
       xtn = Mid(dbName, i) 'extract extension
       lockfile = IIf(xtn = ".mdb", "ldb", "laccdb")
       ldbName = Left(dbName, i)
       ldbName = ldbName & lockfile 'for checking the presense of lock file.
       If Len(Dir(ldbName)) > 0 Then 'database is active
          MsgBox "Database: " & dbName & vbCrLf & "is active. Skipping to the Next in list."
          GoTo nextstep
       End If

    'Prepare for Compacting and to display the status messages.
       msg = "Compact/Repair: " & dbName & vbCrLf & "Proceed...?"
       If MsgBox(msg, vbQuestion + vbDefaultButton2 + vbYesNo, "DBPrepare()") = vbYes Then
            cmdfrm.lblNote.Visible = False
            cmdfrm.lblStat.Caption = "Working, Please wait..."
            DoEvents

    Call DBCompact(dbName) 'Run Compacting

            cmdfrm.lblStat.Caption = ""
            DoEvents

nextstep:
            Sleep 5
        End If
    End If
Next

msg = "Selected Database(s) Compacted Successfully."
MsgBox msg, , "DBPrepare()"

    Sleep 3

cmdfrm.lblNote.Visible = True
cmdfrm.lblStat.Caption = ""


strTmp = bkupPath & "db1" & xtn 'Delete the temporary file
Call KillTempFile(strTmp)

Set fs = Nothing
Set f = Nothing
Set lst = Nothing

DBPrepare_Exit:
Exit Sub

DBPrepare_Err:
MsgBox Err.Description, , "DBPrepare()"
Resume DBPrepare_Exit
End Sub

Private Sub DBCompact(ByVal strdb As String)
'Compact/Repair Database received as Parameter
Dim t As Long
Dim xdir As String
Dim strbk As String
Dim strTmp As String
Dim tmp As String
Dim chkFile As String
Dim msg As String

On Error GoTo dbCompact_Err

tmp = cmdfrm!BackupPath

strTmp = tmp & "db1" & xtn

chkFile = strTmp
Call KillTempFile(chkFile)

t = InStrRev(strdb, "\")
If t > 0 Then
   strbk = Mid(strdb, t + 1)
End If
strbk = tmp & strbk

chkFile = strbk
Call KillTempFile(chkFile)

'Make a Copy in d:\tmp folder for safe keep
msg = "Taking Backup of " & strdb & vbCrLf _
& "to " & tmp
cmdfrm.lblMsg.Caption = msg
DoEvents

'Take a Backup of Original File to the Backup Location
   FileCopy strdb, strbk

msg = "Transferring Objects from " & strdb & vbCrLf _
& "to " & tmp & "db1" & xtn
cmdfrm.lblMsg.Caption = msg
DoEvents

'https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/dbengine-compactdatabase-method-dao
'Compact Database to D:\tmp\db1.accdb
   DBEngine.CompactDatabase strdb, strTmp
   
' Delete uncompacted Database and Copy Compacted db1.mdb with
' the Original Name
msg = "Creating " & strdb & " from " & tmp & "db1" & xtn
cmdfrm.lblMsg.Caption = msg
DoEvents

'Delete uncompacted file
chkFile = strdb
Call KillTempFile(chkFile)

'Create Compacted File with its original name in its home location
    DBEngine.CompactDatabase strTmp, strdb

msg = strdb & " Compacted/Repaired Successfully."

cmdfrm.lblMsg.Caption = msg
DoEvents

Call dbListUpdate(strdb) 'Update the DirectoryList Table

dbCompact_Exit:
Exit Sub

dbCompact_Err:
MsgBox Err & " : " & Err.Description, , "dbCompact()"
Resume dbCompact_Exit
End Sub

Private Function CheckList() As Integer
'Take selected items Count
Dim k As Integer
Dim xcount As Integer

On Error GoTo CheckList_Err

Set lst = cmdfrm.dbList
lstcount = DCount("*", "DirectoryList")

xcount = 0
For k = 0 To lstcount
If lst.Selected(k) Then
    xcount = xcount + 1
End If
Next

If xcount = 0 Then
   MsgBox "No Database(s)Selected."
   Exit Function
End If

CheckList = xcount

CheckList_Exit:
Exit Function

CheckList_Err:
MsgBox Err & ": " & Err.Description, , "CheckList()"
Resume CheckList_Exit
End Function

Private Sub dbListUpdate(ByVal cmpPath As String)
'Update the File Size of the Database after Compacting
On Error GoTo dbListUpdate_Err
Dim sPath As String
Dim i As Variant
Dim DB As Database
Dim Rst As Recordset
Set DB = CurrentDb
Set Rst = DB.OpenRecordset("DirectoryList", dbOpenDynaset)

Rst.MoveFirst
Rst.FindFirst "Path = '" & cmpPath & "'"
If Not Rst.NoMatch Then
    sPath = Rst!Path
    Rst.Edit
    Rst!FileLengthKB = FileLen(sPath) / 1024 'Db size after compacting
    Rst.Update
End If

Rst.Close
cmdfrm.dbList.Requery

dbListUpdate_Exit:
Set Rst = Nothing
Set DB = Nothing
Exit Sub

dbListUpdate_Err:
MsgBox Err & ": " & Err.Description, , "dbListUpdate()"
Resume dbListUpdate_Exit
End Sub

Private Sub DefaultPath()
Dim strLoc As String
'Default path for CommonDialog Control
   strLoc = CurrentProject.Path & "\*.accdb"
   strPath = strLoc 'Assign to Global Variable strPath
   
End Sub

Private Sub FileDialog()
On Error GoTo cmdFileDialog_Err

'Requires reference to Microsoft Office 12.0 Object Library.
Dim fDialog As Office.FileDialog
Dim DB As DAO.Database
Dim Rst As DAO.Recordset
Dim defPath As String
Dim varFile As Variant
Dim strfiles As String

   'Set up the File Dialog.
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
   With fDialog
      'Allow user to make multiple selections of disk files.
      .AllowMultiSelect = True
      .InitialFileName = Dir(strPath)
      .InitialView = msoFileDialogViewDetails
      'Set the title of the dialog box.
      .Title = "Please select one or more files"

      'Clear out the current filters, and add our own.
      .Filters.Clear
      .Filters.Add "Access Databases", "*.mdb; *.accdb"
      .Filters.Add "Access Projects", "*.adp"
      .Filters.Add "All Files", "*.*"
      .FilterIndex = 1
      '.Execute
      'Show the dialog box. If the .Show method returns True, the
      'user picked at least one file. If the .Show method returns
      'False, the user clicked Cancel.
    If .Show = True Then
        Set DB = CurrentDb
        Set Rst = DB.OpenRecordset("DirectoryList", dbOpenDynaset)
        'Add all selected files to the DirectoryList Table
        defPath = ""
      For Each varFile In .SelectedItems
         If defPath = "" Then
            defPath = Left(varFile, InStrRev(varFile, "\"))
            defPath = defPath & "*.*"
            strPath = defPath
         End If
            Rst.AddNew
            Rst![Path] = varFile
            Rst![FileLengthKB] = FileLen(varFile) / 1024
            Rst.Update
      Next
            cmdfrm.cmdDelete.Enabled = True
        Else
            MsgBox "You clicked Cancel in the file dialog box."
        End If
      
   End With

cmdFileDialog_Exit:
Exit Sub

cmdFileDialog_Err:
MsgBox Err & " : " & Err.Description, , "cmdFileDialog_Click()"
Resume cmdFileDialog_Exit
End Sub

Private Sub KillTempFile(ByVal filename As String)
On Error GoTo KillTempFile_Err
'Manage Temporary Files
    If Len(Dir(filename)) > 0 Then
        Kill filename
    End If

KillTempFile_Exit:
Exit Sub

KillTempFile_Err:
MsgBox Err & ": " & Err.Description, , "KillTempFile()"
Resume KillTempFile_Exit
End Sub

The Command Button Wrapper Class starts with the usual Properties the Form Object and Command Button Control declarations. The CommandButton Control is declared and qualified with the Keyword WithEvents for capturing the Click Events when Fired from the Form.

A few local Variables are also declared in the global area of the Class Module followed by the Form and Command Button Get/Set Property Procedures.

Despite four Command Buttons on the Form, a single Click Event Subroutine within the Command Button Wrapper Class suffices. This streamlined approach enables the capture of all four Command Button Clicks within the same Event Subroutine, allowing for the execution of their respective Event Procedures. This efficiency is achievable through the implementation of streamlined Event Procedure coding.

When examining the Event Subroutine Code in order of priority, the initial step involves adding the databases slated for the Compact/Repair procedure to the ListBox. This is accomplished through the Click Event of the Command Button labeled 'Add Databases,' with the name 'CmdFileDialog.' The Click Event, in turn, invokes the FileDialog() Subroutine. The Code Segment is given below:

Private Sub FileDialog()
On Error GoTo cmdFileDialog_Err

'Requires reference to Microsoft Office 12.0 Object Library.
Dim fDialog As Office.FileDialog
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim defPath As String
Dim varFile As Variant
Dim strfiles As String

   'Set up the File Dialog.
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
   With fDialog
      'Allow user to make multiple selections of disk files.
      .AllowMultiSelect = True
      .InitialFileName = Dir(strPath)
      .InitialView = msoFileDialogViewDetails
      'Set the title of the dialog box.
      .Title = "Please select one or more files"

      'Clear out the current filters, and add our own.
      .Filters.Clear
      .Filters.Add "Access Databases", "*.mdb; *.accdb"
      .Filters.Add "Access Projects", "*.adp"
      .Filters.Add "All Files", "*.*"
      .FilterIndex = 1
      '.Execute
      'Show the dialog box. If the .Show method returns True, the
      'user picked at least one file. If the .Show method returns
      'False, the user clicked Cancel.
    If .Show = True Then
        Set db = CurrentDb
        Set rst = db.OpenRecordset("DirectoryList", dbOpenDynaset)
        'Add all selected files to the DirectoryList Table
        defPath = ""
      For Each varFile In .SelectedItems
         If defPath = "" Then
            defPath = Left(varFile, InStrRev(varFile, "\"))
            defPath = defPath & "*.*"
            strPath = defPath
         End If
            rst.AddNew
            rst![Path] = varFile
            rst![FileLengthKB] = FileLen(varFile) / 1024
            rst.Update
      Next
            cmdfrm.cmdDelete.Enabled = True
        Else
            MsgBox "You clicked Cancel in the file dialog box."
        End If
      
   End With

cmdFileDialog_Exit:
Exit Sub

cmdFileDialog_Err:
MsgBox Err & " : " & Err.Description, , "cmdFileDialog_Click()"
Resume cmdFileDialog_Exit
End Sub

This is the same Office.FileDialog Control (the File Browser Control) and Program we used in the earlier Episode with the Title External Files' List in Hyperlinks published earlier. If you click on this link you will be directed to the specific part of the Page that gives its function details.

In this scenario, we utilize the DirectoryList Table to store the databases selected from the disk, which subsequently populate the ListBox on the Form. The table encompasses two fields: Path and FileLengthKB. The former accommodates the full pathname of the database, while the latter calculates the file size in kilobytes when added to the table. Following the Compact/Repair operations, this table undergoes updates to reflect the altered file sizes.

Users have the flexibility to select one or more databases from the list and initiate the Compact/Repair process by clicking on the designated Command Button. This action triggers the execution of the DBPrepare() Subroutine, which in turn identifies the selected database(s) in the ListBox and passes them to the DBCompact() Subroutine for the Compact/Repair operation.

The DBPrepare() Subroutine VBA Code.

Private Sub DBPrepare()
'Preparatory Procedure for Compacting
'the selected Databases individually
Dim xselcount As Integer
Dim dbName As String
Dim ldbName As String
Dim strTmp As String
Dim i As Integer
Dim j As Integer
Dim timr As Double
Dim fs, f
Dim lockfile As String
Dim msg As String

bkupPath = cmdfrm!BackupPath
'create a Backup Folder
On Error Resume Next
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(bkupPath)
    If Err = 76 Or Err > 0 Then
       Err.Clear
       fs.createfolder (bkupPath)
    End If

On Error GoTo DBPrepare_Err

'Remove existing workfiles from backup location
xselcount = CheckList()
If xselcount = 0 Then
msg = "Select Database(s) from List for Compacting!"
MsgBox msg, , "DBPrepare()"
    Exit Sub
End If

'Ensure selected database is not active
msg = "Ensure that Selected Databases are not in Use. " _
& vbCrLf & "Proceed...?"

If MsgBox(msg, vbYesNo + vbDefaultButton2 + vbQuestion, _
"DBPrepare()") = vbNo Then
   Exit Sub
End If

'Check the selected database is active or not
'if inactive then submit it to DBCompact() Program.
For j = 0 To lstcount
    If lst.Selected(j) Then
       dbName = Trim(lst.Column(0, j))
       i = InStrRev(dbName, ".")
       xtn = Mid(dbName, i) 'extract extension
       lockfile = IIf(xtn = ".mdb", "ldb", "laccdb")
       ldbName = Left(dbName, i)
       ldbName = ldbName & lockfile 'for checking the presense of lock file.
       If Len(Dir(ldbName)) > 0 Then 'database is active
          MsgBox "Database: " & dbName & vbCrLf & "is active. Skipping to the Next in list."
          GoTo nextstep
       End If

    'Prepare for Compacting and to display the status messages.
       msg = "Compact/Repair: " & dbName & vbCrLf & "Proceed...?"
       If MsgBox(msg, vbQuestion + vbDefaultButton2 + vbYesNo, "DBPrepare()") = vbYes Then
            cmdfrm.lblNote.Visible = False
            cmdfrm.lblStat.Caption = "Working, Please wait..."
            DoEvents

    Call DBCompact(dbName) 'Run Compacting

            cmdfrm.lblStat.Caption = ""
            DoEvents

nextstep:
            Sleep 5
        End If
    End If
Next

msg = "Selected Database(s) Compacted Successfully."
MsgBox msg, , "DBPrepare()"

    Sleep 3

cmdfrm.lblNote.Visible = True
cmdfrm.lblStat.Caption = ""


strTmp = bkupPath & "db1" & xtn 'Delete the temporary file
Call KillTempFile(strTmp)

Set fs = Nothing
Set f = Nothing
Set lst = Nothing

DBPrepare_Exit:
Exit Sub

DBPrepare_Err:
MsgBox Err.Description, , "DBPrepare()"
Resume DBPrepare_Exit
End Sub

The above DBPrepare() Subroutine picks the User selected items individually and passes them to the actual Compacting Subroutine DBCompact() below for Compact/Repair operations and restoring the Compacted Database to its home location.

The DBCompact Subroutine VBA Code.

Private Sub DBCompact(ByVal strdb As String)

'Compact/Repair Database received as Parameter
Dim t As Long
Dim xdir As String
Dim strbk As String
Dim strTmp As String
Dim tmp As String
Dim chkFile As String
Dim msg As String

On Error GoTo dbCompact_Err

tmp = cmdfrm!BackupPath

strTmp = tmp & "db1" & xtn

chkFile = strTmp
Call KillTempFile(chkFile)

t = InStrRev(strdb, "\")
If t > 0 Then
   strbk = Mid(strdb, t + 1)
End If
strbk = tmp & strbk

chkFile = strbk
Call KillTempFile(chkFile)

'Make a Copy in d:\tmp folder for safe keep
msg = "Taking Backup of " & strdb & vbCrLf _
& "to " & tmp
cmdfrm.lblMsg.Caption = msg
DoEvents

'Take a Backup of Original File to the Backup Location
   FileCopy strdb, strbk

msg = "Transferring Objects from " & strdb & vbCrLf _
& "to " & tmp & "db1" & xtn
cmdfrm.lblMsg.Caption = msg
DoEvents

'https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/dbengine-compactdatabase-method-dao
'Compact Database to D:\tmp\db1.accdb
   DBEngine.CompactDatabase strdb, strTmp
   
' Delete uncompacted Database and Copy Compacted db1.mdb with
' the Original Name
msg = "Creating " & strdb & " from " & tmp & "db1" & xtn
cmdfrm.lblMsg.Caption = msg
DoEvents

'Delete uncompacted file
chkFile = strdb
Call KillTempFile(chkFile)

'Create Compacted File with its original name in its home location
    DBEngine.CompactDatabase strTmp, strdb

msg = strdb & " Compacted/Repaired Successfully."

cmdfrm.lblMsg.Caption = msg
DoEvents

Call dbListUpdate(strdb) 'Update the DirectoryList Table

dbCompact_Exit:
Exit Sub

dbCompact_Err:
MsgBox Err & " : " & Err.Description, , "dbCompact()"
Resume dbCompact_Exit
End Sub

There are three other small supporting Subroutines called from both the DBPrepare() and DBCompact() Subroutines.

The CheckList() Subroutine.

Private Function CheckList() As Integer
'Take selected items Count
Dim k As Integer
Dim xcount As Integer

On Error GoTo CheckList_Err

Set lst = cmdfrm.dbList
lstcount = DCount("*", "DirectoryList")

xcount = 0
For k = 0 To lstcount
If lst.Selected(k) Then
    xcount = xcount + 1
End If
Next

If xcount = 0 Then
   MsgBox "No Database(s)Selected."
   Exit Function
End If

CheckList = xcount

CheckList_Exit:
Exit Function

CheckList_Err:
MsgBox Err & ": " & Err.Description, , "CheckList()"
Resume CheckList_Exit
End Function

The above Subroutine checks whether any Item is selected in the ListBox and takes its count when the 'Compact/Repair' or 'Delete from List' Command Button is Clicked. If found selected then the selected operation is performed.

The dbListUpdate() Subroutine VBA.

Private Sub dbListUpdate(ByVal cmpPath As String)
'Update the File Size of the Database after Compacting
On Error GoTo dbListUpdate_Err
Dim sPath As String
Dim i As Variant
Dim DB As Database
Dim Rst As Recordset
Set DB = CurrentDb
Set Rst = DB.OpenRecordset("DirectoryList", dbOpenDynaset)

Rst.MoveFirst
Rst.FindFirst "Path = '" & cmpPath & "'"
If Not Rst.NoMatch Then
    sPath = Rst!Path
    Rst.Edit
    Rst!FileLengthKB = FileLen(sPath) / 1024 'Db size after compacting
    Rst.Update
End If

Rst.Close
cmdfrm.dbList.Requery

dbListUpdate_Exit:
Set Rst = Nothing
Set DB = Nothing
Exit Sub

dbListUpdate_Err:
MsgBox Err & ": " & Err.Description, , "dbListUpdate()"
Resume dbListUpdate_Exit
End Sub

This Program is Called from the DBCompact() Subroutine to update the File Size in Kilobytes in the ListBox after Compacting the Database.

The DBDelete() Subroutine.

Private Sub DBDelete()
'Delete the selected Items from the DirectoryList Table
Dim delCount As Integer
Dim j As Integer
Dim k As Integer
Dim DB As Database
Dim dbName As String
Dim msg As String
Dim Rst As Recordset
Dim opt As Integer

On Error GoTo DBDelete_Err
opt = 0

msg = "1. Delete Selected." & vbCr & vbCr _
& "2. Delete All from List." & vbCr & vbCr _
& "3. Cancel Deletion."
While opt < 1 Or opt > 3
    opt = InputBox(msg, "Select Option.", 3)
Wend
Select Case opt
    Case 1
        GoTo Selected
    Case 2
      msg = "Empty the Database List...?"
      If MsgBox(msg, vbYesNo + vbCritical, "DeleteList()") = vbNo Then
        Exit Sub
      Else
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "DeleteAll_ListQ"
        DoCmd.SetWarnings True
        cmdfrm.dbList.Requery
        cmdfrm.cmdDelete.eabled = False
        Exit Sub
      End If
    Case 3
        Exit Sub
End Select

Selected:
delCount = CheckList()
If delCount > 0 Then
    msg = "Delete " & delCount & " Items." & vbCr & vbCr & "Proceed...?"
    If MsgBox(msg, vbYesNo, "DBDelete()") = vbNo Then
        Exit Sub
    End If
    
    Set DB = CurrentDb
    Set Rst = DB.OpenRecordset("DirectoryList", dbOpenDynaset)
    Set lst = cmdfrm.dbList
    
For j = 0 To lstcount
    If lst.Selected(j) Then
      dbName = lst.Column(0, j)
      Rst.FindFirst "Path = '" & dbName & "'"
      If Not Rst.NoMatch Then
        Rst.Delete
        Rst.Requery
      End If
    End If
Next
Rst.Close
Set Rst = Nothing
Set DB = Nothing

lst.Requery
    MsgBox delCount & " Item(s) Deleted From List.", , "DBDelete()"
Else
    MsgBox delCount & " Item(s) Selected for Deletion!", , "DBDelete()"
End If

DBDelete_Exit:
Exit Sub

DBDelete_Err:
MsgBox Err & " : " & Err.Description, , "DBDelete()"
Resume DBDelete_Exit
End Sub

To remove some databases from the ListBox, you must select them from the ListBox and Click the 'Delete from List' Command Button. The DBDelete() Subroutine is called and the selected items will be deleted from the DirectoryList Table and refreshes the ListBox to reflect the change.

The KillTempFile() Subroutine.

Private Sub KillTempFile(ByVal filename As String)
On Error GoTo KillTempFile_Err
'Manage Temporary Files
    If Len(Dir(filename)) > 0 Then
        Kill filename
    End If

KillTempFile_Exit:
Exit Sub

KillTempFile_Err:
MsgBox Err & ": " & Err.Description, , "KillTempFile()"
Resume KillTempFile_Exit
End Sub

The Compact/Repair Program creates Temporary Databases for System use and deletes them using the above Subroutine. This Subroutine is called from within the DBPrepare() and DBCompact() Subroutines.

The FLst_ObjInit Interface Class Module VBA Code.

Option Compare Database
Option Explicit

Private cmd As FLst_CmdButton
Private frm As Access.Form
Private Coll As New Collection

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Database Compact/Repair Utility
'Author: a.p.r. pillai
'Date  : 20/02/2024
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------

Public Property Get Ini_Frm() As Access.Form
    Set Ini_Frm = frm.m_cFrm
End Property

Public Property Set Ini_Frm(ByRef pFrm As Access.Form)
    Set frm = pFrm
    
    Call Class_Init
End Property

Private Sub Class_Init()
On Error GoTo Class_Init_Err
Dim ctl As Control
Dim listcount As Long
Const EP = "[Event Procedure]"

listcount = DCount("*", "DirectoryList")

'If ListBox is empty then disable
'cmdDelete Command Button
If listcount = 0 Then
    frm.cmdDelete.Enabled = False
Else
    frm.cmdDelete.Enabled = True
End If

For Each ctl In frm.Controls
Select Case TypeName(ctl)
      Case "CommandButton"
        Select Case ctl.Name
            Case "cmdFileDialog", "cmdCompact", _
            "cmdDelete", "cmdQuit"
            
                Set cmd = New FLst_CmdButton
                Set cmd.cmd_Frm = frm
                Set cmd.c_cmd = ctl
        
                    cmd.c_cmd.OnClick = EP
                Coll.Add cmd
                Set cmd = Nothing
        End Select
End Select
Next

Class_Init_Exit:
Exit Sub

Class_Init_Err:
MsgBox Err & " : " & Err.Description, , "Class_Init()"
Resume Class_Init_Exit
End Sub

Private Sub Class_Terminate()
Do While Coll.Count > 0
    Coll.Remove 1
Loop
End Sub

Within the global declaration area, the FLst_CmdButton Class, the Form Object frm, and the Collection Object Coll are declared. This is succeeded by the inclusion of Get/Set Property Procedures for the frm property. In adherence to common practice, the active Form Object is passed from the Form_Load() Event Procedure into the pFrm parameter, subsequently being assigned to the Form Object frm.

Upon obtaining the reference to the active Form object within the Interface Class, in the subsequent phase of the Set Property Procedure, we invoke the Class_Init() Subroutine.

Within the Class_Init() Subroutine, a constant named "EP" is created to represent the [Event Procedure] text. Following this, a check is implemented to determine the status of the DirectoryList Table, which serves as the source data for the ListBox. If the DirectoryList table is empty, the [Delete from List] Command Button on the Form is disabled.

Within the subsequent For...Next Loop, the program iterates through the Command Buttons on the Form. When a Command Button is identified, an individual instance of the Command Button Wrapper Class is instantiated. This instance is then assigned with the respective Control Reference, and the necessary Events, specifically the Click Events in this case, are enabled. These instances are subsequently stored in the Collection Object, for retaining them in memory.

You may take note of the following Statements:

                Set cmd = New FLst_CmdButton
                Set cmd.cmd_Frm = frm
                Set cmd.c_cmd = ctl
        
                    cmd.c_cmd.OnClick = EP
                Coll.Add cmd
                Set cmd = Nothing

The initial statement initiates the creation of an instance of the FLst_CmdButton Class in memory. Its cmd_frm Property is then configured with the active form object frm, and the current Command Button control Reference in ctl is transmitted to the c_cmd Property. When these two properties are armed with the references of the Form and Command Button, the resulting instance of the Command Button Wrapper Class effectively mirrors the properties and characteristics of the corresponding Command Button on the Form.

The subsequent statement, cmd.c_cmd.OnClick = EP is functionally equivalent to specifying the text [Event Procedure] in the OnClick Event Property of the Command Button. Following the activation of the Event Procedure, the current instance of the Wrapper Class is added to the Collection Object in memory. This enables the capturing of the Event when triggered from the Command Button, subsequently executing the associated Event Procedure in the Wrapper Class Module.

You should not ignore the next statement Set cmd = Nothing.

At this point you may be in doubt when we execute the above statement it will erase the Wrapper Class Instance we created in memory.

While the resemblance may be apparent, there is a crucial distinction. The inclusion of this instance of the Wrapper Class Object in the Collection Object ensures that the Collection Object remains active, retaining the Wrapper Class Instance in memory until the Form is closed and subsequently cleared.
The reason we need to execute Set cmd = Nothing is to avoid creating the next CmdButton Wrapper Class Instance for another Command Button on the Form without clearing the previous one from memory. Without this step, attempting to create the second instance of the Command Button Wrapper Class could result in overwriting the earlier instance in the same memory location. Thus, resetting cmd ensures that a new instance can be created without interference with the earlier Instance of the Wrapper Class.
If we don't execute the Set cmd = Nothing then only the last Command Button's Event will remain valid and others will keep overwriting the earlier Instances.
Following the reset of the "cmd" object, the process of creating another instance of the Command Button Wrapper Class involves searching for an available memory area to instantiate a new instance of the Command Button Class. This ensures the proper allocation of memory for the new instance, preventing any potential interference with existing instances.
So, please don't ignore this statement. Since it is a logical issue you may need help finding it so easily when debugging.

The Compact_Repair Form Module VBA Code.

Option Compare Database
Option Explicit

Dim Obj As New FLst_ObjInit

Private Sub Form_Load()
DoCmd.Restore
Set Obj.Ini_Frm = Me
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Set Obj = Nothing
End Sub

As customary, we have instantiated the Interface Class FLst_ObjInit in the global declaration section of the Form's Class Module. This declaration ensures that all three sets of Class Modules—Wrapper Class, Interface Class, and Form Class Module—are loaded into memory and poised for operation.

In the Form_Load() event procedure, the obj.Ini_Frm property of the Interface Class is assigned the reference to the active Form object, denoted by Me. This initiates a series of actions, and within moments, the system is primed to handle programmed events and their respective functions.

I hope this utility program proves to be a valuable tool for optimizing your Access applications over an extended period, and the best part is, that it comes at no cost.