Right Time To Compact

[devlinp]

Hi All,

Is there any way to determine the right time for compacting a database?

I mean say for example - is there any way (thru a property/system table) which I can refer to see how many records are marked for deletion?

This way - I can compact the database ONLY if there are large number of records marked for deletion?

I have searched all the places I could but could not get the answer I want.

Thank you,
Dev.

 

[Malcy]

My general preference would be to use Compact and Repair almost as often as possible. If it is a stand alone database then I would set to compact on close so it does it every time you close. Keeps it all cleaner and less suspect to corruption.
If a networked database not so easy, but still try to do as often as you can.
Hope this helps
Best wishes

 

[ghudson]

I use this in all of my applications. Adjust the file size to suite your needs. Just run it when your database on close routine is called.

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

 

[devlinp]

Guys...Thanks for your replies

PS: Ghudson - The suggestion you provided will serve my purpose and works like a treat. (Infact I picked up your code from one of your earlier posts and already using it).

But was wondering if you know any way of finding "how many records have been marked for deletion?" in a acess database.

Cheers,
Dev.

 

[The_Doc_Man]

There is no way to know what has been marked for deletion after-the-fact without decompiling Access code. And that, my friend, is a violation of your EULA. So unless you find some third-party evaluator for that issue, the answer is NO. You cannot tell how much is marked for delete. By the way, if there is a temporary record created while a query was open, even though it never resided in a true table, it is probably in the db file and is marked for delete.

So back to the question: How do you tell you need to compact/repair/service your DB? The correct answer from a theoretical standpoint is, if you see any overt symptoms other than the size of the DB itself, you already are overdue.

OK, so that was theory. In PRACTICAL terms, how do you tell?

There is no overnight solution. One way that I have found as a reliable indicator is to watch the DB grow. Set aside time for maintenance. Do a compact/repair cycle on it. See what it shrinks down to. Make a chart over time to determine how quickly it grows. It will be a saw-tooth graph, in all probability. You will see two types of growth. First, the "clutter/bloat" factor of temp records from temp recordsets, which is the sharp ramp of the sawtooth pattern. Second, TRUE growth of real data in the db - which is growth measured only from the bottom points of the sawtooth.

Then comes a bit of judgement. How big is too big? You must decide that based on personal preferences. In my main DB, when it grows by 50% due to clutter, it is time to trim it. But you have to take into account TRUE growth, which is never by itself a justification for trimming.

So we figured out that a weekly compact/repair cycle would be adequate to clean it up before it got to 50%. But another person at our site gets by with a monthly cleanup 'cause her db doesn't grow so fast. It is used more for a few small reports. Small reports = small recordsets = slower growth of temp space.

Let's just say this. If you have to ask whether it is time to clean up - it probably is.

 

[devlinp]

Thank you Doc Man for such a detailed reply - Appreciate your advice.

This is great information for me and will help in a great deal in planning my implementation.



Thanks once again,

Dev.