MS Access 2007 DB, maintenance, compact and repair many DB's at once

I have a simple inventory DB I work with, only about 8,500 records. I have been using it a few years and recently realized I had not compacted and repaired in a long time, the DB was 35 MB and after compacting and repairing it is 2.5 MB.

I keep numerous back ups of the DB, pretty much every time any amount of data is entered or changed I back it up and leave these files in place. I have about 95 backups of the DB at several different dates and times over the years.

1. I made the change to allow it to compact on close. How often should I run compact and repair. Are there any other maintenance things I need to do for a MS Access DB to keep it up to snuff and running smooth?

2. Is there a way to bulk compact or bulk compact and repair all 95 backup DB files so it doesn't take up so much space?

Thanks for your help.
Who is Participating?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
"How often should I run compact and repair"
Daily.  C&R is the BEST possible preventative maintenance you can possibly do. Trust Me!

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Oh ... there is no specific other maintenance you can do per se ... other than be sure you have a stable network, etc.

" Is there a way to bulk compact or bulk compact and repair all 95 backup DB files so it doesn't take up so much space?"

You could write some code using the File System Object to loop through whatever folders and execute a C&R

Rey Obrero (Capricorn1)Connect With a Mentor Commented:
not sure, why you need those 95 backups.

if you are continuously using the db, the last backup or the last 2 or 3 backups is enough.

see this link

Defragment and Compact Database to Improve Performance
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

REIUSAAuthor Commented:
Does the compact on close also repair or do you have to manually do that?

I was burned one time when the backups of a database didn't have records that were deleted somewhere along the way so since the files are actually pretty small I keep the 95 or so copies that span a time frame of three years, about 2 or 3 a month, so I can go back to any one of those time stamps and check the DB if there is a problem later on down the road. Think, redneck shadow copy.

If someone deletes or messes up data un-noticed and then you back that up your backup is bad.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"Does the compact on close also repair"

Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
If you're looking for automated processes you might consider this:
REIUSAAuthor Commented:
Thanks for the info.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.