Back up and zip code from ghudson

[scouser]

Convert

David, wasn't thinking straight last night!! All converted now......

 

[ghudson]

scouser,

That is why I was asking you to search for all new files for that day. I am guessing that you should have a bunch of files named something like "BackupsBackupDB_01142004_012345.zip" in the "C:\Database\" directory because the sBackupPath = "C:\Database\Backups" did not end with a backslash like sBackupPath = "C:\Database\Backups\"

I trust that everything is now working for you!

 

[scouser]

Check

I will check tonight, but am quite sure there were no files anywhere on my system 'Database' included??
Thanks for your time,
Phil.

 

[WayneRyan]

Phil,

I changed the pathnames to suit my machine. The software
works fine. My ZIP file showed up and the copied .MDB was
deleted.

Wayne

 

[scouser]

Success

All working!! Thanks for all your help!! I must eat humble pie:
Catabase\Backups\ !!!!!!!!!!
Tried that many ways, thought I had changed that!!!!!!!!
many thanks guys,
Phil.

 

[gabo]

Problem.....

I try to use this code, but when I call the function trough a click button a received and error with the file system object I use access 2003 could youi please help me....
thank you very much

 

[ghudson]

I try to use this code, but when I call the function trough a click button a received and error with the file system object I use access 2003 could youi please help me....
thank you very much

Did you set the reference to the "Microsoft Scripting Runtime" as I mentioned?

Open any module. Then from the menu bar, click Tools, then click References... Then scroll down and find "Microsoft Scripting Runtime". Check it and then click the OK button. That is how you set a reference. My backup code should now work for you.

 

[gabo]

thanks work great......

thank you ghudson that was all my problem everything works great, great piece of code

 

[Oldsoftboss]

This is great stuff, and I will attempt to add it to by Db's. As mentioned a Db will soon grow to greater then the 1.4 mb. How much would be involved to alter the code to back up to a CD burner ?

Dave

 

[Oldsoftboss]

If I may...

One problem I have is when I try to enter a save path with spaces ie:

sBackupPath = "C:\"
sBackupFile = "BackUp" & Format(Date, "dd-mm-yyyy") & ".mdb"

is OK, but

sBackupPath = "C:\My Documents"
sBackupFile = "BackUp" & Format(Date, "dd-mm-yyyy") & ".mdb"

or

sBackupPath = "C:\"
sBackupFile = "Back Up" & Format(Date, "dd-mm-yyyy") & ".mdb"

generate errors.

I hope to sort this out so I can use the BrowseForFolder dialog box to select a save path.

Dave

 

[ghudson]

This is great stuff, and I will attempt to add it to by Db's. As mentioned a Db will soon grow to greater then the 1.4 mb. How much would be involved to alter the code to back up to a CD burner ?

Dave

I have never seen a sucessfull posted with code to allow Access to write directly to a CD burner. I suggest that you get a mini USB thumbnail drive. One sale with a rebate you can get a 64 megabyte thumbnail drive for less than fifteen dollars at any office supply store. I carry one with me daily.

 

[ghudson]

If I may...

One problem I have is when I try to enter a save path with spaces ie:

sBackupPath = "C:\My Documents"
sBackupFile = "Back Up" & Format(Date, "dd-mm-yyyy") & ".mdb"

generate errors.

I hope to sort this out so I can use the BrowseForFolder dialog box to select a save path.

Dave

Old DOS naming conventions comes back to haunt you. Try this...

sBackupPath = "C:\MyDocu~1"
sBackupFile = "BackUp~1" & Format(Date, "dd-mm-yyyy") & ".mdb"

You need to use the DOS short name for your files and paths.

 

[Oldsoftboss]

I do have a thumb drive, great thing and have edited the code and broken it up into 2 parts. The first uses the C:\Temp bit to back up the Db and then a second to copy it to the folder of choice.

It also check for WinZip and if it is not found, skips the first bit and just copies.

