compact and repair database (1 Viewer)

associates · Feb 1, 2006

Hi,

I was wondering if anyone might be able to help me. What i'd like to do is to be able to get the access to trigger compact and repair database tool automatically just before it shuts. Is it feasible? or has to be done manually?

Thank you in advance

wazz · Feb 1, 2006

tools/options/general tab/compact on close.

also search these forums for 'compact and repair' (or compact or repair separately). it was discussed fairly recently.

ghudson · Feb 1, 2006

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

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. It will not work with Access 97/95/2.

Code:

'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

compact and repair database (1 Viewer)

associates

Registered User.

wazz

Super Moderator

ghudson

Registered User.

Similar threads

Users who are viewing this thread