MS Access - VBA SHELL command problem...
Posted on 2011-04-20
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)
Notes.Value = ZIPEXELOCATION & " a " & Chr(34) & ZipFileName & Chr(34) & " " & Chr(34) & fileToBeZipped & Chr(34)