Copying and zipping Databases

[joeserrone]

Hello Everyone,
I have several databases across different servers that I would like to have every day copied in one location on another server and then zipped. I heard that MDB file cannot be copied while in use which is something I need to do because of different times the databases will be in use. I also heard that a copy can be created using Jet command but can't find much on the subject.

I would like to basically have a way to copy various databases from a network location to another location, I'm also hoping to see if these files can also be zipped after they are copied.

Have you guys had any success in this?

 

[DCrake]

Do a search on this forum for Backup To Zip

 

[shadow9449]

Hello Everyone,
I have several databases across different servers that I would like to have every day copied in one location on another server and then zipped. I heard that MDB file cannot be copied while in use which is something I need to do because of different times the databases will be in use. I also heard that a copy can be created using Jet command but can't find much on the subject.

I would like to basically have a way to copy various databases from a network location to another location, I'm also hoping to see if these files can also be zipped after they are copied.

Have you guys had any success in this?

I use Albert Kallal's solution. It's easy to implement and it works fabulously:

http://www.members.shaw.ca/AlbertKallal/zip/index.htm

SHADOW

 

[jal]

Here's some code that I got from Code Project to zip a file (I had to translate it from C# to VBA).

Add a reference to Microsoft Shell controls and Automation
Also add a reference to Windows Scripting Host.
'the dest zip file should include the zip ext.

'Does not throw an error if the zipfile already exists - just overwrites it.
'The zipFileName is the dest zipfile path ending in .zip extension
Private Sub zipFile(ByVal sourceFile As String, ByVal ZipFileName As String)
'Create a XP-style Windows Zip folder called ZipFileName
Dim arrHex
Dim strBin As String
Dim i As Long
arrHex = Array(80, 75, 5, 6, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
For i = 0 To UBound(arrHex)
strBin = strBin & Chr(arrHex(i))
Next
Dim fso As New IWshRuntimeLibrary.FileSystemObject
Dim stream As IWshRuntimeLibrary.TextStream
Set stream = fso.CreateTextFile(ZipFileName)
stream.Write strBin
stream.Close
Set fso = Nothing
Set stream = Nothing
'Save the source file to the zip folder
Dim oShell As New Shell32.Shell
oShell.NameSpace(ZipFileName).CopyHere sourceFile, 4
Set oShell = Nothing
End Sub

And to decompress:

'In this case I only extract one item
Public Sub UnZipFile(ByVal zipFile As String, ByVal strDestFolder As String)
Dim fso As New IWshRuntimeLibrary.FileSystemObject
If Not fso.FolderExists(strDestFolder) Then fso.CreateFolder (strDestFolder)
Set fso = Nothing
Dim oShell As New Shell32.Shell
Dim oCompressedFolder As Shell32.Folder
Set oCompressedFolder = oShell.NameSpace(zipFile)
Dim oDestFolder As Shell32.Folder
Set oDestFolder = oShell.NameSpace(strDestFolder)
Dim itm As Shell32.FolderItem
Call oDestFolder.CopyHere(oCompressedFolder.items.Item(0), 20)
Set oShell = Nothing
Set oCompressedFolder = Nothing
Set oDestFolder = Nothing
End Sub

 

[jal]

The Code Project article also provided code for zipping an entire folder but I haven't translated that to VBA yet. Here's the C# code from my notes:

//Create an empty zip file
byte[] bytesToWrite = new byte[]{80,75,5,6,0,0,0,0,0, 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0};
FileStream fs = File.Create(strDestZipFile);
fs.Write(bytesToWrite, 0, bytesToWrite.Length);
fs.Flush();
fs.Close();
fs = null;
//Copy a folder and its contents into the newly created zip file
Shell32.ShellClass oShell = new Shell32.ShellClass();
Shell32.Folder oSrcFlder = oShell.NameSpace(strSourceFile);
Shell32.Folder oDestFlder = oShell.NameSpace(strDestZipFile);
Shell32.FolderItems items = oSrcFlder.Items();
oDestFlder.CopyHere(items, 20);
//Ziping a file using the Windows Shell API
//creates another thread where the zipping is executed.
//This means that it is possible that this console app
//would end before the zipping thread
//starts to execute which would cause the zip to never
//occur and you will end up with just
//an empty zip file. So wait a second and give
//the zipping thread time to get started
System.Threading.Thread.Sleep(1000);
In the first example, Value 4 was used to suppress the progress bar. the other possible values (presumably it's a bitmask arrangement).
0 Default. No options specified.
4 Do not display a progress dialog box.
8 Rename the target file if a file exists at the target location with the same name.
16 Click "Yes to All" in any dialog box displayed.
64 Preserve undo information, if possible.
128 Perform the operation only if a wildcard file name (*.*) is specified.
256 Display a progress dialog box but do not show the file names.
512 Do not confirm the creation of a new directory if the operation requires one to be created.
1024 Do not display a user interface if an error occurs.
4096 Disable recursion.
8192 Do not copy connected files as a group. Only copy the specified files.

 

[Mike375]

I use Albert Kallal's solution. It's easy to implement and it works fabulously:

http://www.members.shaw.ca/AlbertKallal/zip/index.htm

SHADOW

That is real neat Also Zipped itself OK.

I could use that to zip the DB that is open and then apply my backup system to the zip file with date/time stamp.

Great stuff

 

[jal]

I just translated the C# folder example:

Private Sub zipThisFolder(ByVal strSourceFolder As String, ByVal strDestZip As String)
'Create a XP-style Windows Zip folder called ZipFileName
Dim arrHex
Dim strBin As String
Dim i As Long
arrHex = Array(80, 75, 5, 6, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
For i = 0 To UBound(arrHex)
strBin = strBin & Chr(arrHex(i))
Next
Dim fso As New IWshRuntimeLibrary.FileSystemObject
Dim stream As IWshRuntimeLibrary.TextStream
Set stream = fso.CreateTextFile(strDestZip)
stream.Write strBin
stream.Close
Set fso = Nothing
Set stream = Nothing
'Copy a folder and its contents into the newly created zip file
Dim oShell As New Shell32.Shell
Dim oSourceFolder As Shell32.Folder, oDestFolder As Shell32.Folder, items As Shell32.FolderItems
Set oSourceFolder = oShell.NameSpace(strSourceFolder)
Set oDestFolder = oShell.NameSpace(strDestZip)
Set items = oSourceFolder.items
Call oDestFolder.CopyHere(items, 20)
Set oShell = Nothing
End Sub

 

[jal]

Now I've got to figure out how to unzip it.

 

[jal]

Woops, forgot, the unzip code above already does the unzipping.

 

[Mike375]

I use Albert Kallal's solution. It's easy to implement and it works fabulously:

http://www.members.shaw.ca/AlbertKallal/zip/index.htm

SHADOW

I just installed within another DB and bound the form to a table so the entires for path remained. Stuck an Autoexec macro in the DB I use that runs the backup and and called a batch file at the end of the Zipper code to open the backup DB. On the DB that runs the backup I only had to do Find/Repace form mdb to zip. Fantastic.

Can zipping a DB while open cause corruption?

 

[shadow9449]

I just installed within another DB and bound the form to a table so the entires for path remained. Stuck an Autoexec macro in the DB I use that runs the backup and and called a batch file at the end of the Zipper code to open the backup DB. On the DB that runs the backup I only had to do Find/Repace form mdb to zip. Fantastic.

Can zipping a DB while open cause corruption?

I've never experienced a problem with zipping while open. I hope Mr. Kallal hasn't either

SHADOW