Code for Backing Up a database?

[Tim McGavin]

Is there code for backup my databases? I know that I can simply make a copy
of the MDB file, but I would like to program my database to back itself up
every time I open it. Can this be done? How?

 

[Brendan Reynolds]

Is there code for backup my databases? I know that I can simply make a
copy of the MDB file, but I would like to program my database to back
itself up every time I open it. Can this be done? How?

Here's some code I use in one of my apps. A couple of things to watch out
for ...

1) The code includes calls to other procedures and global constants, and a
hard-coded reference to one of the tables in my database, so it won't work
in your app 'as is', it will need some modification.

2) The code leaves it to the user to ensure that the data file is not in use
before the backup is performed. There's room for improvement there, it's one
of those things that's been on my to-do list for ever, but never made it to
the top.

3) The code assumes that the 'back-end' data MDB is split from the
'front-end' application MDB.

Private Sub cmdBackup_Click()

Dim strInput As String
Dim strOutput As String
Dim strFilter As String
Dim db As DAO.Database
Dim strPrompt As String

On Error GoTo ErrorHandler
strPrompt = "Before completing your backup, please ensure that no other
" & _
"forms (other than this 'Other Tasks' menu form) or reports are
open, " & _
"and ask any other users who may be using the same data file " &
_
"on your network to do the same."
If MsgBox(strPrompt, vbOKCancel Or vbInformation, gstrcAppTitle) = vbOK
Then
DoCmd.Hourglass True
strFilter = "Microsoft Access Databases (*.MDB)" & vbNullChar &
"*.MDB" & _
vbNullChar & vbNullChar
Set db = CurrentDb
strInput = db.TableDefs("tblStudents").Connect
strInput = Mid$(strInput, InStr(1, strInput, "=") + 1)
strOutput = db.name
strOutput = Left$(strOutput, Len(strOutput) - Len(Dir(strOutput)))
strOutput = strOutput & Format$(Date, "yyyymmdd") & ".MDB"
strPrompt = "Where do you want to save your backup, and what do you
want " & _
"to call it?"
strOutput = GetSaveFileName(Me.Hwnd, strOutput, strFilter,
strPrompt, 1)
If strOutput = strInput Then
strPrompt = "You have selected the same name and location as the
" & _
"existing data file. Please choose a different name, or a "
& _
"different location."
DoCmd.Hourglass False
MsgBox strPrompt, glngcOkInfo, gstrcAppTitle
ElseIf strOutput = CurrentDb.name Then
strPrompt = "You have selected the same name and location as the
" & _
"existing application file. Please choose a different name,
or a " & _
"different location."
DoCmd.Hourglass False
MsgBox strPrompt, glngcOkInfo, gstrcAppTitle
Else
Me!lblStatus.Caption = "Copying data ... please wait."
DoEvents
FileCopy strInput, strOutput
Me!lblStatus.Caption = "Finished copying data."
DoEvents
End If
Set db = Nothing
DoCmd.Hourglass False
End If

ExitProcedure:
On Error GoTo 0
DoCmd.Hourglass False
Exit Sub

ErrorHandler:
'-2147201923 = user cancelled.
If Err <> -2147201923 Then
ShowAndLogError Me.name, "cmdBackup_Click"
End If
Resume ExitProcedure

End Sub

 

[Tim McGavin]

Private Sub cmdBackup_Click()

Dim strInput As String
Dim strOutput As String
Dim strFilter As String
Dim db As DAO.Database
Dim strPrompt As String

Thanks for your code. I will seriously consider using it.

One question...Instead of copying the current database, is there a way to
just cause the File/Backup Database function to start? I think that would
be my first choice.

 

[Guest]

Tim, Think about how many times a day you or your user will enter the
database? And are they entering it to do a read only activity or update
activity?
If there is a way to use the file/backup Think about the naming convention
and how many copies you may end up with? Also think about if you can through
in a compact of the database before backing it up? Could you have one way in
for read only things and for update things have them come in Exclusive mode
so no one else is using it when you do the backup?
Just some more things I think about. Hope to see what answers you get for my
own possible use too.
Good Luck

 

[Tim McGavin]

Tim, Think about how many times a day you or your user will enter the

database? And are they entering it to do a read only activity or update
activity?
If there is a way to use the file/backup Think about the naming convention
and how many copies you may end up with? Also think about if you can
through
in a compact of the database before backing it up? Could you have one way
in
for read only things and for update things have them come in Exclusive
mode
so no one else is using it when you do the backup?
Just some more things I think about. Hope to see what answers you get for
my
own possible use too.
Good Luck

Thanks for the comments. In my particular case there is only a single user
so his activity will automatically be limited since I wanted the backup to
start
when the database is first pulled up.

Yes you are right I suppose I could end up with a ton of copies of the same
database, but I have plenty of space and would periodically clear out
the older copies.

I'm not sure what "Exclusive Mode" is but if you feel it is important
enough to elaborate on then I'm listening.

I would just be happy if I could programmatically launch the
File/Backup feature.

 

