Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Microsoft: Access Modules (VBA Coding) FAQ

Compacting Databases

Auto Compact your Database when FileSize = x by BillPower
Posted: 6 Dec 02 (Edited 28 Mar 03)

Access Version 2000 and later only.

What this function does is look at the file size of the app itÆs being called from. If the file size is smaller than the size specified, it doesnÆt compact on close, if itÆs larger it will compact on close. I donÆt know if any of you have noticed this before, but when I deliver an application, say, when fully compacted the size might be 10 Mb, after using it a few times it might grow to 14 Mb, but there after will only grow possibly 100 Kb after each session. I donÆt know the reason for this, the size just seems to bottom out. Anyway when youÆve got this approx size that the file bottomÆs out at, in this example 14 Mb, put in a reasonable file size (one that wonÆt put a drain on system resources) that you want to allow the file size to grow to.

Put the following in a global module:

Public Function AutoCompactCurrentProject()
    Dim fs, f, s, filespec
    Dim strProjectPath As String, strProjectName As String
    strProjectPath = Application.CurrentProject.Path
    strProjectName = Application.CurrentProject.Name
    filespec = strProjectPath & "\" & strProjectName
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFile(filespec)
    s = CLng(f.Size / 1000000)  æconvert size of app from bytes to MbÆs
    If s > 20 Then                       æedit the 20 (MbÆs) to the max size you want to allow your app to grow.
        Application.SetOption ("Auto Compact"), 1  æcompact app
        Application.SetOption ("Auto Compact"), 0   æno donÆt compact app
    End If
End Function

Call the Function from the procedure that closes down your app, before e.g. Docmd.Quit:


Once itÆs installed (assuming the project is completed), you can forget about it. Also there is no need for error handling as all the function does is turn AccessÆs own in-built Compact on Close feature on or off. Should really give your apps the appearance of being more efficient only compacting, often, after weeks of constant use.

You never have to remember or remind a user to compact your apps again.

Bill Power

Back to Microsoft: Access Modules (VBA Coding) FAQ Index
Back to Microsoft: Access Modules (VBA Coding) Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close