Back up and zip code from ghudson (1 Viewer)

jfgambit

Kinetic Card Dealer
Local time
Today, 05:52
Joined
Jul 18, 2002
Messages
798
What does the current code look like...can you post it?
 

David b

Registered User.
Local time
Today, 05:52
Joined
Mar 2, 2003
Messages
102
Click the link and go to the 2nd message in the thread.
Db
 

jfgambit

Kinetic Card Dealer
Local time
Today, 05:52
Joined
Jul 18, 2002
Messages
798
Sorry David...I wasn't clear...

I meant...what did you convert the code too?

You stated you were havng problems...what changes did you make? What error did you get?

Changing to A: Should be as follows:


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:\Database\"
sSourceFile = "MyDB.mdb"
sBackupPath = "A:\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
 

David b

Registered User.
Local time
Today, 05:52
Joined
Mar 2, 2003
Messages
102
No success so far. Fails on this line -
fso.CopyFile sSourcePath & sSourceFile, sBackupPath & sBackupFile, True.

When the backup path is to C:\ blah, blah, the winzip I agree form appears. Going to A:\ the floppy runs straight away with no winzip. After about 30 seconds an error - method copyfile of object.appears.
david b
 

ghudson

Registered User.
Local time
Today, 01:52
Joined
Jun 8, 2002
Messages
6,195
Does the directory on the A:\ drive exists that you are trying to copy the file to?
If not, you should get a runtime error # 76. I just tested my code and it works file
when I set the sBackupPath = A:\

This is the modified version that will copy the backup to the A:\ drive...
Code:
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:\Database\"
sSourceFile = "MyDB.mdb"
sBackupPath = "A:\"
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
HTH
 

David b

Registered User.
Local time
Today, 05:52
Joined
Mar 2, 2003
Messages
102
Thanks for the reply. Still getting the same problem
Runtime error 2147024784 (80070070)
Using W95 & A97

David b
 

David b

Registered User.
Local time
Today, 05:52
Joined
Mar 2, 2003
Messages
102
Could size be the problem ?
The b_e end am trying to save is 1.8mb. - 350zipped. Just tried with a 400 file and it went no problem

David b
 

ghudson

Registered User.
Local time
Today, 01:52
Joined
Jun 8, 2002
Messages
6,195
Are you using my code above "as-is"? Open up a brand new db and copy the above code into the new db, make the changes to these four strings and try again. I do not think it is the code for it works for me.

sSourcePath = "C:\Database\"
sSourceFile = "MyDB.mdb"
sBackupPath = "A:\"
sBackupFile = "BackupDB_" & Format(Date, "mmddyyyy") & "_" & Format(Time, "hhmmss") & ".mdb"

Also, I have these four references set in my Access 97 db...

Visual Basic for Applications
Microsoft Access 8.0 Object Library
Microsoft DAO 3.51 Runtime Library
Microsoft Scripting Runtime (This one is a must!)

HTH
 

ghudson

Registered User.
Local time
Today, 01:52
Joined
Jun 8, 2002
Messages
6,195
Yes, size does matter! :D

A floppy can only hold 1.4 megabytes total and my test file if very small.
 

David b

Registered User.
Local time
Today, 05:52
Joined
Mar 2, 2003
Messages
102
But the zipped file should fit easily.
Could it be "seeing" the big unzipped file first and that causes the error.
Maybe the code needs a delay between the zipping and copying bits.
Just a suggestion
David b
 

EMP

Registered User.
Local time
Today, 05:52
Joined
May 10, 2003
Messages
574
The code copies the database to the destination (A:\), zips it there, then deletes the database.

Maybe you can do the zipping in C: drive (as the original code did) and add a line of code to copy only the zipped file to A: drive.
 

David b

Registered User.
Local time
Today, 05:52
Joined
Mar 2, 2003
Messages
102
Having slept on the problem. I think we need to do the zip bit on C:\ then copy the zipped file to A:\.
Todays challenge !
David b
 

David b

Registered User.
Local time
Today, 05:52
Joined
Mar 2, 2003
Messages
102
Problem was getting a db of more than floppy size zipped and onto a floppy .
This works but I guess it could be made neater.
Any suggestions
David b





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:\Data\adata\Stock record\Hoofprint\"
sSourceFile = "Stock _be.mdb"
sBackupPath = "C:\hoofprint\backups\"
sBackupFile = "BackupDB_.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)
Dim sSourcePath2 As String
Dim sSourceFile2 As String
Dim sBackupPath2 As String
Dim sBackupFile2 As String

