Alex
You have two options
1. Compact on Close
From the Toolbar select Tools ---> Option -----> General Tab then tick
Compact on close this compact the database when you exit the current
database.
2. Modify the following code, this code compacts the back end of a database
that I have previously used via a command button or menu item
Sub CompactBE()
Dim OldMBD As String
Dim NewMBD As String
Dim MBDPath As String
Dim NewMDBFileNPath As String
Dim OldMDBFileNPath As String
Dim Msg, Style, Title, Response, MyString
Msg = "This option compacts the back end. It takes up to five minutes. Do
you want to continue ?" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "Compact Back End" ' Define title.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.
OldMBD = "DataHolder.mdb"
DoCmd.Echo False, "Locating File. Please wait"
MBDPath = "//Nsw-sit-1\Vol1\Data\rosters\"
NewMBD = "DataHolderNew.mdb"
OldMDBFileNPath = MBDPath & OldMBD
NewMDBFileNPath = MBDPath & NewMBD
DoCmd.Echo False, "Compacting BE. Please wait"
DBEngine.CompactDatabase OldMDBFileNPath, NewMDBFileNPath
DoCmd.Echo False, "Deleting old File. Please wait"
Kill (OldMDBFileNPath) ' deletes the original file
DoCmd.Echo False, "Renaming new File. Please wait"
FileCopy NewMDBFileNPath, OldMDBFileNPath ' copys the new to the
old
DoCmd.Echo False, "Deleting temporary file. Please wait"
Kill (NewMDBFileNPath) ' kills newpath
MyString = "Process actioned." ' Perform some action.
Else ' User chose No.
MyString = "Process abandoned" ' Perform some action.
End If
MsgBox MyString
CompactBE_Exit:
Exit Sub
DoCmd.Echo True
CompactBE_Error:
Select Case Err
Case 3356
MsgBox "Other user currently blocking process. Try again later."
Case Else
MsgBox "Unknown error."
End Select
GoTo CompactBE_Exit
End Sub
Allan Murphy
Email: (e-mail address removed)
