How to Compact ms Access 2000 in vba code

[Hemish]

Hi,

Does anyone know how to compact a ms access 2000 db in vba code. I want the database to compact evertime it is opened up.

Thanks

 

[jrjr]

If you would settle with compact on close, go to tools/options and I think the general tab. There should be a checkbox there for compact on close.

 

[ghudson]

Hemish,

Your suggestion is a bad idea. You should only compact a db when it is being closed and only compact it if gets too big. Making your users wait for a db to compact every time they use your db is not considered a "user friendly application".

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

'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

Searching this forum is a great way to answer your own questions while learning how to program in Access.