Restoring a Back End Database File

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

thebiggermac - 17 May 2007 17:06 GMT

How, or what is the simplest method of backing up and restoring a back end
database file? It must be done via an Access form, perferrably a one-button
click. One button to backup, one button to restore. (Backup location is not a
critical element at the moment, just the "how to".)

Using Windows or Explorer for restoring or overwriting the file is not an
option.

Reply to this Message

Eric Blitzer - 17 May 2007 20:41 GMT

We presently have our databases backed up through network procedures. If any
one has tried to have your IT departemnt restore a file you find out it takes
hours or a day. I have set up, using windows task scheduler, a daily backup
that runs at 8:00PM. If there is a problem I can immediatley get access to
the previous nights backup. Restoring a database is a very easy process but
one that fortunatly I only had to do once. If I need to restore I simply
overwrite the db on the server. It would be easy to automate.
Everytime you restore a database all information that was entered since the
last backup has to be re-entered.
If you are constantly having to restore you databse than thier is a problem
with your application or network.

Bottom line
Trust no one
Backup you databases yourself.

> How, or what is the simplest method of backing up and restoring a back end
> database file? It must be done via an Access form, perferrably a one-button
[quoted text clipped - 3 lines]
> Using Windows or Explorer for restoring or overwriting the file is not an
> option.

Reply to this Message

David W. Fenton - 18 May 2007 21:41 GMT

> Bottom line
> Trust no one
> Backup you databases yourself.

Don't even trust yourself! :)

Have multiple backups that you create yourself, because one ore more
of them will sometimes fail for some reason or the other.

Signature

David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Reply to this Message

John W. Vinson - 17 May 2007 20:56 GMT

>How, or what is the simplest method of backing up and restoring a back end
>database file? It must be done via an Access form, perferrably a one-button
[quoted text clipped - 3 lines]
>Using Windows or Explorer for restoring or overwriting the file is not an
>option.

Then you're in real trouble. A backup of an open database *cannot* be relied
upon. It's like repairing your fuel injector pump while the car is running.
Since Access has no way of ensuring that there isn't some table open for
modification, by this user or *SOME OTHER* user, it's just not safe!

Why can't you use Explorer (even indirectly, say from another database)? Would
having a Windows Schedular operation run automatically work?

John W. Vinson [MVP]

Reply to this Message

Eric Blitzer - 17 May 2007 21:07 GMT

John,

I forgot to mention I check to see that no one is in the database. that's
why I run it a night. I have had great success using the windows task
scheduler. What drawbacks do you see. I guess another way would run
replication but then you run the risk of replicating a corrupted db.

> >How, or what is the simplest method of backing up and restoring a back end
> >database file? It must be done via an Access form, perferrably a one-button
[quoted text clipped - 13 lines]
>
> John W. Vinson [MVP]

Reply to this Message

thebiggermac - 17 May 2007 21:40 GMT

Thanks for all the input but I guess I should clarify some items:

1. This is a stand alone database which only one person will be using.

2. The program can be placed on multiple machines (i.e. a desktop and laptop).

3. The program requires a backup and restore feature. If I update the laptop
then I want to sync up the desktop. I need a way of doing this. A backend
data file seemed to be the way to do this but I am having trouble making it
work.

4. It has to be seemless. A one button click to backup, a one button click to
restore/import data.

Due to the way the database is created I have utilized one-to-one and one-to-
many relationships which must be maintained as they are in order for the
program to work in the desired fashion. I have exported the files to a txt
file and it works as advertised. However when I import those same files I am
running across all types of Primary key violations, preventing me from
updating the values.

One of the issues I have resolved is to use a delete query to zap the
existing data then import the backed-up data from the txt file. It works with
no errors. Maybe not the best way of handling a backup and restore feature
but so far it is the only solution I have found that works.

I was told that splitting the database and then saving the backend would work.
So far I have not been able to make it work. Hence my original question.

This database is NOT going on a server but rather on a desktop, laptop, or
both. Since most of my users are not computer gurus, but rather doctors,
lawyers, and what nots, I need to make this as dummy proof as possible. My
problem is my lack of Access/VBA knowledge. Hence I am my own worse enemy.

