using mdb files which are backed up hourly into zip files

[Robbie MacKinnon]

Hi,

I am new to access and am seeking advice on how to deal with the the following:
Every hour a zip file is created in an FTP folder. The zip file contains an .mdb access database file with any changes to the data since the last hours file.
What is the best method to get access to connect to the content of the zip files and pick up the changes to the data?



Thanks,

Henry Robe.

[June7]

First, have to extract the db from the zip.

I have code that creates a zip and copies file into the zip. Here is my code to create zip (gstrBasePath is a global constant declared in another module, replace it with your file path, ex: "C:\path string here\"):

'create empty zip folder
'found this on web, no idea what the Print line does but if it isn't there, this won't work
Open strZip For Output As #1
Print #1, "PK" & Chr$(5) & Chr$(6) & String(18, 0)
Close #1
'copy file into zip folder
Dim objApp As Object
Set objApp = CreateObject("Shell.Application")
'variable for source file doesn't seem to work in this line
'also double parens not in original code but won't work without
objApp.NameSpace((strZip)).CopyHere gstrBasePath & "Program\Editing\ConstructionExtract.accdb"

I guess the reverse would be quite similar - open the zip and copy from zip to other folder. I could not get a variable to work in constructing the path of file to copy into zip (tested but not need in this case). Might be same issue with copying out of zip. Review http://www.ehow.com/how_6908798_extr...-file-vba.html

Will the file name be different each time? If not, just set up links to the tables and each copy will replace the file. Otherwise, VBA code will have to set table links or just open connection to the db. Run INSERT SELECT and/or UPDATE sql actions.

[joanas]

hey thanks

i tried this code and it seems to work ok

Sub Zip_File()
Dim strDate As String, DefPath As String
Dim oApp As Object
Dim FName, FileNameZip

DefPath = CurrentProject.Path
If Right(DefPath, 1) <> "\" Then
DefPath = DefPath & "\"
End If

FileNameZip = "Path\MyFilesZip.zip"

FName = "C:\Data\db1.mdb"

'Create empty Zip File
NewZip (FileNameZip)

Set oApp = CreateObject("Shell.Application")

oApp.NameSpace(FileNameZip).CopyHere FName

MsgBox "You find the zipfile here: " & FileNameZip
Set oApp = Nothing
End Sub

Sub NewZip(sPath)
'Create empty Zip File
Dim oFSO, arrHex, sBin, i, Zip
Set oFSO = CreateObject("Scripting.FileSystemObject")
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)
sBin = sBin & Chr(arrHex(i))
Next
With oFSO.CreateTextFile(sPath, True)
.Write sBin
.Close
End With
End Sub

[joanas]

to add more files to the zip i just add this two lines:

FName2 = "C:\Data\db2.mdb"

oApp.NameSpace(FileNameZip).CopyHere FName2