Monitoring Access DB Size

[hmongie]

Hello all.

I have a large db. I need something to monitor the size of it. For example the db is saved on a server and used throughout the day. What I need is something to email me an alert when the db reaches 900mbs. Then I can compact and repair the database back down.

I do purge it annually though. Currently it is about 750 mbs.

Any thoughts, please let me know. Thanks much.

Using Access 97, 2000, and 2002.

 

[hooks]

I have used the below function to compact the database when it is over a certain size.

Thanks to Ghudson

'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

 

[hmongie]

Thanks for the post, but I needed something that would send me an alert since I am not using it all the time. The db is used by 20 people constantly. If i have to compact it, then I will have to get everyone to log out.

I need something to email me an alert when the database size bloats up to a certain size. One of the database is in access 97, which the size limit is up to 1gig. Daily, it gets to about 980mbs. I would like to be prompted when it gets to like 950 so that I have the users log off so that I can compact it. This would have to be though an alert by email since I don't away have the database opened.

Any ideas, thanks.