Automatically Compact and Repair Code

[Jakboi]

Hello,

I found this here:

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

This was in a 2004 post...does anyone have something similiar that may have more bells and whistles since that was 3 years ago? Dont know if something else was made by someone that works better?

Also does this go into a module?

I am looking to automatically compact and repair the Front-Ends on the Users workstations when it reaches a certain size...

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

If I wanted to change this is this in kilobytes or how would I change this to compact and repair when it reaches say 5 mb. Its currently at almost 2mb now I believe. Dont know what to change in order to change the size that triggers compaction.

Thanks for any insight to this.

 

[Moniker]

To compact, it's much easier (one line of code) using SendKeys:

SendKeys("%T D C")

To determine the file size, you need a little bit of code, found here:

GetFileSize Function

It will allow you to return the file size in bytes, kilobytes, or megabytes.

 

[Jakboi]

Thanks for the help.

Ok I am rather new so bare with me. So where do I put the:

SendKeys("%T D C")

Does it matter what I name the module?

Does this automatically compact based on size?



Thanks.

 

[boblarson]

Does it matter what I name the module?

No, name the module whatever you want and create a Function or Sub in that module to house the code.

Does this automatically compact based on size?

No, you have to still create code to check, but what I do is not check for size but create a table with just one number and I increment it each time the database closes. When it gets to the number I've selected as when I want it to compact, it resets the number and then runs the code to compact.

 

[Jakboi]

Thanks bob.

Do you happen to have a table example and the code to check?

So my module would just be this:

Public Function getFileSize(sFilePath As String, Optional sSize As String) As Long

   On Error GoTo ErrHandler
   
   Dim nByteSize As Currency
   Dim nFileSize As Currency
   
   Const KILO As Long = 1024
   
   nByteSize = FileLen(sFilePath)
   
   If (UCase$(sSize) = "M") Then
       nFileSize = nByteSize / KILO / KILO
   ElseIf (UCase$(sSize) = "K") Then
       nFileSize = nByteSize / KILO
   Else
       nFileSize = nByteSize
   End If
   
   getFileSize = nFileSize
   
   Exit Function
   
ErrHandler:
   
   MsgBox "Error in getFileSize( )." & vbCrLf & vbCrLf & _
       "Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
   Err.Clear

End Function

Then this would have to have something like this?

Private Sub Form_Unload(Cancel As Integer)

SendKeys("%T D C")

End Sub

If thats right then I could put that in my switchboard and that would be fine? So to trigger this I would have to push the letters "TDC"...not sure.

This may be over my head...

Thanks.

 

[RuralGuy]

I take it everyone has already looked at this thread. http://www.access-programmers.co.uk/forums/showthread.php?t=114688

 

[Jakboi]

I take it everyone has already looked at this thread. http://www.access-programmers.co.uk/forums/showthread.php?t=114688

Thanks that seems more my level...however yet need help. I put that code into my ON CLOSE on my switchboard and get this which obvioulsly doesnt work...but what would i have to do to fix this? I have tried a few things without sucesss. Thanks.

Error: Expected End Sub

Private Sub Form_Close()
Public Function CompactOnClose()
On Error GoTo Err_CompactOnClose

    If FileLen(CurrentDb.Name) > 5000000 And CurrentUser <> "programmer" Then '5 megabytes
        Application.SetOption ("Auto Compact"), 1
        Application.SetOption "Show Status Bar", True
        vStatusBar = SysCmd(acSysCmdSetStatus, "The application must be compacted, please do not interfere with the Compacting process!")
    Else
        Application.SetOption ("Auto Compact"), 0
    End If

Exit_CompactOnClose:
    Exit Function

Err_CompactOnClose:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_CompactOnClose

End Function
End Function

 

[RuralGuy]

Just change the following line:
If FileLen(CurrentDb.Name) > 5000000 Then

 

[Jakboi]

Just change the following line:
If FileLen(CurrentDb.Name) > 5000000 Then

Thanks.

Still does it. I put a database name it and gives me the ;

Error: Expected End Sub

Oh boy my name is 2 words with a space...is that a no no for database names because it doesnt like the space.

Giving me a error.

Expected list separator or )

 

[mawcluster]

Don't want to hijack this thread, but as a side note...

Is it safe to enable compaction on close? Is there some sort of pitfall associated with constantly compacting after every use of the database?

I have a FE/BE setup where I have users open Front End files in their seperate folders on the network. I will have these compact on close.

Also, I intend to make a utility button that remotely compacts the BE file once in a while when an administrator wants to and clicks the button.

Is there anything wrong with my intended implementation?

Also...when you select the option, "Compact on Close" does that preform only compaction...or does it perform a full compaction and repair operation...or are they essentially the same process?

 

[RuralGuy]

Private Sub Form_Close() 
'--- Public Function CompactOnClose() 
On Error GoTo Err_CompactOnClose 

    If FileLen(CurrentDb.Name) > 5000000 Then '5 megabytes 
        Application.SetOption ("Auto Compact"), 1 
        Application.SetOption "Show Status Bar", True 
        vStatusBar = SysCmd(acSysCmdSetStatus, "The application must be compacted, please do not interfere with the Compacting process!") 
    Else 
        Application.SetOption ("Auto Compact"), 0 
    End If 

