Backing up open database

[Tony]

Hi All,

Two part question: can an open database be backed up (by the user who has it
open) and if so, are there any issues associated with doing so?

I have an Access 2000 database through which we crunch payroll data from a
Progress database for the purpose of printing information not stored in the
Progress database on check stubs. The Access database is not set up such
that the users open it exclusively however, I have code in place that
prevents a second user from opening the database if someone's already in.
As part of the payroll processing, I have the payroll clerks notify me when
they are ready to use the database; before they get in, I create a backup
then give them the green light.

I'd like this to work such that the clerk opens the database, clicks a
button to create the backup, then proceeds with their normal duties. But, I
don't want to run into issues, if they exist, with backing up a database
that is open. I know that I can write a script for them to use to back up
the database before they access it, but I think it would be cleaner if the
function lived in the database.

Any ideas, tips, suggestions are appreciated.

Thanks & Ciao

 

[Tom van Stiphout]

On Thu, 20 Dec 2007 16:13:42 -0600, "Tony"

I'm assuming you have split the database in frontend and backend,
following best practices. You now want to backup the backend while it
is in use.
This is technically possible, and will produce a good copy 99% of the
time. You have to decide if that's good enough for you.

Personally I would give them another shortcut on their desktop
pointing to a small vbscript file to make a backup. This script would
also check if the db was in use (by looking for an LDB). They would
run this before starting the Access application.

-Tom.

 

[Ken Sheridan]

If the database is split into front and back ends, which is advisable even in
a single user environment, the back end containing the data can be backed up
provided that the current or any other user has no tables from it currently
open. There is no point backing up the current front end as a 'master' copy
of this would be held safely by the database administrator (presumably you).

To back up the back end from the front end add a procedure such as this to a
standard module in the database. Make sure you save the module with a
different name to the procedure e.g. mdlBackUp.

Public Sub BackUp(strBackEnd As String, strBackUp As String)

Const FILEINUSE = 3356
Dim strMessage As String

' if back up file exists get user confirmation
' to delete it
If Dir(strBackUp) <> "" Then
strMessage = "Delete existing file " & strBackUp & "?"
If MsgBox(strMessage, vbQuestion + vbYesNo, "Confirm") = vbNo Then
strMessage = "Back up aborted."
MsgBox strMessage, vbInformation, "Back up"
Exit Sub
Else
Kill strBackUp
End If
End If

On Error Resume Next
' attempt to open backend exclusively
OpenDatabase Name:=strBackEnd, Options:=True

Select Case Err.Number
Case 0
' no error so proceed
On Error Goto 0
Application.CompactRepair strBackEnd, strBackUp
' ensure back up file created
If Dir(strBackUp) = Mid(strBackUp, InStrRev(strBackUp, "\") + 1) Then
strMessage = "Back up successfully carried out."
Else
strMessage = "Back up failed."
End If
MsgBox strMessage, vbInformation, "Back up"
Case FILEINUSE
' file in use - inform user
strMessage = "The file " & strBackEnd & _
" is currently unavailable. " & _
" It may be in use by another user" & _
" or you may have a table in it open."
MsgBox strMessage
Case Else
' unknown error - inform user
MsgBox Err.Description, vbExclamation, "Error"
End Select

End Sub

Call the procedure from somewhere in the database passing the path to the
back end file and that to the back up file you want to create, e.g. (as a
single line of code)

BackUp "F:\SomeFolder\SomeSubFolder\SomeDatabase.mdb",
"F:\SomeFolder\SomeSubFolder\SomeDatabase_bk.mdb"

If you want to overwrite the current back up every time you can hard code
the paths, or better still store them in a table and get them from there
using the DLookup function. If you want the user to be able to name the back
up file each time you can open a common. You'll find various examples
online, but I usually use Bill Wilson's class module from:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=22415&webtag=ws-msdevapps


Ken Sheridan
Stafford, England

 

[Tony]

Tom,

Thanks for the input. I'm still undecided as to how to do this since 99% of
the time may or may not work; I don't get to make the call on that one. I
appreciate the feedback.

Tony