[AccessD] Keeping your db in tiptop shape

Hi Susan,

The most important thing I do with Access backend files is to set up an
automatic compact & repair.

1) Set your BE.mdb file to 'Compact on Close' in Options | General.

2) Create an Access database with a function called 'CompactBE' in it which
gets initiated from an AutoExec macro.  Add a table with one row and one
field to store the full path to the BE.mdb file.  Name this database
AutoCompactBE.mdb or something.  Put this code into a standard module:

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Public Function CompactBE()
On Error GoTo EH

    Dim stgPathBEFile As String
    Dim stgPathBELDB As String
    Dim appAccess As Access.Application
    Dim fso As FileSystemObject
    Dim stg As String
    Dim rst As DAO.Recordset
    
    stg = "SELECT BEFullPath FROM tblBEFullPath"
    Set rst = DBEngine(0)(0).OpenRecordset(stg, dbOpenSnapshot)
    stgPathBEFile = rst("BEFullPath")
    rst.Close
    Set rst = Nothing
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    '-- If the BE file is in use then the BE can't be compacted
    stgPathBELDB = Replace(stgPathBEFile, "mdb", "ldb")
    If fso.FileExists(stgPathBELDB) Then
        Exit Function
    End If

    Set appAccess = New Access.Application
    
    '-- Open the BE file    
    appAccess.OpenCurrentDatabase stgPathBEFile, False
    
    Sleep 5000
    
    '-- When the BE closes it will auto-compact.
    appAccess.CloseCurrentDatabase
    
    Sleep 5000
            
    appAccess.Quit acQuitSaveNone
    Set appAccess = Nothing
    
    DoEvents
    
    DoCmd.Quit acQuitSaveNone

    Exit Function
    
EH:
    DoCmd.Quit acQuitSaveNone
    
End Function

3) On the server where the BE.mdb file is stored, create a scheduled task to
open the AutoCompactBE.mdb file at a day/time when the BE.mdb file is
unlikely to be in use.

I've had good luck with this - no corrupt data for several years now at 4
separate customers.

HTH,
Dan


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan Harkins
Sent: Tuesday, April 14, 2009 11:33 AM
To: AccessD at databaseadvisors.com
Subject: [AccessD] Keeping your db in tiptop shape

I'm going to write about tasks that keep a db in shape -- the db doesn't 
have to be Access, but can be specific to Access or any database really. If 
it works out, I'll give credit where possible -- I like doing that and the 
readers like it too, but I can't promise anything.

Susan H.