Backup of back-end db

[Mark Andrews]

I'm about to add some code to allow the users of my application to backup
the back-end database.

I would like to make a simple button that the user can press.

I have no trouble getting the path to the back-end db or the path to the new
file I want to be created.

My question is, is the best method to use a simple
FileCopy strPath, strPathNEW
and just make sure no forms are open in the front-end?

or is there a better more elegant approach?

I know I saw code to compact the back-end (and use it for a backup)

Pros/cons appreciated,
Mark

 

[Douglas J. Steele]

My approach is to rename the back-end (usually by change the .mdb extension
to .bak) and then compact the renamed back-end to the "proper" name for the
back-end.

Before running the code, I make sure that the locking file (.ldb) doesn't
exist.

Function CompactDatabase(DatabaseName As String, Optional DebugFG As Boolean
= False) As Boolean
' Renames the existing backend database from .MDB to .BAK
' Compacts the backup copy to the "proper" database
'
' Returns True if successful, False otherwise

On Error GoTo Err_CompactDatabase

Dim booStatus As Boolean
Dim strBackupFile As String

booStatus = True

' Make sure that DatabaseName exists
If Len(Dir$(DatabaseName)) > 0 Then

' Figure out what the backup file should be named
If StrComp(Right$(DatabaseName, 4), ".mdb", vbTextCompare) = 0 Then
strBackupFile = Left$(DatabaseName, Len(DatabaseName) - 4) &
".bak"

' Determine whether the backup file already exists,
' and delete it if it does.
If Len(Dir$(strBackupFile)) > 0 Then
Kill strBackupFile
End If

Name DatabaseName As strBackupFile

' Do the actual compact
DBEngine.CompactDatabase strBackupFile, DatabaseName
End If

End If

End_CompactDatabase:
CompactDatabase = booStatus
Exit Function

Err_CompactDatabase:
booStatus = False
MsgBox Err.Number & ": " & Err.Description
Resume End_CompactDatabase

End Function

 

[Mark Andrews]

Douglas,

Thanks for the code. Although, I think I'm going to stick with Filecopy
instead of compacting the back-end.
Pondering whether I should check for the back-end ldb file or just let it
fail and raise an error message when it can't copy the file. I close all
forms so if only one user was using the back-end the file should not be
open.
However if multiple people are sharing the back-end basically they need to
get out for this type of backup feature to work. Hopefully if using on a
network they have other network backup routines in place.

Mark

 

[Douglas J. Steele]

Since standard network backups seldom check whether the database is in use,
it's possible (although hopefully unlikely) that the backup could be in an
inconsistent state. Hopefully you check the backups occasionally to ensure
that they're valid.

 

[Mark Andrews]

Guys,

Thanks for the comments. I'm adding this to software I hope to sell (so who
knows where it will be installed and how they will handle backups). So I
wanted to give them an option within the application.
I will recommend that backups be done (either outside of my application or
by using what I provide).

Ken, FYI: your compact the database code shows up a good deal when I
searched the web for solutions.

Thanks,
Mark

Mark:

I've always allowed error handling to cope with the file being open either
by
another user or the current user, but I've done it in two stages; firstly
by
trying to open the file exclusively and only proceeding if that raises no
error; then by handling the error which is raised by copying the file
(like
Doug, I compact), which will occur if the current user is accessing one of
its tables in some way. So, essentially I'm approaching it in the same
way
as Doug, but using error handling rather than checking for the existence
of
the .ldb file.

Regarding your point about other network backup routines being in place,
that
should be the case in any competently administered system, but
nevertheless
it can be useful to give users a means of backing up on demand rather than
whenever a backup is next scheduled. If a particularly important and
time-
consuming batch of data has been input or before some large update
undertaken,
it can be reassuring to users to know that a copy is tucked away safe and
sound.

Ken Sheridan
Stafford, England

Douglas,

Thanks for the code. Although, I think I'm going to stick with Filecopy
instead of compacting the back-end.
Pondering whether I should check for the back-end ldb file or just let it
fail and raise an error message when it can't copy the file. I close all
forms so if only one user was using the back-end the file should not be
open.
However if multiple people are sharing the back-end basically they need to
get out for this type of backup feature to work. Hopefully if using on a
network they have other network backup routines in place.

Mark

My approach is to rename the back-end (usually by change the .mdb
extension to .bak) and then compact the renamed back-end to the "proper"

[quoted text clipped - 68 lines]

Pros/cons appreciated,
Mark