MS Access - VBA SHELL command problem...

Posted on 2011-04-20
Last Modified: 2012-05-11
So, after about two hours of searching and testing, I'm stuck.  Hoping someone might have an idea on what to try next!

I have an MS Access app where I zip a file using the SHELL command.  It just doesn't seem to want to work.  I've asked nicely.  I've threatened my PC.  All to no avail.

The code is below.  Notice the following.

1. The hard-coded path to the command line zip utility.
2. It takes two parameters (name of the zip file and the file to be zipped)
3. I'm capturing a return value that seems to change every time I run it.  But my understanding is that anything other than a "0" is a good sign.
4. I display the return value.  I get legit numbers.  Never get a zero.
5. At one point, I had it stuff the string into a text field.  I then copied the resulting text into a command window and ran it manually... it works PERFECT.  So, I'm very confident in the command string.

BUT, when the code is run via my app, it "acts" like it runs,but I don't get the zip file.

Unfortunately, I can't find a way to keep the shell open long enough to see what it's actually doing.

Any thoughts, guidance or ideas would be much appreciated.

Private Sub ZipFile_FX(ZipFileName As String, fileToBeZipped As String)
Dim ReturnVal As Double

    Const ZIPEXELOCATION = "\\fs1\admin\zipexe\7za.exe"

    ReturnVal = Shell(ZIPEXELOCATION & " " & Chr(34) & ZipFileName & Chr(34) & " " & Chr(34) & fileToBeZipped & Chr(34), vbNormalFocus)

    MsgBox ReturnVal

    Notes.Value = ZIPEXELOCATION & " a " & Chr(34) & ZipFileName & Chr(34) & " " & Chr(34) & fileToBeZipped & Chr(34)

End Sub
Question by:careybroph
    LVL 119

    Expert Comment

    by:Rey Obrero
    try the codes from this link
    LVL 44

    Accepted Solution

    1. Shell() is an asynchronous function.  Your code continues without waiting for the shelled process to complete
    2. The value returned by the Shell() function is the process identifier.  You will have to use Win API or WMI calls to monitor the process.  You are seeing different process numbers, not a completion code.
    3. What is the path to the target and destination files?
    You might make a batch file that launches 7zip and uses two parameters for the target and source files.  Then Shell the batch file (with path, of course) and include the two other files as parameters.  Your batch file can conclude with a PAUSE statement instead of an EXIT statement.  You will be able to see the compression operation messages.

    You might want to direct the status and error message streams to one or more files.  That would also help you debug the process.
    LVL 26

    Expert Comment

    Something to keep in mind is NTFS permissions and the context in which the shell runs.
    What you (username) can do may not be something the app(system) can do.

    I've had it happen where permissions came into play.

    One way I got around it was to build a batch file line by line using a textstream object and then executing the batch file.
    Here is some code that gives the general idea
    'Required references
    'assumes DAO 3.6
    'assumes Windows Script Host Object Model
    Dim fs As Object
    Set fs = CreateObject("Scripting.FileSystemObject")
    Dim x As Integer
    Dim wait As Double
    Dim BatFile As TextStream
    Dim Success As Boolean
    'snag a recordset of the pictures that we want to nail into the report
    Dim db As Database
    Dim rs As Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("select distinct path from tblPictures where jobid = " & Forms!frmJobID!JobID, dbOpenDynaset, dbSeeChanges)
    If rs.RecordCount = 0 Then Exit Function
    Set BatFile = fs.CreateTextFile("c:\prod\" & ReturnComputerName & ".cmd", True)
    If fs.FolderExists("c:\prod\temp") = True Then
        fs.DeleteFolder "c:\prod\temp", True
        fs.CreateFolder ("c:\prod\temp")
        fs.CreateFolder ("c:\prod\temp")
    End If
    BatFile.WriteLine ("c:")
    BatFile.WriteLine ("cd \prod")
    'BatFile.WriteLine ("cd " & Chr(34) & Me!Path & Chr(34))
    Do Until rs.EOF
        BatFile.WriteLine ("C:\Prod\PhotoResize700O.exe -c" & Chr(34) & "C:\prod\temp\<PATH><NAME>.jpg" & Chr(34) & " " & Chr(34) & Left(rs!Path, Len(rs!Path) - 1) & Chr(34))
    BatFile.WriteLine (vbCrLf)
    'BatFile.WriteLine ("pause")
    Set BatFile = Nothing
    Shell "cmd /c c:\prod\" & ReturnComputerName & ".cmd"

    Open in new window

    LVL 56

    Expert Comment

    by:Jim Dettman (Microsoft MVP/ EE MVE)
    To further along what has already been said, see the attached code to wait for a shelled process to finish and below, which is a another sample of what Nick suggested (this is doing a PDF conversion process using Ghost Script).  By doing it this way, you can put a stop on the code before it calls shell, jump to a command window, and execute the batch file manually.

                  'Generate file name to use for PDF output and script file.
    170           strPDFFileName = GetTempFileName_TSB(AppShortName())
    180           strPDFFileName = Replace(strPDFFileName, ".tmp", ".pdf")
    190           strPDFScriptFile = strPDFProgPath & AppShortName() & "_MKPDF.bat"
                  ' Change printer driver output into a PDF
                  ' Script the batch file
    200           Open strPDFScriptFile For Output As #1
    210           Print #1, "" & left(strPDFProgPath, 2)
    220           Print #1, "" & "CD " & Chr(34) & Mid(strPDFProgPath, 3) & Chr(34)
    230           Print #1, "" & Chr(34) & strPDFScriptFile & Chr(34) & " " & strPRNFile & " /D /V1.4 /O" & strPDFFileName
    240           Close #1
                  ' Execute batch file and wait till done.
    250           lngHWnd = Shell(Chr(34) & strPDFScriptFile & Chr(34), vbMinimizedNoFocus)
    260           WaitWhileRunning (lngHWnd)
    270           RunReportAsPDF = strPDFFileName

    Open in new window

    Private Declare Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As Long, lpExitCode As Long) As Long
    Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
    ' Used for wait check.
    Const STILL_ACTIVE = &H103
    Public Sub WaitWhileRunning(lngHWnd As Long)
            Dim lngExitCode As Long
            Dim lnghProcess As Long
    10      lngExitCode = STILL_ACTIVE
    20      lnghProcess = OpenProcess(PROCESS_QUERY_INFORMATION, False, lngHWnd)
    30      If lnghProcess > 0 Then
    40        Do While lngExitCode = STILL_ACTIVE
    50          Call GetExitCodeProcess(lnghProcess, lngExitCode)
    60          DoEvents
    70        Loop
    80      End If
    End Sub

    Open in new window


    Author Closing Comment

    Seeing that I already had a routine that called the zip exe using two parameters, it was extremely easy to create a batch file that called the zip exe with two parameters.

    Thanks to others as well.  This was the most straightforward solution.
    LVL 26

    Expert Comment

    That was elegant.
    I'll have to steal that.
    Camera devices that don't report as drive letters are the bane of my existence.
    I've got WIA stuff in a VBScript to take the pictures off the cameras and write the paths.
    Getting the code to pause gracefully has been, hmm...a bit clunky.

    Thanks for the post


    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now