compact on close (XP)

[bigsteve520]

i would like to give the user the option to compact on close when exiting the application. I have placed a check box on my confirm exit dialog to either close or compact & close but cannot seem to get the code to run. Any help would be appreciated...

[HansV]

Use this instruction, where chkCompactOnClose is the name of the check box on your dialog:

SetOption "Auto Compact", Me.chkCompactOnClose

For more info, click on SetOption in your code and press F1. The "Set Options from Visual Basic" link in the Getoption/SetOption help takes you to a list of the name of the option in the Tools | Options... dialog and the corresponding argument for GetOption and SetOption.

Note: don't do this in a multi-user setting.

[D Willett]

<P ID="edit" class=small>(Edited by D Willett on 30-Oct-03 13:26. Screen view size
)</P>Here's a little utility I came across which will automaticaly
compact the current database if the db size > 10meg.
Create a new module and paste the following:

<pre>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

[HansV]

This code looks fine by itself, but in some corporate network environments, Scripting has been disabled to diminish the chance of hackers doing harm. Since this code uses Scripting.FileSystemObject, it won't run in such an environment.

Instead of

strProjectPath = Application.CurrentProject.Path
strProjectName = Application.CurrentProject.Name
filespec = strProjectPath & "" & strProjectName

you can also use

filespec = CurrentProject.FullName

[charlotte]

Hans,

It will work in a multiuser setup. The compact on close will only happen when the last person closes the database. However, that assumes a shared front end, which is what will be compacted by the option. If the desire is to compact a back end data file, you would have to open that project in code and set its option for Auto Compact.

[HansV]

Charlotte,

I know that compact and close will only run when the last user closes the database.

My remark was meant for a non-split database, or a shared front end. It doesn't make sense to me to give each user the choice to turn the "compact on close" option on or off when he/she exits the database.

[MarkD]

You can also use the VB FileLen function to return length of file in bytes. Example:

Sub TestFileLen()

Dim lngFileLen As Long

lngFileLen = FileLen(CurrentProject.Path & "" & CurrentProject.Name)

Debug.Print "File Size in Bytes: " & lngFileLen
Debug.Print "File Size in KB: " & Format(lngFileLen / 2 ^ 10, "#.00")
Debug.Print "File Size in MB: " & Format(lngFileLen / 2 ^ 20, "#.00")
Debug.Print "File Size in GB: " & Format(lngFileLen / 2 ^ 30, "0.000000")

End Sub

Note, according to Help, function returns size of file before it was opened, but testing with open database function seemed to reflect increase in file size after importing records, etc. I'm not sure what the "official" definition of KB, MB, GB etc is, I always understood it to be the number of bytes divided by 2 ^ X, as shown above. In test with open db returned these results:

File Size in Bytes: 4349952
File Size in KB: 4248.00
File Size in MB: 4.15
File Size in GB: 0.004051

When looked in Windows Explorer, file size was listed as 4248 KB, so if you want file size to reflect what's shown in Explorer recommend divide by 2^20 to derive file size in MB.

HTH