[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2013-01-22
7
Medium Priority
?
696 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 936 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 400 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

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 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 664 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

656 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