Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

To backup Access database on regular basis

Posted on 2010-11-12
10
Medium Priority
?
505 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
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 400 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 400 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
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.

 
LVL 46

Assisted Solution

by:aikimark
aikimark earned 400 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 800 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 800 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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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

580 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