How can I use a command button to back my database in Access 2007 ?

[mthornblad]

When my database opens, I display a form that acts as a menu for the
user to navigate. I want to add a button to this menu that will start
a backup of the current database. I want to backup the database to a
CD (drive d

How would I do this?

Thanks in advance
Mark

 

[AranKappler]

Hi,

I'm not sure if this will help, but assuming you have split your database
then you can use the following code. Obviously you will need to add error
handling etc.:

If Len(Dir$("path to backend database file.mdb")) > 0 Then
GoTo DBBU
Else
MsgBox "The Back-End database 'path to backend database file.mdb' cannot
be found. Check drive mapping and try again.", vbOKOnly
DoCmd.Hourglass False
Exit Sub
End If


DBBU:
'set the variables for the code
databasename = "path to backend database file.mdb"
strDate = Format(Time, " hhmm")
strDate = strDate & Format(Date, " mmddyy")

'make sure that database is not being used
If Len(Dir$("path to backend database file.ldb")) > 0 Then
MsgBox "The back-End database is still being used. Make sure everyone is
logged out and try again.", vbOKOnly
DoCmd.Hourglass False
Exit Sub
Else
End If

'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) &
strDate & ".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

Note the reference to the ldb file in line 18 (ish).

I'm not a vba wizard so apologies for any shortcomings in the code.

Aran K