Zipping database

[mlyles]

Hi everyone, let me first start by saying I am horrible with programming and could really use anyones help. I have a database and I want to send it to some clients. The database contains a control panel that has a lot of buttons that link to different forms. I would like to have a control button that I press then it automatically zips the file and then sends it. Ghudson wrote a code that I am trying to work with but me being so bad with programming, I am having trouble.
1. I'm not sure where to put the code so that I can call it.
2. I'm not sure what I need to change in order for the code to operate correctly.
I currently changed the code and when I tried to compile it and it says "User-defined type not defined" and it highlights 'fso as FileSystemObject'. The parts I changed are sourcepath to where the file I am zipping is located, sourcefile to the name of the file I am zipping, sbackuppath to where to store the backup, for sbackupfile I did not know what to do so I left it alone, sWinzip was the same location as mine, and zipfilename, zipfile, filetozip I left alone.


Public Function BackupAndZipit()

'This function will allow you to copy a db that is open,
'rename the copied db and zip it up to anther folder.

'You must set a reference to the 'Microsoft Scripting Runtime' for the CopyFile piece to work!

'Thanks to Ricky Hicks for the .CopyFile code

Dim fso As FileSystemObject

Dim sSourcePath As String
Dim sSourceFile As String
Dim sBackupPath As String
Dim sBackupFile As String

sSourcePath = "S:\CRNA\nawemugufs10vb\NAVSEA1$\CRNA\PED\PE30\QA42_QA43\QA43ML"
sSourceFile = "IPMSA-FY05.mdb"
sBackupPath = "S:\CRNA\nawemugufs10vb\NAVSEA1$\CRNA\PED\PE30\QA42_QA43\QA43ML\Backups"
sBackupFile = "BackupDB_" & Format(Date, "mmddyyyy") & "_" & Format(Time, "hhmmss") & ".mdb"

Set fso = New FileSystemObject
fso.CopyFile sSourcePath & sSourceFile, 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
sZipFileName = Left(sBackupFile, InStr(1, sBackupFile, ".", vbTextCompare) - 1) & ".zip"
sZipFile = sBackupPath & sZipFileName
sFileToZip = sBackupPath & sBackupFile

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

Beep
MsgBox "Backup was successful and saved @ " & Chr(13) & Chr(13) & sBackupPath & Chr(13) & Chr(13) & "The backup file name is " & Chr(13) & Chr(13) & sZipFileName, vbInformation, "Backup Completed"

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

End Function

Here is a link to the original code:
http://www.access-programmers.co.uk/forums/showthread.php?t=52238&highlight=backup
Any help would be greatly appreciated and if you could be as in depth as possible, because once again I am a little slow with programming.
Thank you

 

[sfreeman@co.mer]

Sounds like a reference problem.

Try checking the reference to DAO 3.6 object library.

 

[mlyles]

Thanks for your reply, I would love to check but like I said I am new to all this, what do I have to do to check and where do I go to check it. Also how do I know if it is correct or not.
Thanks for all replies in advace.

 

[FLabrecque]

First, go to the Microsoft Visual Basic Editor. You can do this by right clicking almost anywhere on the Database Window, and selecting "Visual Basic Editor". Next, once inside the editor, click on TOOLS, then REFERENCES.

 

[ghudson]

As I stated in the third line of my code...

"You must set a reference to the 'Microsoft Scripting Runtime' for the CopyFile piece to work"

From within the module where your function exists...

Click the menu option "Tools"
Select the option "References"
Scroll down until you find the ''Microsoft Scripting Runtime' option and select it
Click the OK button

 

[mlyles]

Ok hopefully getting closer, I tried this and when I click the run(continue) arrow it give me a pop up box that says "path not found" with options to end or debug, so I clicked debug and it highlighted
fso.CopyFile sSourcePath & sSourceFile, sBackupPath & sBackupFile, True

Again here is the code I have so far and thanks in advance:

Option Compare Database

Public Function BackupAndZipit()

'This function will allow you to copy a db that is open,
'rename the copied db and zip it up to anther folder.

'You must set a reference to the 'Microsoft Scripting Runtime' for the CopyFile piece to work!

'Thanks to Ricky Hicks for the .CopyFile code