sSourcePath2 = "C:\hoofprint\backups\"
sSourceFile2 = "BackupDB_.zip"
sBackupPath2 = "A:\"
sBackupFile2 = "BackupDB_" & Format(Date, "mmddyyyy") & "_" & Format(Time, "hhmmss") & ".zip"

Set fso = New FileSystemObject
fso.CopyFile sSourcePath2 & sSourceFile2, sBackupPath2 & sBackupFile2, True
Set fso = Nothing
Beep
MsgBox "Backup was successful and saved @ " & Chr(13) & Chr(13) & sBackupPath2 & 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
 

EMP

Registered User.
Local time
Today, 05:52
Joined
May 10, 2003
Messages
574
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 sSourceMDB As String
Dim sFileName As String
Dim sBackupMDB As String
Dim sCDrive_ZIP As String
Dim sADrive_ZIP As String

sSourceMDB = "C:\Data\adata\Stock record\Hoofprint\Stock _be.mdb"
sFileName = "BackupDB_" & Format(Date, "yyyymmdd") & "_" & Format(Time, "hhmmss")

sBackupMDB = "C:\hoofprint\backups\" & sFileName & ".mdb"
sCDrive_ZIP = "C:\hoofprint\backups\" & sFileName & ".zip"
sADrive_ZIP = "A:\" & sFileName & ".zip"

Set fso = New FileSystemObject
fso.CopyFile sSourceMDB, sBackupMDB, True
Set fso = Nothing

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

Call Shell(sWinZip & " -a " & sCDrive_ZIP & " " & sBackupMDB, vbHide)

FileCopy sCDrive_ZIP, sADrive_ZIP

Beep
If Dir(sADrive_ZIP) <> "" Then
MsgBox "File backed up to " & Chr(13) & sADrive_ZIP, vbInformation, "Backup Completed"
Kill sBackupMDB
Kill sCDrive_ZIP
Else
MsgBox "Backup failed"
End If

End Function

Note I have changed the date format to "yyyymmdd" as it can be sorted in ascending or descending order.
 

David b

Registered User.
Local time
Today, 05:52
Joined
Mar 2, 2003
Messages
102
So that is sending the backup file to a zip drive rather than a floppy in A:\. Is that correct ?
David b
 

EMP

Registered User.
Local time
Today, 05:52
Joined
May 10, 2003
Messages
574
It sends a "zip" file to A:\ which normally is a floppy disk drive.

.zip is the default file extension used by WinZip. The file would be something like:
A:\BackupDB_20031120_111744.zip

which can be unzipped with WinZip or any similar unzip tool.
 

ghudson

Registered User.
Local time
Today, 01:52
Joined
Jun 8, 2002
Messages
6,195
This works but I guess it could be made neater.
You get what you pay for. ;)

I have modified the last part of my code so that the zipping is done in the C:\Temp\
directory and then the zipped file is moved to the A:\ drive. I also added "some" error
trapping and you should do more testing and add any more error numbers that you
discover. The error # -2147024784 "Method 'CopyFile' of object 'IFileSystem3'
failed" is because you tried to copy the db "as-is" [too big] to the A:\ drive before it
was zipped up.

I must mention that saving the db to a floppy is not the best place since your db
will grow in size the more it is used and as data is added. But, this will do what you want.
Code:
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 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:\Database\"
sSourceFile = "MyDB.mdb"

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)

Name sZipFile As "A:\" & sZipFileName

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"

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

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." & vbNewLine & vbNewLine & sSourceDB, 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 & sSourceDB, 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 & sSourceDB, vbCritical
        Exit Function
    Else
        MsgBox Err.Number & " - " & Err.Description
        Resume Exit_BackupAndZipitToDriveA
    End If

End Function
HTH
 

GGib7711

Registered User.
Local time
Today, 05:52
Joined
Oct 1, 2003
Messages
28
So that is sending the backup file to a zip drive rather than a floppy in A:\.
In view of the falling of prices, I wonder if one of these days the default A: drive of a computer will be a zip drive instead of a floppy drive.
 

David b

Registered User.
Local time
Today, 05:52
Joined
Mar 2, 2003
Messages
102
Ok tried the code. It says source file can not be found. I copied and pasted from the previous code, which seemed to find it OK -

sSourcePath = "C:\Data\adata\Stock record\Hoofprint\"
sSourceFile = "Stock _be.mdb"

Any reason for this ?

I accept your comments on floppys but this app is a single user system. Used by farmers to manage their livestock records the b_e grows slowly. It will be probably 5 years before the biggest operations` file is too big for a floppy when zipped.
David b
 

Users who are viewing this thread

Top Bottom