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

Back to Microsoft: Access Modules (VBA Coding) FAQ Index
Back to Microsoft: Access Modules (VBA Coding) Forum

My Archive

My FAQ Archive

Come Join Us!

Posting Guidelines

Jobs

Microsoft: Access Modules (VBA Coding) FAQ

Compacting Databases

CODE

CODE

CODE

CODE

My Archive

Join Tek-Tips® Today!

Join Tek-Tips^® Today!