ACC2000: How to Compact Databases At a Scheduled Time (209979)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q209979
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

SUMMARY

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.

MORE INFORMATION

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.

REFERENCES

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.
Modification Type:MajorLast Reviewed:6/23/2005
Keywords:kbhowto kbinfo kbProgramming KB209979
©2004 Microsoft Corporation. All rights reserved.