ACC: How to Compact Databases at a Scheduled Time

• Microsoft Access versions 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic coding, and interoperability skills.

This article describes a technique 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 you want to compact. The form contains a procedure in the Timer event that starts compacting the databases whose names are in the table. The macro opens the form every time you open the database.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.

MORE INFORMATION

The following example uses a Visual Basic procedure to compact one or more databases, and then to close 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 you are compacting resides, and you need enough disk space in that folder to store the original and the compacted copies of the database.

1. Create a new blank database called Compact.mdb.



2. Create the following new table in Design view:

   
          Table: DBNames
          ----------------------------------------------------
          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
   
          Table Properties: DBNames
          -------------------------
          PrimaryKey: DBID 




3. Save the table as DBNames and close it.



4. Create a new blank form and 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 and type the following procedure:

   In Microsoft Access 7.0 and 97:

   
          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 Recordset, DB As 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
         DoCmd.Quit acSaveYes
         End If
         End Sub 

   In Microsoft Access 2.0:
   
   NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

   
         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 Recordset, DB As 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 DBID plus the current date, which
               ' falls within DOS 8.3 file name limits for DBID = 1 to 99.
               NewDBName = RS("DBFolder") & "\" & RS("DBID") & Format(Date, _
                   "MMDDYY") & ".mdb"
               DBEngine.CompactDatabase DBName, NewDBName
               RS.MoveNext
            Loop
         ' Close the form, and then close Microsoft Access.
         DoCmd Close a_Form, "CompactDB"
         DoCmd Quit a_Save
         End If
         End Sub 




6. Save the form as CompactDB and 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 close it.



9. Open the DBNames table and add a record for each database you want to compact. Type the full path to the database in the DBFolder field, and 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 any time before compacting is scheduled to start. The AutoExec macro will automatically open the CompactDB form. Leave Microsoft Access running with this form open. At the specified time, compacting begins and when the last database is done, Microsoft Access closes.

REFERENCES

For more information about the Timer event or the TimerInterval property, search the Help Index for Timer event or TimerInterval property.

Additional query words:

Keywords : kbprg
Version : WINDOWS:2.0,7.0,97
Platform : WINDOWS
Issue type : kbhowto