Microsoft KB Archive/158937

= How to Compact Databases at a Scheduled Time =

Article ID: 158937

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 97 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition

-



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.



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.  Create a new blank database called Compact.mdb .  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  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<BR/> RecordSelectors: No<BR/> NavigationButtons: No<BR/> OnTimer: [Event Procedure]<BR/> TimerInterval: 60000 </li>  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 </li> Save the form as CompactDB, and then close it.</li>  Create a new macro with the following action: <pre class="fixed_text">     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 the macro.</li>  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: <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 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.</li></ol>

Method 2
Use the Microsoft Windows NT Schedule Service to start the database by using the /compact command line option. <ol> 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.</li> On the Start menu, point to Settings, and then click Control Panel.</li> In Control Panel, open Services.</li> 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.</li> In the list, double-click the Schedule service. This displays the Schedule dialog box.</li> In the Schedule dialog box, click to select Log on as This account, and then click Build (...).</li>  Open Notepad or any text editor, and then type the following lines: <pre class="fixed_text">  <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. </li> Save the file to the root of your drive C as "MyTest.bat" (including the quotation marks).</li> 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.</li> When the time expires, Access opens, compacts the database, and then closes.</li></ol>

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: <ol> <li> Open Notepad or any text editor, and then type the following lines: <pre class="fixed_text">         <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. </li> <li>Save the file to the root of your drive C as "MyTest.bat" (including the quotation marks).</li> <li>In the \Winnt\Tasks folder in Windows NT Explorer, double-click Add Scheduled Task. This starts the Scheduled Task Wizard. Click Next.</li> <li>Click Browse.</li> <li>In the Select Program to Schedule dialog box, move to c:\.</li> <li>Click to select MyTest.bat, and then click Open.</li> <li>Change the name of the task to MyTask .</li> <li>Click One time only, and then click Next.</li> <li>Change the Start Time to about 2 minutes ahead of the current time, and leave the Start Date at the current date.</li> <li>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.</li> <li>Click Finish.

When the time expires, Access opens, compacts, and then closes the database.</li></ol>

<div class="references_section">