• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 698
  • Last Modified:

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.
0
REIUSA
Asked:
REIUSA
3 Solutions
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"How often should I run compact and repair"
Daily.  C&R is the BEST possible preventative maintenance you can possibly do. Trust Me!

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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

mx
0
 
Rey Obrero (Capricorn1)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
http://support.microsoft.com/kb/209769/en-us
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"Does the compact on close also repair"

Yes.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If you're looking for automated processes you might consider this:

http://www.fmsinc.com/MicrosoftAccess/Scheduler.html
0
 
REIUSAAuthor Commented:
Thanks for the info.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now