Making Backups

[Duane]

I am using code from the book Building Microsoft Access Applications to
create a backups. The code looks for the path of the database. It then
checks to see if a backup is needed, and looks to see if the backup folder
is present. If there isn't a folder, one is created, along with a copy of
the database.

Here is part of the code:

Set Db = DBEngine(0)(0)
' Open ztblVersion to see if we need to do a backup
Set rst = Db.OpenRecordset("ztblVersion", dbOpenDynaset)
rst.MoveFirst
datBackup = rst!LastBackUp
rst.Close
Set rst = Nothing
' If last backup was yesterday
If datBackup < Date Then

' Get the name of the data file
strData = Mid(Db.TableDefs("ztblVersion").Connect, 11)

' Get the name of its folder
strDir = Left(strData, InStrRev(strData, "\"))

' See if the "BackupData" folder exists
If Len(Dir(strDir & "BackupData", vbDirectory)) = 0 Then

' Nope, build it!
MkDir strDir & "BackupData"
End If

In this scenario my database is in the P:\AccessTools directory. All the
backups are created in the P:\AccessTools\BackupData directory. The problem
with this scenario is all the backups are in the same root directory and if
someone inadvertently deletes the P:\AccessTools folder, all the backups are
deleted too.

This is not a major disaster however, it may take out support team a few
days to restore the data that has been lost, when if the backups were stored
in another location, I could restore the backend database in a couple of
minutes and redistribute the front end database. Everyone could back up and
running in 15 minutes.

I know a better solution would be to have the backend database in another
location where the chances of something happening to it is slim to none.
The problem I experiencing with that solution is that I am not sure how to
connect the two databases in that matter. The drive in which the database
is located on now is called a public drive. If I move the backend database
to a differnent drive, the users who do not have access to that drive get an
error that says the backend cannot be found.

Is there a way to create my backup in another directory or on another drive,
or better yet is it possible to connect the two databases even if the user
doesn't have access to the drive the backend resides on.?

Thanks in advance,
Newbie

 

[Ed Metcalfe]

I know a better solution would be to have the backend database in another
location where the chances of something happening to it is slim to none.
The problem I experiencing with that solution is that I am not sure how to
connect the two databases in that matter. The drive in which the database
is located on now is called a public drive. If I move the backend
database to a differnent drive, the users who do not have access to that
drive get an error that says the backend cannot be found.

Is there a way to create my backup in another directory or on another
drive, or better yet is it possible to connect the two databases even if
the user doesn't have access to the drive the backend resides on.?

Thanks in advance,
Newbie

<snip>

You've hit upon the fundamental security issue in MS Access. As a
file-server database users must have full permissions to the frontend and
backend MDB files (and therefore the directory in which they reside). There
is nothing you can do about this.

In terms of the specific issue you have I can think of a couple of potential
solutions:

1. Remove the user's delete permissions on the directory in which you are
creating the backups.
2. Have the backup process running from a separate PC and account, which has
access to a directory that the users have no rights to.
3. If you're using Windows Server 2003 I've found the Volume Shadow Copy
service helpful for restoring files when you don't have the time for a
restoration from tape backup.

Ed Metcalfe.