Microsoft KB Archive/209979

= ACC2000: How to Compact Databases At a Scheduled Time =

Article ID: 209979

Article Last Modified on 6/23/2005

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



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.

 Create a new blank database called Compact.mdb. 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

 Save the table as DBNames, and then close it. 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

  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

 Save the form as CompactDB, and then close it.</li> Create a new macro with the following action:

Action

OpenForm

Action Arguments

Form Name: CompactDB

View: Form

Data Mode: Read Only

Window Mode: Normal

</li> Save the macro as AutoExec, and then close it.</li>  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: <pre class="fixed_text">  DBID   DBFolder                       DBName ---     1   C:\MSOffice\Access\Samples     Northwind.mdb 2  \\Servername\Access\Sampapps   Nwind.mdb </li> 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.</li></ol>

<div class="references_section">