I am aware of the various methods to compact databases"
I still use the DBEngine.CompactDatabase method for the Back End, or other external database for that matter, but I use the Application.SetOption method to compact the Front End or current DB.
Compacting the open Front End basically works like this:
I am also not sure I trapped all the errors I needed to, so if you get errors, please let me know so I can modify my code.
I have tested this on Access 2010 and 2010 runtime as well as XP-32bit, Win7-32bit. If you can test it on other systems and it does or doesn't work, please let me know.
Any rate, thanks Bob for the inspiration and I hope someone finds this useful.
Craig
- Access 2003: Sendkeys "%(TDC)", False
- Access 2007: Sendkeys "%(FMC)", False
- Access 2010: Sendkeys "%(YC)", False
- accDoDefaultAction Method
- DBEngine.CompactDatabase SourceFile, DestinationFile
I still use the DBEngine.CompactDatabase method for the Back End, or other external database for that matter, but I use the Application.SetOption method to compact the Front End or current DB.
Compacting the open Front End basically works like this:
- Sets the database to "Compact on close"
- Creates a batch file to reopen the current database
- Opens the database
- Deletes the batch file.
I am also not sure I trapped all the errors I needed to, so if you get errors, please let me know so I can modify my code.
I have tested this on Access 2010 and 2010 runtime as well as XP-32bit, Win7-32bit. If you can test it on other systems and it does or doesn't work, please let me know.
Any rate, thanks Bob for the inspiration and I hope someone finds this useful.
Craig
Code:
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