Access Database Back-up

[Sonya]

How do I set up my Access Database to automatically
backup? I have the original database on a network, but I
want to automatically backup the file under my folder (in
case someone deletes it/etc). I am coping the file
currently every couple of days. I hope this is the right
place to post this question, if not please tell me
where. Thank you.

 

[SteveD]

I have done something like this ..... Here's the concept.

Using the windows task scheduler, create a task that opens
Access and a specific database (like when you create an
icon that opens a specific database). When this database
opens and on your opening form, use the onOpen event (or
timer event) which calls a module that will compact or
copy your database(s) to your 'saved' location and when
completed, then quit your applciation.

Couple of notes: In code, you can not compact a database
to itself like you can when your in the database and use
the menu Tools / database utilities / compact. So this
becomes effectively a compact & copy.

OR

You could copy files as necessary with code.

I have added an additional name to the compacted database
(like a date/time stamp) when I run the process. You
could also build your module to cycle through a number of
databases.

Copy:
FileCopy "from location & DBName", "To location & DBName"

Compact:
DBEngine.CompactDatabase "from location & DBName", "To
location & DBName"

See Access Help for more specifics but you get the idea.

I think this is what your after. If more details are
needed to get you started, just ask.

SteveD
PS: this may sound complicated, but if taken in small
steps, it's not.

 

[Sonya]

What you posted sounded reasonable, but when I tried what
you said it didn't work. I know I am probably doing
something wrong. I set up the scheduler. Was the
database supposed to automatically open from the
scheduler, so I could put the code in the OnOpen field?
It never opened, so I went into the database and added
the copy part of the code below to the database. I am
new at the code thing.

 

[SteveD]

Break it down into small steps and get them to work, then
it will easily string together.

1) try setting up the code to a button for testing, then
move it to the form/timer when it works.

2) create an Icon to open your database - when it opens,
does it do what you wanted?

3) the scheduler - you need to set the program just like
the icon. Don't forget the network group & password.

....the program & the Icon(target) is something like...

"c:\program files\Microsoft office\Office10
\msaccess.exe" "C:\testing\autocompact.mdb"

4) sample of the code stuff (in a procedure)
for copying:
copyfile "H:\database\data.mdb", "C:\MyDatabases\data.mdb"

or

for compacting:
DBEngine.CompactDatabase "H:\database\data.mdb", "C:\MyData
bases\data.mdb"


Hope that helps you!
SteveD

 

[Sonya]

Thanks for your help.

-----Original Message-----
Break it down into small steps and get them to work, then
it will easily string together.

1) try setting up the code to a button for testing, then
move it to the form/timer when it works.

2) create an Icon to open your database - when it opens,
does it do what you wanted?

3) the scheduler - you need to set the program just like
the icon. Don't forget the network group & password.

....the program & the Icon(target) is something like...

"c:\program files\Microsoft office\Office10
\msaccess.exe" "C:\testing\autocompact.mdb"

4) sample of the code stuff (in a procedure)
for copying:
copyfile "H:\database\data.mdb", "C:\MyDatabases\data.mdb "

or

for compacting:
DBEngine.CompactDatabase "H:\database\data.mdb", "C:\MyDa ta
bases\data.mdb"


Hope that helps you!
SteveD

.

 

[Unc]

Our company runs totally on Access 97. Data backups are
incredibly important. Besides daily tape backups, and
copies to 2 different Near-Online storage devices I also
backup the most important MDB's every 30 minutes. Instead
of making a bunch of programming changes, etc. I just
setup a Task scheduler event to run an FTP script and
transfer the file every 30 minutes duging business hours.
This works perfectly whether people are in the MDB or not
(believe me, I've done lots of testing to prove this and
it works perfectly!).

A single 655 Megabyte .MDB takes 22 seconds to FTP on
gigabit ethernet. I keep copies from every half an hour
beginning at 6AM and stopping at 11PM. This way I have a
lot of backup "granularity" to go back on in the event we
need to restore from some type of corruption.

Hope this helps.

Unc

 

[SteveD]

I like that process. I may give it a try, so that I may
have my own back-up, in addition to the "back-up" by IT.

Thanks for another idea.