auto-compact bypassed by docmd.quit (1 Viewer)

aidan · Aug 10, 2005

I'm trying to use a button to close a database which has compact-on-exit set. However docmd.RunCommand acCmdCompactDatabase won't run from a form (there are lots of threads about this here), and both Access.RunCommand acCmdExit and DoCmd.Quit both ignore the flag.

Is there a way around this? and why does it get bypassed anyway?

ghudson · Aug 10, 2005

That command will not compact a db in the manner that you want. Use DoCmd.Quit to close the db. Then also add my CompactOnClose() function to only compact the db if it is really needed when you are closing the db.

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

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

aidan · Aug 10, 2005

Wow, just what I was looking for. Thanks!

auto-compact bypassed by docmd.quit (1 Viewer)

aidan

Registered User.

ghudson

Registered User.

aidan

Registered User.

Similar threads

Users who are viewing this thread