Running shell command from Access

I'm trying to zip some items using Shell.
These are the two commands
    1) pkzipc.exe -add C:\temp\ c:\temp\test.doc
    2) pkzipc -add -passphrase=@c:\temp\secret.txt -cryptalgorithm=aes c:\temp\ c:\temp\test.doc

So I've tried this code, that I saw elsewhere:
Dim strProgramName As String
    Dim strArgument As String

    strProgramName = "pkzipc.exe"
    strArgument = "-add c:\temp\ c:\temp\test.doc"

    Call Shell("""" & strProgramName & """ """ & strArgument & """", vbNormalFocus)

Open in new window

But it doesn't work. Not only that, but the command window closes too quickly, so I can't see why it failed.
So two questions:
a) What is the correct syntax for the Shell command to run both of the pkzip commands above
b) How do I keep the command window open so that I can see the results?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Rather than PKZip, I use 7-Zip (free) to zip and unzip files from Access.

Here is some code I used to zip ACCDE files that you can customize:
Sub ZipAccdeFile(psPathFile As String, psPathFileZip As String)
   On Error GoTo Proc_Err

   Const PATHFILE_7zip = "C:\Program Files\7-Zip\7z.exe"
   Dim sPath As String
   sPath = "D:\DATA\UserDatabases\"
   'strip ACCDE from End
   'add _ACCDE.Zip to end
   psPathFileZip = Left(psPathFile, Len(psPathFile) - 6) & "_ACCDE.ZIP"
   'delete zip file if it already exists
   If Dir(psPathFileZip) <> "" Then
      Kill psPathFileZip
   End If
'   For Each file In CreateObject("Scripting.FileSystemObject").GetFolder(SOURCE).Files
      Shell PATHFILE_7zip & " a -tzip " & psPathFileZip & " " & psPathFile
'   Next
   On Error Resume Next
   Exit Sub
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   ZipAccdeFile"

   Resume Proc_Exit

End Sub

Open in new window

> "b) How do I keep the command window open so that I can see the results?"

Better than trying to do that is to open a Windows Explorer window to the directory you are working with.  Press F5 to refresh the files.  The first time you WRITE a new file, it may not show up right away due to Windows just being slow.  That happened to me and I thought my zipping code didn't work ... well it did!
You can try this:

Dim strYourCommand As String
strYourCommand = """" & strProgramName & """ """ & strArgument & """"
Shell "cmd -k " & strYourCommand' it might be /k rather than -k...don't have OS access at the moment to try it

Open in new window

This should "k"eep the window open (-k) which can later be changed to -c to "c"lose the window. (it might be / rather than -, like /k or /c)
It should also show you the command which is being run, which I think will be something like "pkzipc.exe" "-add C:\temp\ c:\temp\test.doc"

What I suggest it to try to get the command to run from the command window first, then migrate the correct result back into your VB window (replacing quotes as needed.)
In some cases, you will need to send a "start" to get the command to run through a shell.  I think it's "cmd /k start pkzip..."

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jdhackettAuthor Commented:
The commands I listed actually do work from the command window.
The /k was the key, as it let me see the errors. There was no need for the extra quotes around the -add part.
My work code is below, thanks for the help

    Dim strProgramName As String
    Dim stArg1 As String
    Dim stArg2 As String
    Dim stArg3 As String
    Dim stCommand As String

    strProgramName = "pkzipc.exe"
    stArg1 = "-add"
    stArg2 = "c:\temp\"
    stArg3 = "c:\temp\test.doc"

    stCommand = "cmd /k " & """" & strProgramName & " " & stArg1 & " " & stArg2 & " " & stArg3 & """"
    Call Shell(stCommand, vbNormalFocus)

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.