Backup And Zip Database

Hello Friends,
In one of my applications i have to Backup my master database located at some drive <This location keeps on changing> than zip the backup file using winzip, than delete the backup mdb file.

I have developed code for backup and it works fine. But generates error while zipping the same. I hav reg version of winzip n thats not a problem.
There should be some problem in the codding.

Hav any one done this before, or know how is it possible.
I am attaching a Code as well as sample database.

I am trying this for a very long time, but it just keep failing.

Thanks in advance.

Code :

' ***************** CODE FOR Backup (Working)' *****************

Dim strNewDBName As String
Dim strOldDbName As String
Dim strOldDBPrefix As String
Dim fYear As String
Dim fMonth As String
Dim fDay As String
Dim iloc As Integer
Dim x As Integer
strOldDbName = Me![newpath]

For x = Len(strOldDbName) To 1 Step -1
iloc = InStr(x, strOldDbName, "\")
If iloc <> 0 Then
Exit For
End If
Next x

strOldDBPrefix = Mid$(strOldDbName, iloc + 1, 2)

DoCmd.Hourglass True

If gBrowseFlag = 2 Then
' repair and compact database
x = RepairMDB(strOldDbName)
DoCmd.Hourglass False
DoCmd.Beep
MsgBox "The database has been repaired and compacted successfully.", 64
ElseIf gBrowseFlag = 1 Then
' copy database
strNewDBName = CurDir$ & "\BuExp.mdb"
FileCopy strOldDbName, strNewDBName

' repair backup database
x = RepairMDB(strNewDBName)

' rename backup database
fYear = DatePart("yyyy", Now)
fYear = Mid$(fYear, 3, 2)
fMonth = DatePart("m", Now)
If Len(fMonth) = 1 Then
fMonth = "0" & fMonth
End If
fDay = DatePart("d", Now)
If Len(fDay) = 1 Then
fDay = "0" & fDay
End If

' rename backup database
Name strNewDBName As CurDir$ & "\" & strOldDBPrefix & fYear & fMonth & fDay & ".mdb"

DoCmd.Hourglass False
DoCmd.Beep
MsgBox "The database has been backed up, repaired and compacted successfully.", 64
End If

DoCmd.Close A_FORM, "frmBrowse"
DoCmd.Close A_FORM, "frmBlank"
' ****************** END CODE HERE ' ******************

' ***************** CODE FOR MAKING COMPRESSED ZIP FILE (Not working)' *****************
'

Dim objScript
Dim objAccess
Dim strPathToMDB
Dim strMsg
Dim Comp0001
Dim strTempDB

If MsgBox("Do you wish to zip the Backup file also?", 1) = 1 Then
' ////////////////////////////////////////////////////////////////
'
' Path to the Access MDB which will be compacted
'
strPathToMDB = CurDir$ & "\" & strOldDBPrefix & fYear & fMonth & fDay & ".mdb"
'
' ////////////////////////////////////////////////////////////////
' Set a name and path for a temporary mdb file
strTempDB = CurDir$ & "\" & Comp0001 & fYear & fMonth & fDay & ".mdb"
' Create Access 97 Application Object
Set objAccess = CreateObject("Access.Application.8")
' For Access 2000, use Application.9
'Set objAccess = CreateObject("Access.Application.9")
' Perform the DB Compact into the temp mdb file
' (If there is a problem, then the original mdb is preserved)
objAccess.DBEngine.CompactDatabase strPathToMDB, strTempDB
If Err.Number > 0 Then
' There was an error. Inform the user and halt execution
strMsg = "The following error was encountered while compacting database:"
strMsg = strMsg & vbCrLf & vbCrLf & Err.Description
Else
' Create File System Object to handle file manipulations
Set objScript = CreateObject("Scripting.FileSystemObject")

' Back up the original file as Filename.mdbz. In case of undetermined
' error, it can be recovered by simply removing the terminating "z".
objScript.CopyFile strPathToMDB, strPathToMDB & "z", True
' Copy the compacted mdb by into the original file name
objScript.CopyFile strTempDB, strPathToMDB, True
' We are finished with TempDB. Kill it.
objScript.DeleteFile strTempDB
End If
' Always remember to clean up after yourself
Set objAccess = Nothing
Set objScript = Nothing
End If
'
' ****************** END CODE HERE ' ******************

View attachment Script.txt

View attachment Backup & Zip.zip

Have you looked at ghudson's post on this very subject?

Hello

RuralGuy said:

Have you looked at ghudson's post on this very subject?

Click to expand...

Ya i have tried this also but, it also generates error.

What version of Access are you using and what error are you getting?

Hi

RuralGuy said:

What version of Access are you using and what error are you getting?

Click to expand...

I am using Office 2003.
The error that i am getting is

1. 426
2. ActiveX component can't create object.

Thanks for the reply.

Simple Software Solutions

Here is another option to try.

Thanks!

DCrake said:

Here is another option to try.

Click to expand...

Hi Friend, Thank you so much. it worked supurbly.
I hav edited the code, since the backup file that i want to zip is not located at a specific location, and keeps on changing.

So insted of Temp, i am asking to brouse and select the file.

Thanks again for your time.

Hello DCrake
I Downloaded the file, but is not working: it Says "Project Contains missing or broken reference to the file xzip.dll version 1.0

Please Help!

vdanelia said:

Hello DCrake
I Downloaded the file, but is not working: it Says "Project Contains missing or broken reference to the file xzip.dll version 1.0

Please Help!

Click to expand...

To bring you up to speed: http://www.access-programmers.co.uk/forums/showthread.php?t=209174

Oh.... I am shocked and saddened to hear the news, David was certainly a valuable member of this forum, and his contributions will be missed.
He Helped me so many times
My prayers go to his family and friends.

Rest In Peace David Crake, We Will never Forget You!