How to know a Database needs compact and repair?

[Mahdi Jazini]

As you know in (Windows OS > Disk Defragment) there is a button with name of (Analyze)

Is in (VB6 > ADO) an option like this:

True (the database needs to compact and repair)

False (the database doesn't need compact and repair)

[dilettante]

This assumes Conn is open:

Dim Savings As Long

Savings = Conn.Properties("Jet OLEDB:Compact Reclaimed Space Amount").Value

You could use the Savings estimate value to decide whether to compact or not. However this tells you nothing about whether repair is needed.

But a "repair" operation is faster and more likely to succeed if not ignored for a long period of database updating. Thus it is good practice to "compact and repair" fairly often.

There is no crystal ball to tell you when to do it though. Don't wait for errors before doing it.