compact access database

Question

I have a 1gig access database, is there some sort of formula that gives a time frame for compacting an acccess db. I understand that it relays a lot on hardware, but assuming all things are equal is there some sort of way to know if 1gig will take 1hour or 20 minutes to compact? thank you

Answer 1

How long has it taken in the past?

You can watch it create the new database and watch its file size increase during the compact process.

if you think it's hung, you can kill it and try it again.

Answer 2

For a 1GB database, I'd suggest an RDP connection to the file server its living on and run the compact on that server. If it's an MDB, you don't need Access -- a vbScript can do it just fine, as Jet is installed on every copy of Windows from Win2000 on. If it's an ACCDB, then you could install the current version of the ACE and use that, but it might be easier to just install Access.

The reason I'd do it in the server's RAM is simply to avoid pulling the file across the LAN. A 100BaseT LAN is still going to be significantly lower I/O rates than between RAM and disk on the server. If it's GBit networking, that's a different ballgame, but for a 1GB file, I'd still not want to be compacting across the network.

And if your file is already 1GB, I'd be planning to upsize sometime soon, anyway. It's way too easy for something to get out of hand and bloat the file up to some significant portion of the 2GB limit.