Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Microsoft: Access Modules (VBA Coding) FAQ

Compacting Databases

Compact and Repair automatically at the start of each day by BobStubbs
Posted: 30 Mar 04 (Edited 31 Mar 04)

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.


+    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


+    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


Private Sub Form_Load()
End Sub

Private Sub Form_Timer()
    Me.TimerInterval = 0
End Sub

Code within the Autoexec procedure


Form_Switchboard.TimerInterval = 3000

The Maintenance Check function


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
    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    -
Exit Function


MsgBox CStr(Err) & " - " & Error$
Resume MCEnd

End Function

The Compact Database function


'- 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..."). _

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

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close