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. I posted this in the Access Security Newsgroup but I thought
I might get more responses here.

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

 

[Pete D.]

Your best solution would be to use a real backup program that can run as an
administrator on its own and let it place the backup in a secure directory.
What you are doing now could be changed so that users only have create
rights to the directory but not delete. This would prevent most
possibilities of lost data but you would have to change the name each time
your backup occurs as users wouldn't be able to delete the old backup file.
You could incorporate some form of Date() in your file name. Administrators
would need to clean out the directory of expired backups occasionally.

 

[Jim Bunton]

If you want to backup a database you can use
FileCopy source, destination

which will copy the whole database to whereever you want
when you copy it add a date to the filename
e.g. MyDatabase.mdb & Format(now(), "yymmdd hhnnnss"

To check when you last created the copy you use Dir to go down the list of
filenames in the backup directory and check the last howevermant characters
then decide whether or not you want to create another one.

Limitation - you can't do more than one a second without overwriting the
file
advantages - the backup files will list in date order using year first then
month . . . .
and - seemingly a problem! you can't run the backups witjout renaming them
because their file expension totally confuses the system!!
yes - this is an advantage touse a backup copy it rename it THEN run it -
you still have your backup!

With a bit of string manipulation you can add the backup date bsfore the
file extension .mdb then yopui can run a backups as is - if you want.

Other disadvantages - you abck up the whiole .mdb not just a table - if
that's a problem split the backend into 'the tables you want to abckup
regularly' and those that really stay pretty constant (err anyofthem!!??)

 

[Duane]

Thank you for your response.

I guess I should have expounded just a bit more on the backup code. I
didn't post all of the code in the procedure. The procedure creates up to
3 copies of the database in the BackUp folder, with the date() in the file
name. The code kills the oldest file before a new one is created.

If the rights were changed so users could not delete any files, would this
code still run or would it have to be modified to only create the backups
and force an administrator to clean out the directory periodically? I am
not sure they would be too keen on that idea....

 

[Pete D.]

I made a mistake also, use Now() and then you avoid one per day instead you
can do it per second. Jim was right on that one.