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: allanmurphy@unwired.com.au