Knowledge Base

How to Compact Databases at a Scheduled Time

Article ID: 158937

Article Last Modified on 1/19/2007

APPLIES TO

This article was previously published under Q158937
Moderate: Requires basic macro, coding, and interoperability skills.

SUMMARY

This article describes two methods that you can use to start to compact one or more databases automatically at a scheduled time. In the first method, 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 to compact the databases whose names are in the table. The macro opens the form every time you open the database.

In the second method, you can use a scheduling utility, such as the NT Schedule Service, to open an Access database by using the /compact command line switch.

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

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

MORE INFORMATION

Method 1

The following example uses a Microsoft Visual Basic procedure to compact one or more databases, and then to close Access when it is finished. You cannot compact the database that runs 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 then close it.
  4. Create a new blank form, and then set the following properties:
           Caption: Compact Databases<BR/>
       Default View: Single Form<BR/>
       Scrollbars: Neither<BR/>
       RecordSelectors: No<BR/>
       NavigationButtons: No<BR/>
       OnTimer: [Event Procedure]<BR/>
       TimerInterval: 60000
  5. Click Build next to the OnTimer property of the form, and then 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
      'starts to compact all databases in the DBNames table.
      '==================================================================
      Dim StartTime As String
      ' Set this variable for the time you want compacting to start.
      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
      RS.Close
      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 to compact 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 in 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 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 the macro.
  9. Open the DBNames table, and then 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 the database before compacting is scheduled to start. The AutoExec macro automatically opens the CompactDB form. Leave Access running with this form open. At the specified time, compacting begins and when the last database is complete, Access closes.

Method 2

Use the Microsoft Windows NT Schedule Service to start the database by using the /compact command line option.
  1. On a Windows NT-based Server computer, make a copy of the sample database Northwind.mdb, and then move it to the root directory of drive C.
  2. On the Start menu, point to Settings, and then click Control Panel.
  3. In Control Panel, open Services.
  4. In the Services list, look for the service called Schedule. If you see it, go to step 5.

    However, if you do not see Schedule listed in the Services list, but you do see Task Scheduler in the list, close the Services dialog box, go to the "Using Task Scheduler" section in this article, and then continue with those steps instead.
  5. In the list, double-click the Schedule service. This displays the Schedule dialog box.
  6. In the Schedule dialog box, click to select Log on as This account, and then click Build (...).
  7. Open Notepad or any text editor, and then type the following lines: 
       <B>c:
   cd\program files\microsoft office\office
   Msaccess.exe c:\Northwind.mdb /compact</B>
    The path to the Access executable program is c:\program files\microsoft office\office. Msaccess.exe may be in a different location on your computer. If so, you can use the Find command on the Start menu to locate Msaccess.exe, and then determine the correct path.
  8. Save the file to the root of your drive C as "MyTest.bat" (including the quotation marks).
  9. To schedule the application to run, type the following at the command prompt:

    at 4:30pm /interactive "c:\mytest.bat"

    NOTE: 4:30pm is an example. Use a time that is about 2 minutes ahead of the current time for this test.
  10. When the time expires, Access opens, compacts the database, and then closes.

Using Task Scheduler

If you see Task Scheduler listed in the Services dialog box, you have probably installed Microsoft Internet Explorer 4.0 or later. Internet Explorer setup replaces the Schedule Service with Task Scheduler. In this case, you cannot change the logon for the service. You must do so on a task-by-task basis. After completing steps 1 through 7 earlier, continue with the following steps to demonstrate the example by using Task Scheduler:
  1. Open Notepad or any text editor, and then type the following lines: 
              <B>c:
          cd\program files\microsoft office\office
          Msaccess.exe C:\Northwind.mdb /compact</B>
    The path to the Access executable program is c:\program files\microsoft office\office. Msaccess.exe may be in a different location on your computer. If so, you can use the Find command on the Start menu to locate Msaccess.exe and determine the correct path.
  2. Save the file to the root of your drive C as "MyTest.bat" (including the quotation marks).
  3. In the \Winnt\Tasks folder in Windows NT Explorer, double-click Add Scheduled Task. This starts the Scheduled Task Wizard. Click Next.
  4. Click Browse.
  5. In the Select Program to Schedule dialog box, move to c:\.
  6. Click to select MyTest.bat, and then click Open.
  7. Change the name of the task to MyTask.
  8. Click One time only, and then click Next.
  9. Change the Start Time to about 2 minutes ahead of the current time, and leave the Start Date at the current date.
  10. Click Next.

    You see the screen for the user account that the task uses. Type the current user name and password that you are currently logged on as.
  11. Click Finish.

    When the time expires, Access opens, compacts, and then closes the database.

REFERENCES

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

For additional information about starting Access with the NT Schedule Service, click the article number below to view the article in the Microsoft Knowledge Base:

231478 ACC97: How to Start Access by Using the Windows NT Schedule Service

For additional information about starting Access with command line switches, click the article number below to view the article in the Microsoft Knowledge Base:

105128 ACC: How to Use Command-Line Switches in Microsoft Access


Additional query words: inf

Keywords: kbhowto kbprogramming KB158937


Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.