Compact and Repair Question

[AnnaZ]

I have an Access database that's on our in-house server. This database has never been compacted/repaired. Recently our server crashed (but we were able to retrieve the database). Does the fact that the database has not been compacted have anything to do with the server crashing? Could that have affected it in any way?
Also, how often should the database be compacted/repaired?

Thanks for your help!

 

[alastair69]

Compacting & Repairing is good practice, but it depends on the size of the Database,

Server crashing this could be due to the size of the database, it also may have corrupted the database, if the database runs and all is ok then the database is fine, but i would recommend that you do from time to time compact your database.

Alastair

 

[ghudson]

You should compact and backup your db on a daily basis. What version of Access are you using.

 

[AnnaZ]

thank you both.
the database is almost 7MB and it's MS Access 2003.

 

[Debased]

Here is a function that I got from this forum, if you search you can probably find more info about it but I use this in most of my databases. You can change the size you want it to run compact, for example this one compacts when a db gets larger than 5mb.

Public Function AutoCompact()
On Error GoTo Err_AutoCompact

If FileLen(CurrentDb.Name) > 5000000 Then '5 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

 

[AnnaZ]

thank you!

 

[ghudson]

The AutoCompact() function Debased posted above is my routine that I created to only compact a db if it reached a certain size. I use it to avoid compacting the db every time the db was closed since it does not always need to be compacted and it is annoying to a user to wait for an unnecessary compacting. It will not work with Access 97/95/2.

Auto Compact on Close or Not?

 

[Debased]

And in case I did not thank you gHudson, when I first found your compact code on this site: THANK YOU! I use this often and it has never failed and like you say, takes the hassel out of having to remember to do it and having to do it "manually"...