Compact All Back-Ends in the Same Folder

[MSingh]

Hi,

I have multiple back-ends all in one folder with the front-end in a different folder.

With all back-ends closed, would there be a way to compact all back-ends from the front-end?

Your assistances are greatly appreciated.

Kind Regards
Mohamed

[HansV]

Here is code you can use. You can call it from the on click event of a command button on a form, for example.
You MUST make sure that none of the backends is in use when you run the code!

Change the path in the constant strPath as needed.

Code: Select all

Sub CompactBackends()
    ' Path to backends including trailing backslash
    Const strPath = "C:\Backends\"
    Dim strName As String
    Dim strTemp As String
    Screen.MousePointer = 11 ' hourglass
    strName = Dir(strPath & "*.accd*")
    Do While strName <> ""
        strTemp = Left(strName, Len(strName) - 6) & "_temp" & Mid(strName, Len(strName) - 5)
        CompactDatabase strPath & strName, strPath & strTemp
        Kill strPath & strName
        Name strPath & strTemp As strPath & strName
        strName = Dir
    Loop
    Screen.MousePointer = 0 ' default
    MsgBox "Done!", vbInformation
End Sub

[MSingh]

Thank You Hans,
Greatly appreciated, and your response time is incredible!
Kindest Regards,
Mohamed

[MSingh]

Hi Hans,

Tested the code several times & it runs without error, however, I don't see a decrease in the size of the back-end files.
If I do a manual "Compact & Repair" then the back-end file size does reduce (quite significantly).
I ensured that the back-ends are closed since the code runs-off cmdClose where all forms are first closed before calling CompactBackEnds.
Is there something I'm not doing correctly?

Kind Regards,
Mohamed

[HansV]

I performed a (admittedly limited) test, and the size of the backends was reduced.
Keep in mind that compacting an already compacted database will have little or no effect.

You might try the following:
- Open a Windows Explorer window on the folder with the backends.
- Make sure that Windows Explorer is in Details view, so that you can see the file sizes.
- Open Visual Basic Editor in Access and open the module with the CompactBackends procedure.
- Arrange these two windows so that you can see both.
- Click in the CompactBackends procedure, then run the code step by step by pressing F8.
- You should see a copy of each database being created, with _temp appended after the original filename.
- This is the compacted database; compare its size to the original file.
- The original file is deleted, and then the new one is renamed to the original name.

Before:

S236.png

After:

S237.png

[MSingh]

Perfect Hans,

I appended your code to Ron De Bruin's code for copying a folder (in Excel) (the copy is saved as a backup, then the live back-ends compacted).
Then after following your instructions & seeing the back-end file sizes change (significantly),
I call your code as in:
Sub BackUP_BE()
'This example copy all files and subfolders from FromPath to ToPath.
'Note: If ToPath already exist it will overwrite existing files in this folder
'if ToPath not exist it will be made for you.
Dim FSO As Object
Dim FromPath As String
Dim ToPath As String

FromPath = "C:\Users\Public\Documents\MyBackEnds_be " '<< Change
' ToPath = "C:\Users\Ron\Test" '<< Change

'If you want to create a backup of your folder every time you run this macro
'you can create a unique folder with a Date/Time stamp.
'ToPath = "C:\Users\Ron\" & Format(Now, "yyyy-mm-dd h-mm-ss")
ToPath = "C:\BkUp\" & Format(Now, "yyyy-mm-dd h-mm-ss")

If Right(FromPath, 1) = "\" Then
FromPath = Left(FromPath, Len(FromPath) - 1)
End If

If Right(ToPath, 1) = "\" Then
ToPath = Left(ToPath, Len(ToPath) - 1)
End If

Set FSO = CreateObject("scripting.filesystemobject")

If FSO.FolderExists(FromPath) = False Then
MsgBox FromPath & " doesn't exist"
Exit Sub
End If

FSO.CopyFolder Source:=FromPath, Destination:=ToPath
' MsgBox "You can find the files and subfolders from " & FromPath & " in " & ToPath
MsgBox "The Data Files have been BackedUp to " & ToPath, vbInformation, "BackUp Completed"

Call CompactBackends

End Sub

That works perfectly !!!
Many Thanks
Kind Regards,
Mohamed