A boring, albeit important MS Access question

October 24, 2007 7:58 AM

Our main MS Access database seems to have ballooned mysteriously. Our membership database which was 12 megabytes as of at least April is now 78! We did not acquire 66 megabytes worth of new members, whats the deal?

Please assume for the purposes of this question that I am an idiot, but I will answer your questions as best I can to get a solution.

Thanks.

posted by BobbyDigital to Computers & Internet (4 answers total)

IIRC, Access doesn't delete the data behind the scenes when you edit it. You need to compact the database. Open it up, go to Tools > Utilities > Compact and Repair.
posted by desjardins at 8:06 AM on October 24, 2007

This has not-too-geeky details as to what this procedure accomplishes and why you should do it.
posted by desjardins at 8:09 AM on October 24, 2007

Back it up first.

I would suggest selecting Tools> Options, clicking on the General tab and select "Compact on Close". This will continually optimize your database, although it will add about 30 seconds to the shut-down time.

The only reason I advise against "Compact and Repair" is that sometimes the "Repair" part messes things up if your database doesn't have standard relationships/etc.
posted by JeremiahBritt at 8:19 AM on October 24, 2007

Response by poster: I should add that this database is using the access 2003 format and my colleague and I just switched to office 2007, actually this switch occurred around the time frame that this bloat happened which I would say is suspicious except for the fact that our other database, which entails our conference data was about the same size as our afore mentioned membership database and has been in use much more since the switch and it is still roughly the same size.

I will do the compact on close, and any further ideas would be greatly appreciated.

Thanks again.
posted by BobbyDigital at 8:38 AM on October 24, 2007