Exit_CompactOnClose: 
    Exit Function 

Err_CompactOnClose: 
    MsgBox Err.Number & " - " & Err.Description 
    Resume Exit_CompactOnClose 

'--- End Function 
End Sub

 

[Jakboi]

Still no go.

It now says Exit function allowed in sub or property and highlights the spot in below:

Exit_CompactOnClose: 
Exit Function

Private Sub Form_Close() 
'--- Public Function CompactOnClose() 
On Error GoTo Err_CompactOnClose 

    If FileLen(CurrentDb.Name) > 5000000 Then '5 megabytes 
        Application.SetOption ("Auto Compact"), 1 
        Application.SetOption "Show Status Bar", True 
        vStatusBar = SysCmd(acSysCmdSetStatus, "The application must be compacted, please do not interfere with the Compacting process!") 
    Else 
        Application.SetOption ("Auto Compact"), 0 
    End If 

Exit_CompactOnClose: 
Exit Function 

Err_CompactOnClose: 
    MsgBox Err.Number & " - " & Err.Description 
    Resume Exit_CompactOnClose 

'--- End Function 
End Sub

Also for the

 If FileLen(CurrentDb.Name)

it doesnt like spaces...and mine is called Pipeline Reports.mde. Should I never include spaces in database names?

Thanks.

 

[RuralGuy]

Change the Exit Function to Exit Sub
...and YES, avoiding embedded spaces in names saves you from all kinds of problems. Here's some more tips while we are on the subject:
List of reserved words in Access 2002 and Access 2003
List of Microsoft Jet 4.0 reserved words
Special characters that you must avoid when you work with Access databases

 

[Jakboi]

Change the Exit Function to Exit Sub
...and YES, avoiding embedded spaces in names saves you from all kinds of problems. Here's some more tips while we are on the subject:
List of reserved words in Access 2002 and Access 2003
List of Microsoft Jet 4.0 reserved words
Special characters that you must avoid when you work with Access databases

Thank you it compiled in my switchboard form. Thanks for the info as well.

One more issue. When I exit my switchboard I now get this error in a popup window:

424 - Object Required

Any ideas?

Here is the code I have exactly:

Private Sub Form_Close()
'--- Public Function CompactOnClose()
On Error GoTo Err_CompactOnClose

    If FileLen(Pipeline.MDE) > 5000000 Then  '5 megabytes
        Application.SetOption ("Auto Compact"), 1
        Application.SetOption "Show Status Bar", True
        vStatusBar = SysCmd(acSysCmdSetStatus, "The application must be compacted, please do not interfere with the Compacting process!")
    Else
        Application.SetOption ("Auto Compact"), 0
    End If

Exit_CompactOnClose:
    Exit Sub

Err_CompactOnClose:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_CompactOnClose

'--- End Function
End Sub

In my view code this is in my Form and Close section.

Thanks.


Is it because of a status bar?

 

[RuralGuy]

The FileLen() function needs a fully qualified filename as a string so put back the line:
If FileLen(CurrentDb.Name) > 5000000 Then '5 megabytes
It looks like you have changed your MDB/MDE name to eliminate the space.
Go into the immediate window (^G while looking at the code) and type in
? CurrentDB.Name and see what you get back.

 

[Jakboi]

The FileLen() function needs a fully qualified filename as a string so put back the line:
If FileLen(CurrentDb.Name) > 5000000 Then '5 megabytes
It looks like you have changed your MDB/MDE name to eliminate the space.
Go into the immediate window (^G while looking at the code) and type in
? CurrentDB.Name and see what you get back.

Ruralguy your awesome thanks!

Now didnt know about this or not...but the the statusbar message doesnt go away when its finished...

How difficult would it be to make it change to some other text when done:

Like:

vStatusBar = SysCmd(acSysCmdSetStatus, "The application must be compacted, please do not interfere with the Compacting process!")

Could that be placed in this part with a different message like:

Compact Complete you can now close out the database.

Exit_CompactOnClose: 
    Exit Sub

My users are like 45+ year olds with no knoweledge of Access let alone computers...trying not to confuse them. They would probably let it sit there all day.

Any ideas?

Thanks.

 

[Jakboi]

I guess an easy way would be to change the text to this:

The Pipeline Database is under going routine maintenance, please do not exit until your mouse is no longer an hourglass!

 

[RuralGuy]

Sorry, but I never got an email notice for the last two posts. Since this code should be in the last event of the last form to close, I wouldn't think any additional messages are necessary. The very next step should be the compaction with its thermometer. Have you tried it with a real low byte count so it compacts each time and shows you what happens?

 

[Jakboi]

It compacts just fine. It just doesnt exit the database or close it down. I attached a screenshot of where it stops.

The message just stays there and didnt want to confuse my users.

ATTACHED.

 

[RuralGuy]

I duplicated your condition so I've been trying the code in several MDB's I have. It no longer stops with the screen you are showing. Go Figure! I have one MDB that is > 7 MB so I can see the compaction take place. Would you believe it works just as I would expect; namely it compacts with the thermometer and then exits Access if the ByteCount is too high. I have the code in the Close event of a form that loads hidden 1st thing when I open the db. It is also the last to close. So now we know it works. We just need to figure out why yours is acting the way it is.