Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How can I compress a file using VB-code in MS Access

Posted on 2011-10-20
2
Medium Priority
?
438 Views
Last Modified: 2013-12-20
I have a file that is generated in my MS Access db. I want to be able to send the same file by email (also using VB-code); however, Outlook doesn't like the file format, so the file needs to be compressed to *.zip before sending it. The file is accessible from the same network drive as the db itself.

Can the compression be done using VB-code in MS Access?

Code samples deeply appreciated :)
0
Comment
Question by:PerMagnusStrom
2 Comments
 
LVL 28

Accepted Solution

by:
omgang earned 2000 total points
ID: 37000052
Here's sample code to add files to a zip archive.  If Outlook doesn't like the file type now I believe it still won't like it even when compressed in a zip archive.  You may need to change the file extension.
OM Gang

Public Function AddFilesToZip()
On Error GoTo Err_AddFilesToZip

        'Declarations
    Dim objFSO As Object, objZip As Object, objShell As Object
    Dim objFolder As Object, objFile As Object
    Dim sngStart As Single
    Dim strPath As String, strZip As String
   
    strPath = "C:\test\"   'Path to read files from
    strZip = "C:\test2\omgang.zip"  'Output zip file
   
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objZip = objFSO.CreateTextFile(strZip)
    objZip.WriteLine Chr(80) & Chr(75) & Chr(5) & Chr(6) & String(18, 0)
    objZip.Close
 
    Set objShell = CreateObject("Shell.Application")
    Set objFolder = objFSO.GetFolder(strPath)
 
        'loop through files - adding them to the zip
    For Each objFile In objFolder.Files
       
        objShell.NameSpace("" & strZip).CopyHere objFile.PATH
       
        sngStart = Timer
        Do While Timer < sngStart + 2
            DoEvents
        Loop

    Next

Exit_AddFilesToZip:
        'destroy object variables
    Set objFile = Nothing
    Set objFolder = Nothing
    Set objShell = Nothing
    Set objZip = Nothing
    Set objFSO = Nothing
    Exit Function

Err_AddFilesToZip:
    MsgBox Err.Number & ", " & Err.Description, , "Error in Function AddFilesToZip of Module Module7"
    Resume Exit_AddFilesToZip
   
End Function
0
 

Author Closing Comment

by:PerMagnusStrom
ID: 37000220
Simply outstanding :)

Thank you so much!

 - PM
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question