Compact a Back End OR a Front End Database (1 Viewer)

Option Compare Database
Option Explicit

'------------------------------------------------------------------------------
'
'    This module will compact an external database OR the current database.
'    If the current database is being compacted, it will close the database,
'    create a batch file, and reopen.
'
'    The inspiration behind this method comes from Bob Larson and the way his
'    "Front End Update" tool works.
'
'    Required References:
'         1.   Microsoft Scripting Runtime
'
'    To compact a database add the following to a button or other control:
'
'              Call CompactRepair(sSourceFile)
'                   where 'sSourceFile' is the full path to the
'                   database you want to compact.
'
'    I highly recommend creating a backup prior to running ANY compact/repair
'    operation, regardless of which various method you are using.
'
'    In the startup form's Form_Load event or your startup routine be sure to
'    add the following:
'              Application.SetOption "Auto Compact", False
'                   This way the current DB won't compact every time it closes.
'
'    I think I have trapped the error for the Back End being in use. If there
'    are other errors that need to be trapped, please let me know so I can
'    add them to my code.
'
'------------------------------------------------------------------------------

Dim FSO As FileSystemObject

Dim sDestFile As String
Dim sExt As String
Dim sFileName As String
Dim sPrompt As String
Dim sSourcePath As String
Dim sTitle As String

Function CompactRepair(ByVal sSourceFile As String)
On Error GoTo Error_Handler

    If sSourceFile <> Application.CurrentDb.Name Then
        
        'Compact the back end
        Set FSO = New FileSystemObject
        sFileName = FSO.GetBaseName(sSourceFile)
        sExt = "." & FSO.GetExtensionName(sSourceFile)
        sSourcePath = FSO.GetParentFolderName(sSourceFile) & "\"
        
        'Delete the previous Temp file if it exists.
        If Dir(sSourcePath & sFileName & "_Temp" & sExt) <> "" Then
            Kill sSourcePath & sFileName & "_Temp" & sExt
        End If
        
        'Compact the Back-End database to a temp file.
        DBEngine.CompactDatabase sSourceFile, sSourcePath & sFileName & "_Temp" & sExt
        
        'Delete the previous backup file if it exists.
        If Dir(sSourcePath & sFileName & ".bak") <> "" Then
            Kill sSourcePath & sFileName & ".bak"
        End If
    
        'Rename the current database as backup and rename the temp file to
        'the original file name.
        Name sSourceFile As sSourcePath & sFileName & ".bak"
        Name sSourcePath & sFileName & "_Temp" & sExt As sSourceFile
        Kill sSourcePath & sFileName & ".bak"
        Set FSO = Nothing
    
    Else
    
        'Compact the front end
        Application.SetOption "Auto Compact", True
        
        'Sets the file name of the batch file to create
        Dim BatchFile As String
        BatchFile = CurrentProject.Path & "\Compact.cmd"
                
        ' creates the Batch file
        ' Change the ping value to allow for speed.
        ' 60000 = 60 secs, 30000 = 30secs etc
        ' I recommend 60 seconds to prevent any overlapping should the compact take time.
        ' Large databases may require more time and smaller databases may require less
        ' time. Just be sure you are not trying to open the database while it is still
        ' compacting.

        Open BatchFile For Output As #1
        Print #1, "Echo Off"
        Print #1, "ECHO Compacting Front End"
        Print #1, ""
        Print #1, "ping 1.1.1.1 -n 1 -w 60000"
        Print #1, ""
        Print #1, "CLICK ANY KEY TO RESTART THE ACCESS PROGRAM"
        Print #1, "START /I " & """MSAccess.exe"" " & sSourceFile
        Print #1, ""
        Print #1, "Del %0"
        Close #1

        ' runs the batch file
        Shell BatchFile

        'closes the current front end and runs the batch file
        DoCmd.Quit
        
    End If

Error_Handler_Exit:
    Exit Function
   
Error_Handler:
    Select Case Err.Number
        Case 3356
            sPrompt = "The Back End is currently being used by another User. " & vbCrLf
            sPrompt = sPrompt & "You can only Compact the Database if you are the only person using it." & vbCrLf
            sPrompt = sPrompt & vbCrLf & "Please try again later."
            sTitle = "Back End in use..."
            MsgBox sPrompt, vbExclamation, sTitle
            Err.Clear
            Resume Error_Handler_Exit
        Case Else
            MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Database Error..."
            Err.Clear
            Resume Error_Handler_Exit
    End Select

End Function

Public Function CompactRepair(ByVAl sSourceFile as String) as Boolean