Solved

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

Posted on 2013-01-22
7
690 Views
Last Modified: 2013-01-26
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
Comment
Question by:REIUSA
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 234 total points
ID: 38807692
"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
 
LVL 75
ID: 38807703
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
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 100 total points
ID: 38807708
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:REIUSA
ID: 38807916
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
 
LVL 75
ID: 38807939
"Does the compact on close also repair"

Yes.
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 166 total points
ID: 38809704
If you're looking for automated processes you might consider this:

http://www.fmsinc.com/MicrosoftAccess/Scheduler.html
0
 

Author Closing Comment

by:REIUSA
ID: 38823011
Thanks for the info.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question