标题: Compact database(s) automatically on a schedule in Access 2000[推荐] [打印本页] 作者: cattjiu 时间: 2002-12-18 22:34 标题: Compact database(s) automatically on a schedule in Access 2000[推荐] http://2fix.bvu.edu/help/apps/access/EA3W88FG8.ASP
How to: Compact database(s) automatically on a schedule in Access 2000.
Solution:
Create a new blank database and a new table, with fields for identifying the database path(s), database name(s), and an autonumber. Create a new form, set the 'Timer Interval' to 6000, and in the On Timer event, type the code that will check the time, access the table, and compact the database(s).
1) Create a new, blank database, and name it Compact.mdb.
2) In Compact.mdb, open a new table in Design view, and set up the necessary fields:
Fields in table Design view
a) In the 'Field Name' column, type the name DBID.
b) From the drop-down list in the 'Data Type' column, select 'Autonumber'.
c) In the next row, in the 'Field Name' column, type the name DBFolder.
d) From the drop-down list in the 'Data Type' column, select 'Text'.
e) In the 'Field Properties' section, click the 'General' tab, and in the 'Field Size' box, type 255.
f) In the next row, in the 'Field Name' column, type the name DBName.
g) From the drop-down list in the 'Data Type' column, select 'Text'.
h) In the 'Field Properties' section, click the 'General' tab, and type 255 in the 'Field Size' box.
3) Save the table with the name DBNames.
4) Create a new, blank form, and open the form in Design view.
5) In the Design view of the new, blank form, select the 'View' menu and select 'Properties'. (The 'Form' properties dialog box appears.)
NOTE: If 'Properties' does not appear, click the down-arrow at the bottom of the menu to expand the menu.
6) Click the 'Format' tab, and set the following form properties:
'Format' tab
a) In the 'Caption' box, type Compact Database.
b) From the drop-down list in the 'Default View' box, select 'Single Form'.
c) From the drop-down list in the 'Scroll Bars' box, select 'Neither'.
d) From the drop-down list in the 'Record Selectors' box, select 'No'.
e) From the drop-down list in the 'Navigation Buttons' box, select 'No'.
7) To write the code to auto-run the Compact Database command:
a) In the 'Form' properties dialog box, click the 'Event' tab.
'Event' tab
b) In the 'Timer Interval' box,Type '6000'.
c) Click in the 'On Timer' box, and click the '...' button that appears. (The 'Choose Builder' dialog box appears.)
d) From the list box, select 'Code Builder', and click 'OK'. (A code module window appears, with the cursor appearing beneath a line that reads 'Private Sub Form_Timer()' and above a line that reads 'End Sub'.)
e) Type the following code between the two lines:
NOTE 1: While VBA automatically sets some keywords to initial caps, it is recommended that you type in the code exactly as you see it here, in order to avoid syntax errors. Misspelled or incorrectly capitalized variable names is a common cause of errors in code.
NOTE 2: It is not necessary to copy the commented code (the code preceded by a single quote); however, it is useful for making the code more readable for others.
'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 Recordset
Dim DB As Database
Dim DBName As String
Dim NewDBName 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 and 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 Access
DoCmd.Close acForm, ''Compa