MS Access - VBA SHELL command problem...

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
Who is Participating?
aikimarkConnect With a Mentor Commented:
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.
Rey Obrero (Capricorn1)Commented:
try the codes from this link
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

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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

careybrophAuthor Commented:
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.
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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.