It can be called from either a cmdButton or Menu item.

Dave

PS: One other thing I woud like to do is, when selecting the file to backup, cut the path and use just the file name as the base name for the backup.

 

[ghudson]

One other thing I woud like to do is, when selecting the file to backup, cut the path and use just the file name as the base name for the backup.

Here is some code I have not used in quite a while...

Private Function ParseFileName()
On Error GoTo ParseFileName_Err

    Dim sFullName As String
    Dim sFilePathOnly As String
    Dim sDrive As String
    Dim sPath As String
    Dim sLocation As String
    Dim sFilename As String
    
    sFullName = tbExportImportName.Value
    
    ' Find the final "\" in the path.
    sPath = sFullName
    
    Do While Right$(sPath, 1) <> "\"
    sPath = Left$(sPath, Len(sPath) - 1)
    Loop
    
    ' Find the Drive.
    sDrive = Left$(sFullName, InStr(sFullName, ":") + 1)
    'tbDrive = sDrive
    
    ' Find the Location.
    sLocation = Mid$(sPath, Len(sDrive) - 2)
    'tbLocation = sLocation
    
    ' Find the Path.
    sPath = Mid$(sPath, Len(sDrive) + 1)
    'tbPath = sPath
    
    ' Find the file name.
    sFilename = Mid$(sFullName, Len(sPath) + 4)
    tbFileName = sFilename

ParseFileName_Exit:
    Exit Function

ParseFileName_Err:
    Msgbox Err.Number & " - " & Err.Description
    Resume ParseFileName_Exit

End Function

 

[Oldsoftboss]

One thing before testing that I noticed..

sFullName = tbExportImportName.Value

What is: tbExportImportName.Value

I presume it is the string of the full path??

Dave

 

[ghudson]

I presume it is the string of the full path??

Yes, that is correct.

I am using Access 97 so I could not view your attached db.

 

[willg]

Backing up a DB to a server and or local machine.

Hi all and thanks for the code, it's working a treat on my application . Was wondering if anyone could help me with the code to implement the back up on a server (if the user is connected) and the local machine in the one implemention.??????

Thanks,

Will

 

[ghudson]

I do not have the time to rewrite this for your needs but I will offer this advice [at not additional charge to you]...

You need to double the effort with two backup paths [sBackupPath1 & sBackupPath2]. Check if the user has a connection to the network by using the Dir() function and see if the network directory or a network file exists. If true then you know to also include the sBackupPath2 in the backup process.

You will have to modify the code and use some If statements to account for the network backup if the user is connected.

This should give you an idea of what I am thinking you need...

sSourcePath = "C:\Database\"
sSourceFile = "MyDB.mdb"
sBackupPath1 = "C:\Database\Backups\"
sBackupPath2 = "X:\Database\Backups\"
sBackupFile = "BackupDB_" & Format(Date, "mmddyyyy") & "_" & Format(Time, "hhmmss") & ".mdb"

sZipFile1 = sBackupPath1 & sZipFileName
sFileToZip1 = sBackupPath1 & sBackupFile
sZipFile2 = sBackupPath2 & sZipFileName
sFileToZip2 = sBackupPath2 & sBackupFile

Call Shell(sWinZip1 & " -a " & sZipFile1 & " " & sFileToZip1, vbHide)
Call Shell(sWinZip2 & " -a " & sZipFile2 & " " & sFileToZip2, vbHide)

Those steps do not include the Dir() test to see if the user has a connection to the network.

Good luck!

 

[willg]

That kinda makes sense though I have never used dir() before so let the fun begin

and thanks for the free advise!

Will

 

[Oldsoftboss]

Sorry I haven't got back earlier. I had already doubled the copy code, also added the parse bit, as well as including a replace bit to replace any spaces with an underscore.
The attachment has A2000 as well as A97 for ghudson

If improvements can be made, please feel free.

If it looks OK I will post it in the Code Sample forum.

Dave