Eef:
What is it you are trying to do? Do you want a means of regularly backing
up the data from within your database? If so, I'd advise that you firstly
split the database into front and back ends if its not already split. the
built in database splitter wizard will do this for you, creating a new back
end file with a '_be' suffix and replacing the local tables in your current
file with links to the back end. This means you can back up the data in the
back end separately. It also means you can distribute copies of the front
end to other users in a multi-user environment and link them to a single back
end file on a server. You should keep a 'master copy' of the front end
somewhere safe.
To back up the data from within the front end you can use the following
module. Just paste the code into a new blank module and save it as something
like mdlBackUp (the name isn't important so long as it does not conflict with
the name of a function, procedure etc).
''''module starts''''
Option Compare Database
Option Explicit
Declare Function CopyFile& Lib "kernel32" Alias "CopyFileA" (ByVal _
lpExistingFilename As String, ByVal lbNewFileName As String, ByVal _
bFailIfExists As Long)
Public Sub MakeFileCopy(strExistingFile As String, _
strNewfile As String, _
blnDoNotOverWrite As Boolean, _
Optional blnShowMessage As Boolean = False)
Dim strMessage As String
strExistingFile = strExistingFile
strNewfile = strNewfile
If CopyFile(strExistingFile, strNewfile, blnDoNotOverWrite) = 1 Then
strMessage = "File successfully copied."
Else
strMessage = "File copy failed."
End If
If blnShowMessage Then
MsgBox strMessage, vbInformation, "Copy File"
End If
End Sub
Public Sub BackUp(strBackEnd As String, strBackUp As String)
Const FILEINUSE = 3356
Dim strMessage As String
Dim strBackUpTemp 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
' make temporary copy of backend file and then delete current
back up
strBackUpTemp = Left(strBackUp, InStr(strBackUp, ".")) & "bak"
MakeFileCopy strBackUp, strBackUpTemp, False
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
Application.CompactRepair strBackEnd, strBackUp
If Err.Number = FILEINUSE Then
' file in use by current user
strMessage = "The file " & strBackEnd & _
" is currently unavailable. " & _
" You may have a table in it open."
MsgBox strMessage
' rename temporary copy of back up file
' if exists, back to original
If Dir(strBackUpTemp) <> "" Then
MakeFileCopy strBackUpTemp, strBackUp, False
Kill strBackUpTemp
End If
Exit Sub
Else
On Error GoTo 0
' ensure back up file created
If Dir(strBackUp) = Mid(strBackUp, InStrRev(strBackUp, "\") + 1)
Then
strMessage = "Back up successfully carried out."
' delete temporary copy of back up file if exists
On Error Resume Next
Kill strBackUpTemp
On Error GoTo 0
Else
strMessage = "Back up failed."
' rename temporary copy of back up file
' if exists, back to original
If Dir(strBackUpTemp) <> "" Then
MakeFileCopy strBackUpTemp, strBackUp, False
Kill strBackUpTemp
End If
End If
MsgBox strMessage, vbInformation, "Back up"
End If
Case FILEINUSE
' file in use - inform user
strMessage = "The file " & strBackEnd & _
" is currently unavailable. " & _
" It may be in use by another user."
MsgBox strMessage
' rename temporary copy of back up file,
' if exists, back to original
If Dir(strBackUpTemp) <> "" Then
MakeFileCopy strBackUpTemp, strBackUp, False
Kill strBackUpTemp
End If
Case Else
' unknown error - inform user
MsgBox Err.Description, vbExclamation, "Error"
' rename temporary copy of back up file
' if exists, back to original
If Dir(strBackUpTemp) <> "" Then
MakeFileCopy strBackUpTemp, strBackUp, False
Kill strBackUpTemp
End If
End Select
End Sub
''''module ends''''
To back up the back end file you call the BackUp procedure like so (as one
line of code, it will probably be split over two lines in your newsreader)
BackUp "F:\MyFiles\MyDatabases\MyDatabase.mdb",
"F:\MyFiles\MyDatabases\MyBackUps\MyDatabase.mdb"
This will make a copy of your database in the MyBackUps subfolder. The
subfolder must already exist. Alternatively you could back up to the same
folder as that in which the back end file is located, renaming the file to
MyDatabase_bak.mdb for instance.
Ken Sheridan
Stafford, England
