MS Access - VBA SHELL command problem...

Posted on 2011-04-20
Medium Priority
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35435580
try the codes from this link

LVL 46

Accepted Solution

aikimark earned 2000 total points
ID: 35435673
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

ID: 35435679
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


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

LVL 58
ID: 35435960
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

ID: 35435992
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

ID: 35436154
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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.

809 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