Back up and zip code from ghudson (1 Viewer)

[scouser]

Getting there???

Hi David, still trying!!
Get error when I try to run the code:

[Run-time error '53']
[File not found]
[fso.CopyFile sSourcePath & sSourceFile, sBackupPath & sBackupFile, True]

I set Microsoft Scripting as sugested.
Cheers,
Phil.

PS: I have zip (came installed with XP) but can't appear to find it on hard drive?? Searched C: for winzip.exe.

 

[David b]

If you can not find winzip exe that could be the prob.
It is often on mag cover discs or you can download it from winzip web site.
DB

 

[David b]

You also need to check very carefully that your paths are right, including the path where the back up is to be stored. If you have "C:\Database\Backups\" do these folders exist on your machine.
colons, quotations marks and slashes are all critical

hth
DB

 

[scouser]

Cheers

Thanks David. I will look into the winzip side of things (never seem to be able to find a free version!!). I have a re-writeable CD drive, could I simply back-up direct to a disc without zipping/ (Big code changes??).

Yes the Folders do exist on my C: Drive as referenced in the path.

Thanks for your efforts,
Phil.

 

[ghudson]

Curiously... Where is the Windows XP compression program located on your computer? I have Windows XP but do not know where the built-in compression program is on my PC.

The "evaluation" version of WinZip should work for I believe that somebody else has previously mentioned in another posting using my backup function that it worked for them. Go to www.WinZip.com to download the evaluation version. You can find a bunch of freeware zip compression programs at www.NoNags.com that truly are full functioning yet "free" programs to use. I do not use any of those compression freeware programs since WinZip serves my file compression purposes and yet WinZip is so cheap to buy.

No, you can not use my backup function to burn to a CD. (I would love to be proved wrong ;-)

 

[scouser]

Will Give It a Go

I will give it a go and let you know my next error! Ha! Ha!
Cheers,
Phil.

 

[scouser]

Error: Can not find source file

Code

Option Compare Database
Option Explicit

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Public Function BackupAndZipitToDriveA()
On Error GoTo Err_BackupAndZipitToDriveA

'This function will allow you to copy a db that is open,
'rename the copied db and zip it up to another 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

' This is the path and my file is called pv????
sSourcePath = "C:\Database"
sSourceFile = "pv.mdb"

'sBackupPath = "A:\"
If Dir("C:\Temp", vbDirectory) = "" Then MkDir ("C:\Temp")
sBackupPath = "C:\Temp\"
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)

Sleep 10000 'ten second pause, 5000 = 5 seconds

Name sZipFile As "A:\" & sZipFileName

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

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

Exit_BackupAndZipitToDriveA:
Exit Function

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

End Function

Where am I going wrong?
At least it's a different error!!
Cheers,
Phil.

 

[David b]

try a slash after database

sSourcePath = "C:\Database\"

DB

 

[ghudson]

I do not recommend copying the file to a diskette [A:\ drive]. Once the db gets larger that 3 megabytes if will probably be to big to fit onto a diskette even after it has been zipped up.

The code I posted a page back is smaller because it is moving the zipped backup file to another location [your choice] on your computer.

HTH

 

[scouser]

Nearly there!

Guys the code runs error free, yippy!! However the file does not appear in the designated backup folder C:\Database\Backups. When I run the code I get a message saying backup was successful and saved C:\Database\Backups the backup file name is BackupDB_01442004_214431.zip??

Nothing there, empty folder? Code as follows, have I made an error?

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\Philip Horan\My Documents\Project\Project Database\"
sSourceFile = "pv.mdb"
sBackupPath = "C:\Database\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
Thanks for your patience!
Phil.

 

[ghudson]

Your code looks okay. Try stepping through the code and follow each step [line of code]. Click your mouse anywhere into the code and then Press the F8 key one F8 at a time, check the results as each line of code in run for each press of the F8 key, verify that the file is being copied to the correct location and that it is named correclty, verify that the zip file is created and then deleted. Try searching the hard drive for the file name the message box gives to see if the file is somewhere else. Actually search for ALL files for todays date and see if it is named or placed somewhere else.

HTH

 

[scouser]

Ok

OK, I will step through the code and see where it leades me!!
Thanks,
Phil.

 

[scouser]

Invisible File

Head scratcher this one? The file appears to back-up, get the file name:

BackupDB_01152004_172630.ZIP

Search C: drive after for file name created on todays date which returns NO results. The folder in which the backup should reside is EMPTY!
Ahh well I tried,
Cheers for all your advice and assistance.
Phil.

 

[David b]

I had probs with this and it was related to the code running faster than the actual routine was happening. related to the speed of your `puter and the size of the file you are copying, I think !

Try putting a message box directly below the Shell line.
Give it a few seconds before you OK the message box

Call Shell(sWinZip & " -a " & sZipFile & " " & sFileToZip, vbNormalFocus)
MsgBox "Backup is proceeding. This routine can take up to 5 seconds to complete"

let us know if it does any good.
DB

 

[scouser]

Tried It

David thanks for the advice. I inserted the code and the message displayed OK. I have an evaluation version of winzip so that window also pops up! I waited for a while then clicked OK on the first message box. I then waited a while longer before clicking OK on the back-up & yep you guessed it...............nothing!!
Time to call it a day I think as all appears to work apart from the phantom file!!
Cheers,
Phil.

 

[ghudson]

Try searching for ALL files for todays date. Maybe the file is being name something else other than what you are expecting. Not sure how or if that is the cause but it is worth searching for to see if you can find where the file is going.

I am using Access 97, can you post a sample of your db with just the part to copy and zip the file and I will look at it tomorrow.

 

[biskra]

perhaps a solution with this

 

[scouser]

Cheers

I backed up and all was OK, but no file when I searched.........I found Lord Lucan but no .zip!! Ha! Ha!
Mr H, I have attached a small sample DB as requested. Biskra, thanks for your efforts, much appreciated.
Thanks guys.

 

[WayneRyan]

Phil,

I was converting this to my filenames and noticed no "\"

sBackupPath = "C:\Databases\Backups"

I guess it thinks that your path is a little different.

Wayne

 

[David b]

Thought there was an option to "Convert to previous version in A2000
DB