Automise Compacting a Database

[flip_33_]

Hi Everyone.

I have a database which I have designed that many users have access to, so it gets quite large very quickly...Does anyone have any code that will result in the database compacting evertime a user opens it or closes it.

 

[Joe4]

No code is necessary.

Go to Tools | Options.
On the General tab, there is a check box for "Compact on Close". Simply check that box.

 

[Dugantrain]

You have to be careful with this, though, with multiple users. If someone exits the database, then the database begins the Compact and Repair. If someone else opens the database file while it is in the process of Compacting/Repairing, then the db will copy itself, name the new, Compacted copy "DB1", and the original will not be Compacted/Repaired. I've had cases where, within 5 minutes, I had 4 or 5 copies of the same database within the same folder (DB1, DB2, DB3, etc.) because of users entering/exiting the db simultaneously! Because of this, I don't use the Compact On Close option; I just do it myself manually once a week or so.

 

[Joe4]

I guess I haven't experienced that to be much of a problem because my database only takes 15 seconds to close. I get a copy every once in a while, but simply delete when I am backing up the database to another server.

I guess it really depends upon how big the database is, and how many people are using it.

 

[Dugantrain]

Right, that is a good point. At the time, mine was about 20mb, about 150,000 or so records.