INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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!

*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.

Jobs

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.

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

Resources

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