[Guest]

Tim,

Look in Access menu under tools then options then advanced and see the
default open mode Exclusive.

I wish I knew how to code the compact and the backup on a button too. I am
still trying to search through help when I have time.

 

[krissco]

I wish I knew how to code the compact and the backup on a button too. I am

still trying to search through help when I have time.

If you are interested in compacting code, you can try something that I
wrote/adapted. The company I work for has about a hundred special
purpose .MDB/.MDE files. They are listed in directories such as: \
\fileserver\db$\[something] where different users permissions are set
on each [something] directory. The code recurses through the directory
tree and compacts any MDB/MDE file.

Also, this won't work as is - I have a table where I audit the
performance of this code (size before, size after, time start, time
stop, etc.) and you will need to remove the calls to that function.

'Recurse through the path provided and compact all .MDE or .MDB files
'Must include final backslash in path
Public Function AutoCompactDatabase(Optional strPath As String = "\
\fileserver\db$\", Optional intDirectory As Integer = 1)
On Error GoTo AutoCompactDatabase_Err

Dim strDir As String
strDir = Dir(strPath & "*.mdb")
While strDir <> "" And intDirectory = 1
compactDB strPath & strDir
strDir = Dir
Wend
strDir = Dir(strPath & "*.mde")
While strDir <> "" And intDirectory = 1
compactDB strPath & strDir
strDir = Dir
Wend

strDir = Dir$(strPath, vbDirectory)
Dim intLocalDir As Integer
intLocalDir = 0
While strDir <> ""
'If a directory other than this and previous then recurse
If strDir <> "." And strDir <> ".." And (GetAttr(strPath & strDir)
And vbDirectory) = vbDirectory Then
intLocalDir = intLocalDir + 1
If intLocalDir = intDirectory Then
AutoCompactDatabase strPath & strDir & "\"
End If
End If
strDir = Dir$
Wend
Exit Function

AutoCompactDatabase_Err:
If Err.Number = 5 Then
intLocalDir = 0
intDirectory = intDirectory + 1
strDir = Dir(strPath, vbDirectory)
Resume Next
Else
MsgBox Error$ & Err.Number
End If
End Function

Private Function compactDB(aName As String) As Boolean
On Error GoTo compactDB_Err
Dim ws As Workspace
Dim db As Database
Dim nName As String
Dim dtStart As Date, dtEnd As Date
Dim lngSizeStart As Long, lngSizeEnd As Long

'Initialize audit data
dtStart = Now()
lngSizeStart = FileLen(aName)

'Compact and replace the database
nName = Left(aName, Len(aName) - 3) & "NEW"
DBEngine.CompactDatabase aName, nName
Kill aName
Name nName As aName

'Successful Compacting of Database
dtEnd = Now()
lngSizeEnd = FileLen(aName)
auditLog dtStart, dtEnd, lngSizeStart, lngSizeEnd, aName
compactDB = True
DoEvents
Exit Function

compactDB_Err:
Select Case Err.Number
Case 3356 'MDB or MDE in use
auditLog dtStart, dtEnd, lngSizeStart, lngSizeEnd, aName,
"Error 3356: Database in use"
compactDB = False
Debug.Print "Unable to compact " & aName & " in use."
Case 70 'Permission denied (probably kill command)
auditLog dtStart, dtEnd, lngSizeStart, lngSizeEnd, aName,
"Error 70: " & Err.Description
compactDB = False
Debug.Print "Unable to compact " & aName & " permission
denied."
Case Else
auditLog dtStart, dtEnd, lngSizeStart, lngSizeEnd, aName,
"Error " & Err.Number & ": " & Err.Description
compactDB = False
Debug.Print "Unhandled Error for DB " & aName & "."
End Select
DoEvents
End Function

 

[Guest]

Tim,
Check out the following for doing the backup:
DoCmd.RunCommand acXXXXXXXX
DoCMD.Run Command using one of the following options:
acCMDCompactdatabase
acCMDBackup
acCMDCopydatabasefile
Note: I am not including all of the parameteres/options/details. So review
on your own before trying it.

DoCmd.RunCommand acBackup

http://msdn2.microsoft.com/en-us/library/aa221579(office.11).aspx


I have not tried coding it yet, but it may be the direction you are looking
for.

 

[Brendan Reynolds]

Thanks for your code. I will seriously consider using it.

One question...Instead of copying the current database, is there a way to
just cause the File/Backup Database function to start? I think that would
be my first choice.

The code I posted doesn't copy the current MDB, which would be the
front-end, application MDB. It copies the back-end, data MDB. You do not
want to copy the front-end, application MDB from code within that front-end,
application MDB, because you should not copy a file while it is open. Doing
so may result in a corrupted copy. You certainly don't want to use the
File/Backup Database feature each time that the MDB is opened, because that
feature closes the MDB before backing it up, and then opens it again, so if
you called it every time the MDB was opened, you'd have an endless loop.

You could write a batch file that makes a copy of the MDB before opening it.