Need to backup the database before closing VBA

[sidkid]

Hi,

I want to know while closing my VBA project, how can I backup the database before closing it. I am getting error message with my project.
Can someone point me in correct direction?

Thanks in advance.

[Shutdown]

Back up a database

If the number of records in your database grows regularly, you can also consider archiving the old data. Archiving is the process by which you periodically move older records from a table in an active database to a table in an archive database. This article does not explain how to archive old data.

If you want to automate creating backups of database files, consider using a product that performs automated backups of a file system, such as file server backup software or a USB external backup device. This article does not discuss ways to automate the creation of Microsoft Office Access backups.

Back up a database

When you back up a database, Access saves and closes any objects that are open in Design view, compacts and repairs the database, and then saves a copy of the database file by using a name and location that you can specify. Access then reopens any objects that it closed.

Access will reopen objects as specified by the value of the objects' Default View property.

1. Open the database that you want to back up.
2. Click the Microsoft Office Button Button image, point to Manage, and then, under Manage this database, click Back Up Database.
3. In the Save As dialog box, in the File Name box, review the name for your database backup.

You can change the name if you want, but the default name captures both the name of the original database file and the date that you make the backup.

Tip When you restore data or objects from a backup, you want to know which database the backup came from and when the backup was made. Therefore, it is usually a good idea to use the default file name.
4. In the Save As dialog box, select a location in which to save your database backup, and then click Save.

[Saaarc]

How to backup Access file using VBA code and zip it

Here I found a code posted on a blog I hope this is what you are searching for.

Function ZipandBackUpDb()
On Error GoTo Err_BackUpDb

'this line is very important to handle files
'it's very necessary to add the "Microsoft scripting runtime" reference from the tools->references in the VBA window
Dim fso As FileSystemObject

Dim sSourcePath As String
Dim sSourceFile As String
Dim sBackupPath As String
Dim sBackupFile As String
Dim strFileName As String
Dim sBackupFolder As String
Dim sFinalPath As String

'this will show you the dialog box to select the file you want to backup
strFileName = FindBackUpFile
If Not strFileName = "None Selected" Then
sSourcePath = strFileName
Else
MsgBox " BackUp Action cancelled. Database not backed up. ", vbCritical, " BackUp Failure"
Exit Function
End If

'this will create a temporary directory on "C:\" and will call it Temp if there is no a temp directory
If Not Dir("C:\Temp", vbDirectory) <> "" Then MkDir "C:\Temp"
sBackupPath = "C:\Temp\"
sBackupFile = "BackUp.mdb"

'this will show you the dialog box to select where you want to save the zipped file
sBackupFolder = FindBackUpFolder
If Not sBackupFolder = "None Selected" Then
sFinalPath = sBackupFolder & "\"
Else
MsgBox " BackUp Action cancelled. Database not backed up. ", vbCritical, " BackUp Failure"
Exit Function
End If

'this will make the cursor hour glass shape
Screen.MousePointer = 11

Set fso = New FileSystemObject
fso.CopyFile sSourcePath, sBackupPath & sBackupFile, True
Set fso = Nothing

Dim sWinZip As String
Dim sZipFile As String
Dim sZipFileName As String
Dim sFileToZip As String


sWinZip = "C:\Program Files\WinZip\WinZip32.exe" 'Location of the WinZip program

'here you can change the name of the file.
sZipFileName = Left(sBackupFile, InStr(1, sBackupFile, ".", vbTextCompare) - 1) & Format(Date, "dd-mm-yyyy") & "-" & Format(Time, "hh-mmAMPM") & ".zip"
sZipFile = sBackupPath & sZipFileName
sFileToZip = sBackupPath & sBackupFile

Call Shell(sWinZip & " -a " & sZipFile & " " & sFileToZip, vbHide)

Pause (3)

Set fso = New FileSystemObject
fso.CopyFile sBackupPath & sZipFileName, sFinalPath & sZipFileName, True
Set fso = Nothing


Screen.MousePointer = 0

MsgBox "Backup was successful. " & "The backup file is named: " & Chr(13) & " " & sFinalPath & sZipFileName, vbInformation, "Backup Completed"

If Dir(sBackupPath & sBackupFile) <> "" Then Kill (sBackupPath & sBackupFile)
If Dir(sBackupPath & sZipFileName) <> "" Then Kill (sBackupPath & sZipFileName)

Exit_BackUpDb:
Exit Function

Err_BackUpDb:
If Err = 5 Then 'Invalid procedure call or argument
MsgBox "Disk is full! Can not move the zip file to the Drive. Please move the " & sZipFile & " file to a safe location.", vbCritical, " BackUp Failure"
If Dir(sBackupPath & sBackupFile) <> "" Then Kill (sBackupPath & sBackupFile)
If Dir(sBackupPath & sZipFileName) <> "" Then Kill (sBackupPath & sZipFileName)
Exit Function
ElseIf Err = 53 Then 'File not found
MsgBox "Source file can not be found!" & vbNewLine & sZipFileName, vbCritical, " BackUp Failure"
Exit Function
ElseIf Err = 71 Then 'Disk not ready
If Dir(sZipFile) <> "" Then Kill sZipFile
If Dir(sFileToZip) <> "" Then Kill sFileToZip
MsgBox "Please insert a diskette in Drive and try again!", vbCritical, " BackUp Failure"
Exit Function
ElseIf Err = -2147024784 Then 'Method 'CopyFile' of object 'IFileSystem3' failed
MsgBox "File is to large to be zipped to the Drive!" & vbNewLine & sZipFile, vbCritical, " BackUp Failure"
Exit Function
Else
MsgBox Err.Number & " - " & Err.Description, , " BackUp Failure"
Resume Exit_BackUpDb
End If

End Function

[Anti_Trend]

Try something like this with proper database path;

strDBtoBackUp = "Path to DB"
DBEngine.CompactDatabase strDBtoBackUp

Private Sub ButtonName_Click()
Dim strDBtoBackUp As String
strDBtoBackUp = "D:My Documents\DBName.mdb"
DBEngine.CompactDatabase strDBtoBackUp
End Sub

Please change the path & check it out.