Solved

To backup Access database on regular basis

Posted on 2010-11-12
10
494 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
 
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now