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