Solved

To backup Access database on regular basis

Posted on 2010-11-12
10
498 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 - Access MVP) 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 45

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 45

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

785 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