Back up On exit

[Galan]

Back up On exit..H..E..L..P!

Hiya,



I have created a program on Access; & now i would like to make it do a back up of the Mdb. So I figure since it can't do a backup while its "active" it should do it when unloading..

-Module-

Funtion  Mnu_Exit()

Unload ME

Screen.MousePointer = vbHourglass
 Dim fso  As New FileSystemObject
    Dim pathfrom As String, pathto As String
 pathfrom = App.Path & "\cabrera.mdb"
 pathto = App.Path & "\cabrera.bak"
    fso.CopyFile pathfrom, pathto
    Set fso = Nothing

End Funtion

The thing is that I have little expiriance on Access, But some good on VB, so I pasted this code I used on VB, It should have worked. If i can find how to add refreceses
I get two errors on the frist two lines, and I bet a few more After those.
How can I unload the program using code?? And if anyone can tell me If this coding is apropriate for this?? Also whats this Compact database I be seeing??-whats it all about?
Thank you

 

[ghudson]

So I figure since it can't do a backup while its "active"

Why not? I use the below function to backup the current db [while it is open] and I zip it up
(using Winzip) and rename the new backup file. You can easily modify it to meet your needs.

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 = "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

Also, you would have found my previous thread using the above function and many
other ideas if you had searched the forum for "backup".

HTH

 

[Galan]

I belive in one of your previous post you talked about "Compact database "?? What is this used for?

 

[Rob.Mills]

GHudson

Tried your code. All I changed were the names of the paths and files.

It runs through the whole thing and gives me the message at the end but no zip file is created and no error is given.

Could this be because I don't have a registered version of winzip?

 

[ghudson]

Not sure for I can not test that. What happens if you just use the code to create the zip file [no backup stuff] and then step F8 through the code? Do you see WinZip open up during the process?

 

[David b]

Using this code with unreg Winzip no probs. The initial problems I had were to do with the code running faster than the backup files were being created. A stategicaly placed message box or use of 5 seconds sleep solved it
David b

 

[JSDART!!]

Where did you place the msg box in th code

Using this code with unreg Winzip no probs. The initial problems I had were to do with the code running faster than the backup files were being created. A stategicaly placed message box or use of 5 seconds sleep solved it
David b

Hi David
I am finding that I can get the process to run but when I go to look in the Folder I see a slight second of a file and then it disappears I am wondering if I need this pause you are talking about and where it would be placed.
\Thanks
J

 

[namliam]

i have a related question...

i can do a dir and so on in VBA, but how about a bat file?

I want to have a bat file that does something like:

winzip -e C:\Temp\Some*.zip C:\Temp

Thus extracting every Some .... .zip file in the folder to the extraction folder..... A do while loop with dir in VBA will do the trick i know, but... i want/need a bat file...

Anybody?

Thanx

 

[Deleted member 28156]

Is it possible to do the above but not as complicated. So basically while database is open save a copy (with out winzip or finding a location to save it etc)

Cheers
Bikeboardsurf

 

[ghudson]

All you had to do was remove the code related to zipping the db. Like this...

Public Function BackupCopy() 

'This function will allow you to copy a db that is open, 

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

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 = "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 

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) & sBackupFile, vbInformation, "Backup Completed" 

End Function

 

[Deleted member 28156]

When I tried running that code I get an error:
'User defined type not defined' and it points to
FileSystemObject

Can you help?
Cheers again
Bikeboardsurf

 

[ghudson]

As I stated in the top of my fucntion...

You must set a reference to the 'Microsoft Scripting Runtime' for the fso.CopyFile part to work!

 

[carlnewboult]

when i run this is works but then brings up

"Run-time errror '424':

Object required

any ideas what might be going wrong

Cheers

 

[colette]

Minor Change to Code

Hi,
I tried the code and received errors on the line...
Dim fso As FileSystemObjectFileSystemObject
I changed the "Set" line to...
Set fso = CreateObject("Scripting.FileSystemObject")
and it worked.


Colette

 

[sunilvedula]

hi i tried the code but i get an error

It says that "File not found" and on the line "fso.CopyFile sSourcePath & sSourceFile, sBackupPath & sBackupFile, True"

i have a doubt is that do we need to create the file "BackupDB_" or does the proram create it for u.

 

[sunilvedula]

thanq it is resolved.

thanq it is resolved now.

 

[loo8866]

Hi there, ive been trying your backup code out of interest ! I seem to be getting a "path not found" error on the fso.copyfile line? any ideas?

 

[boblarson]

Make sure that the path you build doesn't contain extra backslashes, or has the right number (for example - If I have strPath of "C:\Temp" and I have strDbName of "MyDatabase.mdb" and concatenate them with strPath & strDbName, then it would end up with "C:\TempMyDatabase.mdb" which would be missing the backslash between "C:\Temp" and "MyDatabase.mdb" and it SHOULD be "C:\Temp\MyDatabase.mdb."

Also, make sure the file name contains the file type at the end (.mdb or .bak, etc.)

 

[loo8866]

I've checked that but i think its ok?! Can you have a look:

sSourcePath = "C:\"
sSourceFile = "db1.mdb"
sBackupPath = "C:\Backup\"
sBackupFile = "BackupDB_.mdb"

Set fso = New FileSystemObject

fso.CopyFile sSourcePath & sSourceFile, sBackupPath & sBackupFile, True

 

[boblarson]

So the folder C:\Backup exists and your database really is on your root directory?