Auto Compact on Close or Not?

[Debased]

Quickie: Do you folks recommend to set most databases to compact on close? If not, when would you NOT want to compact on close. Any other comments also appreciated.
Thanks

 

[stormin_norm]

Quickie: Do you folks recommend to set most databases to compact on close? If not, when would you NOT want to compact on close. Any other comments also appreciated.
Thanks

I like the manual method.
I would train the 'admin' user to run the compact manually once a week or once a month. Depending on the how large the db grows per day/week/month.

Plus, the admin user needs exclusive access. Would this person be the last one logging off everynight?
Plus-Plus,Some users may get impatient on a friday night if the compact takes too long. Maybe they think access is stuck and they just turn off their computer and head out for the weekend.

Anyone want to throw a vote for the automated side?

 

[Daveswanton77]

You could always set the database to compact only when it reaches a defined size using the code below.

Public sub subCompact()
On Error goto Err_subCompact

Dim fs, f, ProjectSize, 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)

ProjectSize = Round((f.Size / 1024) / 1024, 2)

If ProjectSize > 30 Then
Application.SetOption ("Auto Compact"), 1

Else
Application.SetOption ("Auto Compact"), 0

End If

Exit_subCompact:
Exit Sub




Err_subCompact:
Msgbox err.description
Resume Exit_subCompact


End sub

 

[stormin_norm]

Nice Dave!
Makes me think of a good one for my customer- Check day of week and compact on friday afternoon.

 

[Kevin_S]

Bravo Dave~
That's a nice bit of handy work - may want to make a post in the code archieve and hang a copy of this little gem in there...

Thanks -
Kev

 

[ghudson]

How about this for a few less lines of code?

Public Function AutoCompact()
On Error GoTo Err_AutoCompact
    
    If FileLen(CurrentDb.Name) > 30000000 Then '30 megabytes
        Application.SetOption ("Auto Compact"), 1
    Else
        Application.SetOption ("Auto Compact"), 0
    End If
    
Exit_AutoCompact:
    Exit Function
    
Err_AutoCompact:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_AutoCompact
    
End Function

 

[Debased]

Thanks for your replies! I knew I could count on you all!

 

[Debased]

How about this for a few less lines of code?

Public Function AutoCompact()
On Error GoTo Err_AutoCompact
    
    If FileLen(CurrentDb.Name) > 30000000 Then '30 megabytes
        Application.SetOption ("Auto Compact"), 1
    Else
        Application.SetOption ("Auto Compact"), 0
    End If
    
Exit_AutoCompact:
    Exit Function
    
Err_AutoCompact:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_AutoCompact
    
End Function

I placed this in a separate module however it does not work. I am using Access 2002, WinXP. Any suggestions?

 

[Debased]

I answered my own question: I called the function in the "QUIT" command button code that is on my switchboard and it works fine.

Thanks again.

 

[Newman]

I made a vbscript that is run every night using the "Task planner" ( Is that how you call it in English? ). So the database is always compacted in the morning.

Dim Compacting

Const AccessPath = "C:\Program Files\Microsoft Office\Office10\MSACCESS.EXE"
Const UserID = "******"
Const UserPassword = "******"
Const DatabasePath = "\\Server\Path\Database.mdb"
Const SecurityPath = "\\Server\Path\System.mdw"

Set Compacting = WScript.CreateObject("WScript.Shell")
Compacting.Run """" & AccessPath & """ """ & DatabasePath & """ /wrkgrp """ & SecurityPath & """ /compact /user """ & UserID & """ /pwd """ & UserPassword & """", 3, True

Of course, it works if you don't have a night shift team.

 

[ghudson]

Launching a shortcut will also let you do the same thing. Set your shortcuts "Target:" field to something like this will compact the db when the shortcut is opened...

"C:\Program Files\Microsoft Office\Office\msaccess.exe" /wrkgrp "X:\YourWorkgroupFile.mdw" "X:\YourDatabaseFile.mdb" /user YourUserID /pwd UserPassword /excl /compact

This is what I run when I want to quickly repair and compact a db from a simple shortcut...

"C:\Program Files\Microsoft Office\Office\msaccess.exe" /wrkgrp "X:\YourWorkgroupFile.mdw" "X:\YourDatabaseFile.mdb" /user YourUserID /pwd UserPassword /excl /repair /compact

But you can not do a repair "unattended" since it gives you an OK message box after the repair was run.

HTH