Dim fso As FileSystemObject

Dim sSourcePath As String
Dim sSourceFile As String
Dim sBackupPath As String
Dim sBackupFile As String

sSourcePath = "S:\CRNA\nawemugufs10vb\NAVSEA1$\CRNA\PED\PE30\QA42_QA43\QA43ML"
sSourceFile = "IPMSA-FY05.mdb"
sBackupPath = "S:\CRNA\nawemugufs10vb\NAVSEA1$\CRNA\PED\PE30\QA42_QA43\QA43ML\Backups"
sBackupFile = "BackupDB_" & Format(Date, "mmddyyyy") & "_" & Format(Time, "hhmmss") & ".mdb"

Set fso = New FileSystemObject
fso.CopyFile sSourcePath & sSourceFile, 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
sZipFileName = Left(sBackupFile, InStr(1, sBackupFile, ".", vbTextCompare) - 1) & ".zip"
sZipFile = sBackupPath & sZipFileName
sFileToZip = sBackupPath & sBackupFile

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

Beep
MsgBox "Backup was successful and saved @ " & Chr(13) & Chr(13) & sBackupPath & Chr(13) & Chr(13) & "The backup file name is " & Chr(13) & Chr(13) & sZipFileName, vbInformation, "Backup Completed"

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

End Function

 

[mlyles]

I forgot to mention, that the path to sourcepath is a shortcut, could this be a problem? The parts I changed are sourcepath to where the file I am zipping is located, sourcefile to the name of the file I am zipping, sbackuppath to where to store the backup, for sbackupfile I did not know what to do so I left it alone, sWinzip was the same location as mine, and zipfilename, zipfile, filetozip I left alone.

 

[mlyles]

OK I keep trying and trying and looking at old posts I have found some errors that I have made. Now I moved my database to the C drive and changed my code, now however I get "Run-time error '53' " and File not found. I saw an old post of someone who had the same error ( http://www.access-programmers.co.uk/forums/showthread.php?t=57863&page=4&pp=15 ) but for some reason I can not fix mine. Here is my code with the name of the file IPMSA-FY05. Please any help would be greatly appreaciated.

Option Compare Database

Public Function BackupAndZipit()

'This function will allow you to copy a db that is open,
'rename the copied db and zip it up to anther folder.

'You must set a reference to the 'Microsoft Scripting Runtime' for the CopyFile piece to work!

'Thanks to Ricky Hicks for the .CopyFile code

Dim fso As FileSystemObject

Dim sSourcePath As String
Dim sSourceFile As String
Dim sBackupPath As String
Dim sBackupFile As String

sSourcePath = "C:\Documents and Settings\michael.lyles1\"
sSourceFile = "IPMSA-FY05.mdb"
sBackupPath = "C:\Documents and Settings\michael.lyles1\Backups\"
sBackupFile = "BackupDB_" & Format(Date, "mmddyyyy") & "_" & Format(Time, "hhmmss") & ".mdb"

Set fso = New FileSystemObject
fso.CopyFile sSourcePath & sSourceFile, 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
sZipFileName = Left(sBackupFile, InStr(1, sBackupFile, ".", vbTextCompare) - 1) & ".zip"
sZipFile = sBackupPath & sZipFileName
sFileToZip = sBackupPath & sBackupFile

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

Beep
MsgBox "Backup was successful and saved @ " & Chr(13) & Chr(13) & sBackupPath & Chr(13) & Chr(13) & "The backup file name is " & Chr(13) & Chr(13) & sZipFileName, vbInformation, "Backup Completed"

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

End Function

 

[mlyles]

OK, I just came to work this morning and ran the code again and for some reason it seems to have gotten better (at least I hope). Now I get a Winzip window poping up saying
"Error: No files were found for this action that match your criteria-nothing to do. (C:\Documents.zip)."
Heres what is says when I click help
"Nothing To Do: no files were found for the specified action. Examples of when this message is issued include attempts to add a non-existent folder to an archive and attempts to freshen an archive that is already up to date."
I checked the paths and they are correctly setup.
Behind this box it also has the Backup Completed box.
Can someone please help me with this, the code is the same as above.
Thanks in advance.