Back up On exit

Galan · Oct 26, 2003

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-

Code:

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 · Oct 26, 2003

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.

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 = "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 · Oct 26, 2003

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

Rob.Mills · Dec 11, 2003

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 · Dec 11, 2003

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 · Dec 12, 2003

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!! · Apr 29, 2004

Where did you place the msg box in th code

David b said:
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 · Apr 29, 2004

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 · Sep 12, 2005

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 · Sep 12, 2005

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

Code:

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 · Sep 13, 2005

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 · Sep 13, 2005

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 · Oct 12, 2005

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 · Dec 20, 2005

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 · Feb 22, 2007

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 · Feb 23, 2007

thanq it is resolved.

thanq it is resolved now.

loo8866 · Mar 3, 2007

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 · Mar 3, 2007

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 · Mar 3, 2007

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 · Mar 3, 2007

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

Back up On exit

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

The Mailman - AWF VIP

Deleted member 28156

Guest

Registered User.

Deleted member 28156

Guest

Registered User.

Registered User.

Registered User.

Sunil

Sunil

Registered User.

Smeghead

Registered User.

Smeghead

Similar threads

Users who are viewing this thread