M
Malik Conway
NOT SURE IT THIS WILL DO IT BUT THESE ARE TWO POSSIBLE
SOLUTIONS I FOUND A FEW MONTHS BACK...
HOW TO COMPACT DATABASES AT A SCHEDULED TIME
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:
3. Table: DBNames
4. --------------------------------------------
--------
5. Field Name: DBID
6. Data Type: AutoNumber (or Counter in
version 2.0)
7. Field Name: DBFolder
8. Data Type: Text
9. Field Size: 255
10. Field Name: DBName
11. Data Type: Text
12. Field Size 255
13.
14. Table Properties: DBNames
15. -------------------------
16. PrimaryKey: DBID
17. Save the table as DBNames, and then close it.
18. Create a new blank form, and then set the
following properties:
19. Caption: Compact Databases<BR/>
20. Default View: Single Form<BR/>
21. Scrollbars: Neither<BR/>
22. RecordSelectors: No<BR/>
23. NavigationButtons: No<BR/>
24. OnTimer: [Event Procedure]<BR/>
25. TimerInterval: 60000
26. 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
27. Save the form as CompactDB, and then close it.
28. Create a new macro with the following action:
29. Action
30. --------
31. OpenForm
32.
33. Action Arguments
34. --------------------
35. Form Name: CompactDB
36. View: Form
37. Data Mode: Read Only
38. Window Mode: Normal
39. Save the macro as AutoExec, and then close the
macro.
40. 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:
41. DBID DBFolder DBName
42. ---------------------------------------------
------
43. 1 C:\MSOffice\Access\Samples
Northwind.mdb
44. 2 \\Servername\Access\Sampapps
Nwind.mdb
45. 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:
8. <B>c:
9. cd\program files\microsoft office\office
10. 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.
11. Save the file to the root of your drive C
as "MyTest.bat" (including the quotation marks).
12. 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.
13. 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:
2. <B>c:
3. cd\program files\microsoft office\office
4. 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.
5. Save the file to the root of your drive C
as "MyTest.bat" (including the quotation marks).
6. In the \Winnt\Tasks folder in Windows NT Explorer,
double-click Add Scheduled Task. This starts the Scheduled
Task Wizard. Click Next.
7. Click Browse.
8. In the Select Program to Schedule dialog box, move
to c:\.
9. Click to select MyTest.bat, and then click Open.
10. Change the name of the task to MyTask.
11. Click One time only, and then click Next.
12. Change the Start Time to about 2 minutes ahead of
the current time, and leave the Start Date at the current
date.
13. 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.
14. Click Finish.
When the time expires, Access opens, compacts, and then
closes the database.
SOLUTIONS I FOUND A FEW MONTHS BACK...
HOW TO COMPACT DATABASES AT A SCHEDULED TIME
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:
3. Table: DBNames
4. --------------------------------------------
--------
5. Field Name: DBID
6. Data Type: AutoNumber (or Counter in
version 2.0)
7. Field Name: DBFolder
8. Data Type: Text
9. Field Size: 255
10. Field Name: DBName
11. Data Type: Text
12. Field Size 255
13.
14. Table Properties: DBNames
15. -------------------------
16. PrimaryKey: DBID
17. Save the table as DBNames, and then close it.
18. Create a new blank form, and then set the
following properties:
19. Caption: Compact Databases<BR/>
20. Default View: Single Form<BR/>
21. Scrollbars: Neither<BR/>
22. RecordSelectors: No<BR/>
23. NavigationButtons: No<BR/>
24. OnTimer: [Event Procedure]<BR/>
25. TimerInterval: 60000
26. 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
27. Save the form as CompactDB, and then close it.
28. Create a new macro with the following action:
29. Action
30. --------
31. OpenForm
32.
33. Action Arguments
34. --------------------
35. Form Name: CompactDB
36. View: Form
37. Data Mode: Read Only
38. Window Mode: Normal
39. Save the macro as AutoExec, and then close the
macro.
40. 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:
41. DBID DBFolder DBName
42. ---------------------------------------------
------
43. 1 C:\MSOffice\Access\Samples
Northwind.mdb
44. 2 \\Servername\Access\Sampapps
Nwind.mdb
45. 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:
8. <B>c:
9. cd\program files\microsoft office\office
10. 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.
11. Save the file to the root of your drive C
as "MyTest.bat" (including the quotation marks).
12. 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.
13. 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:
2. <B>c:
3. cd\program files\microsoft office\office
4. 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.
5. Save the file to the root of your drive C
as "MyTest.bat" (including the quotation marks).
6. In the \Winnt\Tasks folder in Windows NT Explorer,
double-click Add Scheduled Task. This starts the Scheduled
Task Wizard. Click Next.
7. Click Browse.
8. In the Select Program to Schedule dialog box, move
to c:\.
9. Click to select MyTest.bat, and then click Open.
10. Change the name of the task to MyTask.
11. Click One time only, and then click Next.
12. Change the Start Time to about 2 minutes ahead of
the current time, and leave the Start Date at the current
date.
13. 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.
14. Click Finish.
When the time expires, Access opens, compacts, and then
closes the database.