Solved

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

Posted on 2013-01-22
7
679 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
7 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) 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 119

Assisted Solution

by:Rey Obrero
Rey Obrero 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article will show you how to use shortcut menus in the Access run-time environment.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
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…

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now