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
Compact All Back-Ends in the Same Folder
-
- 3StarLounger
- Posts: 366
- Joined: 12 May 2010, 06:49
-
- Administrator
- Posts: 78790
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Compact All Back-Ends in the Same Folder
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.
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
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 366
- Joined: 12 May 2010, 06:49
Re: Compact All Back-Ends in the Same Folder
Thank You Hans,
Greatly appreciated, and your response time is incredible!
Kindest Regards,
Mohamed
Greatly appreciated, and your response time is incredible!
Kindest Regards,
Mohamed
-
- 3StarLounger
- Posts: 366
- Joined: 12 May 2010, 06:49
Re: Compact All Back-Ends in the Same Folder
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
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
-
- Administrator
- Posts: 78790
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Compact All Back-Ends in the Same Folder
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: After:
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: After:
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 366
- Joined: 12 May 2010, 06:49
Re: Compact All Back-Ends in the Same Folder
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
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