Automatic compact and repair with DB closed? (1 Viewer)

[G_Loc]

Is there some way to have my DB to compact and repair itself with no user intervention? I've seen the automation scripts and DB's, but they all require that a database that runs the script be open.

It will be used by numerous users so having it compact and repair upon closing is not an option.

 

[ghudson]

Searching this forum is a great way to discover and learn the answers to your Access programming questions.

Access Database Administration Utilities

Auto Compact OnClose

It will be used by numerous users so having it compact and repair upon closing is not an option.

Why? Access 2000/2002/2003 will not compact the db until the last user closes it [if the compact on close option is turned on]. A db should not need to be constantly compacted.

I run this function to only compact the db on close if it exceeds my preset amount when the db is being closed.

I use it to avoid compacting the db every time the db was closed since it does not always need to be compacted and it is annoying to a user to wait for an unnecessary compacting. This will NOT work with Access 2/95/97.

'Used with setting the status bar message
Public vStatusBar As Variant

Public Function CompactOnClose()
On Error GoTo Err_CompactOnClose

    If FileLen(CurrentDb.Name) > 5000000 And CurrentUser <> "programmer" Then '5 megabytes
        Application.SetOption ("Auto Compact"), 1
        Application.SetOption "Show Status Bar", True
        vStatusBar = SysCmd(acSysCmdSetStatus, "The application must be compacted, please do not interfere with the Compacting process!")
    Else
        Application.SetOption ("Auto Compact"), 0
    End If

Exit_CompactOnClose:
    Exit Function

Err_CompactOnClose:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_CompactOnClose

End Function