Hope this helps.

Reply to this Message

John W. Vinson - 18 May 2007 02:13 GMT

>Thanks for all the input but I guess I should clarify some items:
>
>1. This is a stand alone database which only one person will be using.
>
>2. The program can be placed on multiple machines (i.e. a desktop and laptop).

Those are almost contradictory specifications. If you have the database on
multiple computers, what is to prevent multiple users from USING them? If
you're in an office, can you be *absolutely certain* that there will *never*
be two doctors, or a doctor and an administrative assistant, using the
database simultaneously?

>3. The program requires a backup and restore feature. If I update the laptop
>then I want to sync up the desktop. I need a way of doing this. A backend
>data file seemed to be the way to do this but I am having trouble making it
>work.

Will the laptop be attached to the network, or truly remote (out in a
patient's home or a non-computer wired facility)?

>4. It has to be seemless. A one button click to backup, a one button click to
>restore/import data.
[quoted text clipped - 5 lines]
>running across all types of Primary key violations, preventing me from
>updating the values.

You should almost certainly be backing up *the entire database* - the .mdb
file, probably the backend .mdb file containing the tables. Backing up
individual tables is orders of magnitude more complicated (as you are
discovering).

>One of the issues I have resolved is to use a delete query to zap the
>existing data then import the backed-up data from the txt file. It works with
[quoted text clipped - 3 lines]
>I was told that splitting the database and then saving the backend would work.
>So far I have not been able to make it work. Hence my original question.

What specific problems have you had? Check the suggestions at

http://www.granite.ab.ca/access/splitapp.htm

for suggestions about how best to set up a split application. It is not
necessary to have "a server" in the sense of Windows Server operating system -
a peer to peer network with one machine functioning as a shared resource is
fine.

>This database is NOT going on a server but rather on a desktop, laptop, or
>both. Since most of my users are not computer gurus, but rather doctors,
>lawyers, and what nots, I need to make this as dummy proof as possible. My
>problem is my lack of Access/VBA knowledge. Hence I am my own worse enemy.
>
>Hope this helps.

You're asking in the right place - most of us have been there!

John W. Vinson [MVP]

Reply to this Message

John W. Vinson - 18 May 2007 02:06 GMT

>John,
>
>I forgot to mention I check to see that no one is in the database. that's
>why I run it a night. I have had great success using the windows task
>scheduler. What drawbacks do you see. I guess another way would run
>replication but then you run the risk of replicating a corrupted db.

I have code that checks to see if there is an open .ldb file (indicating
either an active user or possibly a corrupt database) and if so, sending a
message to an administrator, writing a file to disk with the details, and
aborting the backup.

John W. Vinson [MVP]

Reply to this Message

John Nurick - 17 May 2007 21:32 GMT

Assuming you mean backing up the back end to which the front end with
the "backup" and "restore" buttons is connected, there is no method that
is both simple and reliable.

Basically you need to ensure that no one (including yourself) is
connected to the back end, and then compact and copy the back end mdb
file. If you copy it while it's in use, you can't be sure that you're
not doing it while someone is editing a record, or a query is running,
or something - so your backup can't be relied on.

Allen Browne MVP knows much more than I do about this sort of thing.
Here's his take on it from a newsgroup post a couple of years ago:

I don't think there is a safe way to backup an Access database
while it is in use.

It's not difficult to run a routine that loops through the tables
and copies them out to another database. Happy to provide more
info on that, but the problem is that the copy may be inconsistent.
For example, if it loops through the TableDefs, copies Table1, Table2,
etc, but before it gets to Table24 a user deletes a record. If there is
a cascading delete back to Table2, your copy is now inconsistent due to
the time delay between copying Table2 and Table24.

For the whole thread, search Google Groups for
"<%23oLcl1NBFHA.4072@tk2msftngp13.phx.gbl>"

>How, or what is the simplest method of backing up and restoring a back end
>database file? It must be done via an Access form, perferrably a one-button
[quoted text clipped - 3 lines]
>Using Windows or Explorer for restoring or overwriting the file is not an
>option.

--
John Nurick [Microsoft Access MVP]

Please respond in the newsgroup and not by email.

Reply to this Message