Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Oct 2003
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    compact on close (XP)

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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: compact on close (XP)

    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.

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: compact on close (XP)

    <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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: compact on close (XP)

    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

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: compact on close (XP)

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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: compact on close (XP)

    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.

  7. #7
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: compact on close (XP)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •