This FAQ describes a process which will cause the æcompress and repair databaseÆ process to run when the first user logs into a database each day. This ensures that the database is compacted once every business day, with no user intervention. I have only tested this with Access 2000, and I suggest it is appropriate for smaller databases (say, 20 Mb or less) where you probably donÆt have an administrator to ensure that æcompact and repairÆ is run each day.
The same automation process could be used to run any other processes which you would like to run the first time a database is opened each day.
I have left some example code in the MaintenanceCheck function, to show how you can:
-- Write a log record etc. to record who triggered the compact and repair process, and when.
-- Display a message to users with read-only access, as they can't run Compact and Repair.
Assumptions
+ Access 2000 (not tested with other versions)
+ A start-up form such as the Switchboard form loads when the database opens
+ A control table exists in the database to hold information about the database itself.
+ The database size is such that compact and repair takes a reasonable time (say, one minute or less)
+ There is an AutoExec procedure triggered when the Switchboard form loads
Overview
+ When the database opens, the Switchboard form displays
+ The current date is checked against a ælast maintenance dateÆ field in the Control Table
+ If the current date is later than the stored date, the maintenance process is triggered after a few seconds.
+ This runs æCompact and RepairÆ as if this was selected from the menu.
Why This Works
Normally, any attempt to run æcompact and repairÆ from VB causes an error message, as you cannot compact and repair a database while other processes are running. My procedure overcomes this by:
+ Waiting three seconds, using the Switchboard formÆs timer, before beginning the Maintenance Check process. This ensures that no other processes are active at the same time.
+ Performing the checking process in one function û MaintenanceCheck.
+ If the compact and repair process is required, calling another function, CompactDatabase, which contains a single command. This command triggers compact and repair via the ædo a menu actionÆ facility.
+ This means that when the compact and repair process is triggered, no other code is active.
Code added to the Switchboard form
Code:
Private Sub Form_Load()
AutoExec
End Sub
Private Sub Form_Timer()
Me.TimerInterval = 0
MaintenanceCheck
End Sub
Code within the Autoexec procedure
Code:
Form_Switchboard.TimerInterval = 3000
The Maintenance Check function
Code:
Function MaintenanceCheck()
'--------------------------------------
'- This function checks to see if the -
'- user is the first to log in today. -
'- It triggers a Compact and repair -
'- process if this is true -
'--------------------------------------
On Local Error GoTo MCError1
Dim stDatabaseName As String
Dim stLastCompacted As String
Dim stMessage As String
Dim stSQl As String
Dim stTimeNow As String
Dim stToday As String
stToday = Format$(Now, "yyyymmdd") 'Note the yyyymmdd format
stLastCompacted = DLookup("[ParameterValue]", "tblControl1", "[ParameterName] = 'LastCompacted'")
stDatabaseName = DLookup("[ParameterValue]", "tblControl1", "[ParameterName] = 'DatabaseName'")
'--------------------------------------
'- Database already compacted today -
'--------------------------------------
If stLastCompacted >= stToday Then
Exit Function
End If
'--------------------------------------
'- Database compact process is -
'- required. Display message -
'--------------------------------------
stMessage = "You are the first person to use this database today." & vbCrLf & vbCrLf
If intSecurityLevel = 1 Then
stMessage = stMessage & "Please ask someone with Data-entry or Administrator permissions "
stMessage = stMessage & "to log in and run start of day maintenance."
MsgBox stMessage, vbInformation, stDatabaseName
Exit Function
Else
stMessage = stMessage & "When you click [OK], start of day maintenance will take place." & vbCrLf & "Please wait ..."
MsgBox stMessage, vbInformation, stDatabaseName
stMessage = SysCmd(acSysCmdSetStatus, "Daily Maintenance In Progress ... Please Wait")
End If
'---------------------------------------
'- Write a log record -
'---------------------------------------
stMessage = WriteLogRecord("CompactDatabase", "MaintenanceCheck", "")
'---------------------------------------
'- Update the Control Table record -
'---------------------------------------
stSQl = "UPDATE tblControl1 SET [tblControl1].[ParameterValue] = '" & stToday & "' WHERE [tblControl1].[ParameterName] = 'LastCompacted'"
DoCmd.SetWarnings (False)
DoCmd.RunSQL (stSQl)
DoCmd.SetWarnings (True)
'---------------------------------------
'- Call the CompactDatabase function. -
'- This must be the last line of code -
'- in the MaintenanceCheck function -
'---------------------------------------
CompactDatabase
Exit Function
MCError1:
MsgBox CStr(Err) & " - " & Error$
Resume MCEnd
MCEnd:
End Function
The Compact Database function
Code:
'------------------------------------
'- Compact the database. This only -
'- works if it is the only code in -
'- the function, and if the -
'- function is called from the last -
'- line of another VB function -
'------------------------------------
CommandBars("Menu Bar"). _
Controls("Tools"). _
Controls("Database utilities"). _
Controls("Compact and repair database..."). _
accDoDefaultAction
End Function
You can also call the CompactDatabase function from a Switchboard menu option. Set the menu item up like this via the Switcboard Manager:
Text: Compact and Repair Database
Command: Run Code
Function Name: CompactDatabase
end of document
