Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

To backup Access database on regular basis

Posted on 2010-11-12
10
Medium Priority
?
504 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

971 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