Solved

To backup Access database on regular basis

Posted on 2010-11-12
10
502 Views
Last Modified: 2012-05-10
How to back up the MS Access database on the regular basis. let say evey day at 7 PM the database MYDB should be compacted and repaired and then backup in the folder I specified

Any experience? please
0
Comment
Question by:rfedorov
[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
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 100 total points
ID: 34122724
Fully automatic backup can be problematic, because if someone is in the db, wherein you can still backup, you will not be able to Compact & Repair - which is very important for preventative maintenance.

For starters, you can use the built in Access backup utility:

On the Access menu >> Tools>>Database Utilities>>Backup Database ...

Here are some other approaches:
http://www.fmsinc.com/MicrosoftAccess/monitor.asp

http://www.peterssoftware.com/cal.htm

http://www.everythingaccess.com/accessworkbench.htm

Final note:  Each of the products I listed has different functions and capabilities.  So, you have a good choice to find what fits your bill.

mx


mx
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 100 total points
ID: 34122911
Lot easier if work already done for you as MX as supplied ready made s/w

However, if you wanted to do it yourself you can use the various command line switches to achieve this task.
http://support.microsoft.com/kb/209207

You need to create a dos batch file then use Windows scheduler to run this batch script

In terms of backing up, do you always want to overwrite? My recommendation is do not keep overwrtiing (if that is what you plan to do). Just create a new folder based on the date and copy into that folder. Again within your dos batch script.
0
 
LVL 75
ID: 34122986
Since I have a 'semi automated' nightly backup that is run every weekday by myself or one of 3 other analysts, and I've seen the issues that do come up, I really have to recommend against a fully automatic, unattended backup ... unless you are going to put in all sorts of error trapping and error logging, such that you can see what 'really happened' for a given backup session.  

Otherwise, if you don't ... this is not going to be a reliable backup approach.  And I really don't think you have that flexibility with a dos batch file.  My utility is 100% Access, with full error trapping and error logging as well as logging the results of backing up & Compact & Repairing (currently) 25 mdb's.  Whereas the process is all point and (just a couple of) click(s), it is performed by a human.  This approach has been very successful over the last 2.5 years.

"My recommendation is do not keep overwrtiing "
Totally agree.  Add a Date/Time tag to the end of the mdb/acccdb name

MyCoolDb_20101112_1755.mdb

mx
0
Technology Partners: 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!

 
LVL 46

Assisted Solution

by:aikimark
aikimark earned 100 total points
ID: 34123515
I use the Microsoft JetComp utility to do the repair and compact.

I have a timer on the forms with a Timer event that looks for a Stop.flg file commonly accessible to all the front-end databases.  The big batch job follows a much smaller batch job that renames the file.  The user is prompted to stop their use of the application.

I create a backup copy using the COPY command in the batch file.
0
 
LVL 75
ID: 34123758
I don't think JetComp works on A2007/10.  Also, using the Copy command, I think you get an error if the db is open.

I use the File System object to copy.

mx
0
 
LVL 46

Expert Comment

by:aikimark
ID: 34123868
>>I don't think JetComp works on A2007/10
The Access version wasn't specified.  But it is good to know that it doesn't work with the 2007/2010 versions.

This utility only works up through A2007
http://www.vista-files.org/programs/datanumen-inc/advanced-access-repair.html

0
 

Assisted Solution

by:mpb8
mpb8 earned 200 total points
ID: 34124060
So far in using access 2007, whenever I do make a database, I make a point to go to Access Options from Menu --> Select Current Database and Select 'Compact on Close' . In your case, I would simply write a batch file that opens your specified database, run a macro (which just will Access to exit), upon closing compact your database and once done, execute a line in the same batch file to copy it in at the given destination. Works fine for me since past 1 year or so, but again this is a quick and easy way.

"No offense to anyone. "
0
 
LVL 75
ID: 34124151
Actually, Compact on Close has several issues, and it only works on the db you are in.  It does not C&R a linked backend db, which is likely the case here.  And even if you have a shared frontend (not recommended) on a server connected to a common back end, C on C only works when the last user exists ... and Compacting and Repairing across a network is not recommended either.

In the utility that I referred to, I copy db's to the local workstation, run C&R, and then copy back up to the server.

mx
0
 
LVL 75
ID: 34124161
"last user exists " >>> Last user exits ...

mx
0
 

Assisted Solution

by:mpb8
mpb8 earned 200 total points
ID: 34124304
I think so, if you make a batch file. and include all the steps to compact and copy it, it should not matter. I have been compacting and copying the db's for over an year, unattended and it is working perfectly fine. I understand your point, agree to it, but I recommended the solution, only because I thought it was something that I face an year ago.
0

Featured Post

Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

622 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