ACC2000: How to Compact Databases At a Scheduled Time

This article describes a technique that you can use to start compacting one or more databases automatically at a scheduled time. You create a small database with a table, a form, and a macro. The table stores the names of the databases that you want to compact. The form contains a procedure in the Timer event that starts compacting the databases whose names are in the table at the time specified in the procedure. The macro opens the form every time that you open the database.

The following example uses a Visual Basic procedure to compact one or more databases and then to quit Microsoft Access when it is finished. You cannot compact the database that is running the procedure, nor can you compact any database that you cannot open exclusively. You must have read and write permissions for the folder where the database that you are compacting resides, and you need enough disk space in that folder to store both the original and the compacted copies of the database. In the example, the code specifies 12:00 midnight as the starting time. To change the starting time, you must edit the code.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.
NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

1. Create a new blank database called Compact.mdb.
2. Create the following new table in Design view:
   Field Name: DBID
   Data Type: AutoNumber (or Counter in version 2.0)
   
   Field Name: DBFolder
   Data Type: Text
   Field Size: 255
   
   Field Name: DBName
   Data Type: Text
   Field Size 255
   
   PrimaryKey: DBID
3. Save the table as DBNames, and then close it.
4. Create a new blank form, and then set the following properties:
   Caption: Compact Databases
   Default View: Single Form
   Scrollbars: Neither
   RecordSelectors: No
   NavigationButtons: No
   OnTimer: [Event Procedure]
   TimerInterval: 60000
5. Click the Build button next to the OnTimer property of the form, select Code Builder, and then type or paste the following procedure:

   Private Sub Form_Timer()
   '==================================================================
   'The Timer event runs this code every minute. It compares your
   'system time with the StartTime variable. When they match, it
   'begins compacting all databases in the DBNames table.
   '==================================================================
   Dim StartTime As String
   ' Set this variable for the time you want compacting to begin.
   StartTime = "12:00 AM"
   ' If StartTime is now, open the DBNames table and start compacting
   If Format(Now(), "medium time") = Format(StartTime, _
           "medium time") Then
      Dim RS As DAO.Recordset, DB As DAO.Database
      Dim NewDBName As String, DBName As String
      Set DB = CurrentDb()
      Set RS = DB.OpenRecordset("DBNames")
      On Error Resume Next
      RS.MoveFirst
      Do Until RS.EOF
         DBName = RS("DBFolder") & "\" & RS("DBName")
         ' Create a new name for the compacted database.
         ' This example uses the old name plus the current date.
         NewDbName = Left(DbName, Len(DbName) - 4)
         NewDbName = NewDbName & " " & Format(Date, "MMDDYY") & ".mdb"
         DBEngine.CompactDatabase DBName, NewDBName
         RS.MoveNext
      Loop
   ' Close the form, and then close Microsoft Access
   DoCmd.Close acForm, "CompactDB", acSaveYes
   RS.Close
   DoCmd.Quit acSaveYes
   End If
   End Sub
   
   					

6. Save the form as CompactDB, and then close it.
7. Create a new macro with the following action:
   Action
   OpenForm
   
   Action Arguments
   Form Name: CompactDB
   View: Form
   Data Mode: Read Only
   Window Mode: Normal
8. Save the macro as AutoExec, and then close it.
9. Open the DBNames table, and then add a record for each database that you want to compact. Type the full path to the database in the DBFolder field, and then type the name of the database itself in the DBName field. For example:

      DBID   DBFolder                       DBName
      ---------------------------------------------------
         1   C:\MSOffice\Access\Samples     Northwind.mdb
         2   \\Servername\Access\Sampapps   Nwind.mdb
   					

10. Close the database, and then reopen it at any time before compacting is scheduled to start.
    
    The AutoExec macro automatically opens the CompactDB form. Leave Microsoft Access running with this form open. At the specified time, compacting begins, and when the last database is finished, Microsoft Access quits.

For more information about the Timer event or the TimerInterval property, click Microsoft Access Help on the Help menu, type timerinterval property in the Office Assistant or the Answer Wizard, and then click Search to view the topic.