Repair and Compact

[chezealot9]

Hey guys, Im working with a database a coworker created and he set it up so it runs compact and repair when the database closes, is this typically worth it? If there is a significant value in it then Ill add it to some other databases if not then its a nice 8 seconds on closing that I wont miss.

Greatly Appreciate Any Help

 

[neecie]

Hi chezealot9,
The answer to your question depends on how often you modify your code, how often it is accessed, etc.

I have a timeclock program that runs continuously (and hadn't been modified for over a year).
I had to make a change recently and the db compacted from 8M to 212kb!

I compact a database every time I close it, and will occasionally create a new adp, import everything from then replace an existing adp.
File sizes have gone from 65+M to about 12M - 14M.

Compacting is one of the easiest ways to maintain some semblance of performance in Access

 

[BobJacksonNCI]

chezealot9, (Wisconsin?)

Probably not...

I don't know what happens with the option set and multiple users - when they exit the database individually.
Plus, you run the risk of someone who turns off their PC when they leave turning it off before the compaction has finished. (It's not supposed to cause any problem other than leaving a work file open. But...)

For lightly used databases, I perform the compaction myself whenever I make a maintenance change, having it opened exclusively at the time. Or when the mood strikes me.

For active databases (lots of inserts/deletes), I use the Windows task manager to create my own backup copies in the early hours. Then I have compaction performed on the just copied database, if I think it's necessary. This does require some work: You have to have Task scheduler on your PC. It's on the installation CD, but not automatically installed. Operating it is pretty simple. But you also have to write DOS batch files to do the actual steps.
In my case it performs a copy and then compacts the database.

Recommendation: Control it yourself manually unless it is VERY active, and thus growing large. Then click your "my computer" icon and see if the window that opens has a "Scheduled Tasks" icon. If it does, you can read the built-in help file.

Post a reply here if you need more help with the task scheduler and/or an example .BAT file.

Bob

 

[PhysicsBloke]

Can someone tell me how to use SQL to optimize performance of 10,000-record databases? I heard there might be a way to create a "reservoir", making the database effectively smaller and improving performance.

Any suggestions will be greatly appreciated.