Backing Up an Access97 Database

[Tim Rogers]

I've recently been given the responsibility for backing up our
Access97 Database application. It is a 'split' application with
forms/queries in one database (which is stored/launched locally on the
client PCs) and a backend data-only database on the server. This app
is our lifeblood here, so I want to make sure I get this right.

Right now the backup happens at night. Apparently, users were not
shutting down Access97 when they left for the day and the backup was
unable to backup the data-only file on the server (I assume because it
was locked). So, a process was created where the database files were
copied, in the middle of the day, to another drive. This drive was
backed up and the problem of users having that file locked was not an
issue anymore.

This seems like a bad practice. I'm concerned that when this copy is
occuring someone could be manipulating the data and the copy could be
in a corrupt state. Could this practice lead to a corrupt copy of the
database?

Also, I've read that I should also back up a system.mdw file. As far
as I can tell there is no system.mdw file nor any other .mdw file on
the server. Is this possible?

In the end, I'm looking for the proper way to back up our Access97
database (application). I assume the correct solution is to have
everyone close Access97 before they leave for the day and then backup
the data file on the server.

Any help would be much appreciated.

Thanks,

Tim Rogers

 

[Pete Sheridan]

Hello Tim,

All MS Access split databases (with a backend (BE) and a
front end (FE)) are two or more seperate files.

Backing up the front end should be very easy since you
have a seperate copy on each client PC. My assumption is
that you have a common FE (same FE File) on all of the
client PC's and that you have a 'Boilerplate' copy on the
server. Just copy the 'Boilerplate' copy to the backup
medium.

Now the 'hard part'.
It is recommended that all users close Access when they
have ended their work day. This will insure that they
accomplish the following, 1) They finish updating the last
record that they have been working on and 2) That the last
record will be written back to the BE data file.
It is very bad practice to allow any user to leave the db
open for an extended length of time unless they are
actively using the db. If their system crashes during the
night, the 'LDB' file will not be updated and this may
corrupt the db (one of the more common methods to crash a
db). One other reason to close the db when not using it
is to prevent locking problems (a very discourtious way to
work with your fellow users).

Go to the Access Knowledge Base and look up the 'LDB
Viewer'. This is an application (provided by Microsoft
Support) that I use constantly to determine who is signed
into an Access database. I use LDB Viewer to make sure
all users are out of the DB before I make updates to the
front-end. Nothing 'frosts me' more than to be blocked by
an active user in the 'ldb' file after everyone has left
for the night. Usually, the active user just shut off the
system without closing Access (the second most common
method of causing DB corruption). The 'LDB Viewer' will
also tell you which user('s) leave their DB's open when
they go home. This will enable you to close access on
their systems (if you want to) and then proceed with
making a backup copy of the BE database.

One other thing about a backend db (the corporate data).
It should be compacted and repaired fairly often (once a
day if you have a lot of activity). Watch the size of the
BE datafile and compact when needed. Don't let it get
above 50% max file size. If it is close to the max file
size, it could crash. If it is close to max file size
after compacting, think about changing the BE database
engine to a different one like SQL Server (it is self
maintaining; it backs up using a maintence plan, it
shrinks the db size automatically and it can do this when
clients are using it!!!).

Last thing - the 'System.mdw' file. The client systems
will contain a copy of 'system.mdw' on each machine.
Unles you or the person who had your job before you
changed this, I think that you will find that the clients
are signed into the 'system.mdw' file on their own
systems. If not, and this is an older db, you may be
looking for a 'system.mda' file instead of a 'system.mdw'
file. .mda was the extension on the locking/security
files in the older versions of Access.

I hope this helps.
Pete Sheridan
(e-mail address removed)