Auto Compact your Database when FileSize = x

[color red]Access Version 2000 and later only[/color].

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
Else
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:

AutoCompactCurrentProject


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