Working With Console Tools – Microsoft Access

We recently had a project that required us to interact with console tools. People might have different terms for those type of tools so when I speak of console tools, I’m referring to a program or script that opens a command prompt and output stuff to that prompt and maybe accept user input by typing via the command prompt. An example of such application might be ipconfig, chkdsk, telnet, wscript/cscript and so forth.

Normally, I’m a firm believer in using APIs or type library instead of console tools whenever I need to interact with something outside Access. I tend to shun Shell Function or its cousin ShellAndWait Function because there are many problems with those approaches. Let’s count…

• When you use Shell, there is no way to tell whether the shelled program completed successfully or had an error.
• Exit Code is not necessarily the only way nor the best way to signal success or failure. For instance, you might get 0x0 exit code, but there were information or warning messages that you might not want to ignore.
• No progress monitoring. You can either let it fly freely, but you wouldn’t know when it’s done or if you opted to use ShellAndWait, you’re potentially twiddling your thumbs for several minutes (potentially forever!).
• No way to read the output from the console tool.
• No guaranteed way to terminate the process you shelled.

But in this situation, we had no API nor library available to us; only a console tool. What should we do then?

Enter WScript Host Model

There are two objects that might be interesting: WshShell Object and WshScriptExec Object. Both are part of the library “Windows Script Host Model” and can be referenced if you want early-binding (but of course you know that we should distribute our production code late-bound).

Looking over the documentation, WshShell.Run is basically the same thing as Shell function we already have. Not very interesting. But wait, the Exec method which returns the WshExec object looks much more interesting. Just reviewing the methods and properties, we can see that having a WshExec object allows us to:

• Detect if it’s still running, has failed or is finished
• Get the exit code
• Terminate the process (without the guesswork of whether it’s our process or not)
• Grab the StdIn, StdOut and StdError streams and, therefore, read the output emitted by the console tools

Therefore, you could quickly write up some code on an Access form something like this to print out all the network connection to the Access form:
Dim objShell As Object 'IWshRuntimeLibrary.WshShell
Dim objExec As Object 'IWshRuntimeLibrary.WshExec
Dim objStdOut As Object 'IWshRuntimeLibrary.TextStream

Const WshRunning As Long = 0 ‘Not needed if early-binding

Dim strCommand As String

Set objShell = CreateObject(“WScript.Shell”)

strCommand = “netstat -a”

Set objExec = objShell.Exec(strCommand)
Set objStdOut = objExec.StdOut

Do While objExec.Status = WshRunning
If Not objStdOut.AtEndOfStream Then
Me.txtOutput.Value = Me.txtOutput.Value & objStdOut.ReadLine & vbNewLine
End If
Loop
Me.txtOutput.Value = Me.txtOutput.Value & objStdOut.ReadAll & vbNewLine
Me.txtOutput.Value = Me.txtOutput.Value & “Exit Code: ” & objExec.ExitCode

Set objStdOut = Nothing
Set objExec = Nothing
Set objShell = Nothing
Now you can have the Access form display the output (via a textbox control named ‘txtOutput’). Slick!

Well, not quite there…

However, there are few gotchas.

First biggest gotcha is that the Exec method won’t let you specify the Windows style as you could with either Shell function or WshShell.Run method. That’s a big bummer but not insurmountable. This is an example where if we use Windows API, we would have more control because we still can specify the windows style via one of Windows’ Shell*** functions.

However, to do this all with Windows API would require a boatload of Declare statement, and a lot of time & patience to set all the necessary plumbing code. As you saw, WScript already provides most of the functions and I’m not too eager to write more code. Nobody’s paying me by lines of code I write! Fortunately (unfortunately?), there is a hack available: I can at least specify the position of the console windows next time it is opened. If you’ve ever twiddled with your console windows’ properties, you might

Fortunately (unfortunately?), there is a hack available: I can at least specify the position of the console windows next time it is opened. If you’ve ever twiddled with your console windows’ properties, you might have noticed that there is also a default setting that when changed, applies to any new console windows you create subsequently. And it turns out they are all stored in the registry, typically in the path: HKCU\Console. With that, you can “hide” an Exec’d console windows as thus:
objShell.RegWrite "HKCU\Console\WindowPosition", 4096, "REG_DWORD"
Set objExec = objShell.Exec(strCommand)
'Deleting the key will cause the console window to open in normal position again
objShell.RegDelete "HKCU\Console\WindowPosition"
I have to emphasize this is a hack, and you are potentially overwriting some poor sod’s original default setting. A better approach might be to first read the original value, store it and restore it when you are done. This also must be ensured that restoration is always done even if you had an error in your procedure that led to early termination. Otherwise, you might find that next time you open the command prompt, you can’t see it!

You first, I’ll wait

The other gotcha I found out about WshExec object is that if I grab a reference to its Std***, I’m actually blocking the process from writing to the Std***. It can only write to the Std*** after I’ve read the Std*** stream. It’s especially insidious because when I first tested it, it worked great on my computer. I gave the code to my co-worker and was told it was slower. But if my co-worker ran the same command in a console windows, it was quick to execute. A process that should have taken about 5 minutes on one computer suddenly took 30 minutes to run on another computer. I never understood why it didn’t happen on my computer and attributed it to the differences in the hardware or the environment.

If the console tool only generates a few lines of output, you likely won’t even notice the difference on most of machines you encounter. In our case, we were generating anywhere from 50 lines to several hundred lines of output. That in itself was enough to cause some machines to run very slowly. We found that if we did NOT grab a reference to the Std*** objects, the WshExec object will allow the process to run as fast as it possibly can.

File to rescue!

Because we do want to read the output in our Access form, but at the same time we couldn’t afford to block the console tool, we adjusted the command from this:
strCommand = "<some path to my fancy-pants console tool>"
to this:
strCommand = """<some path to my fancy-pants console tool>"" > ""C:\Temp\output.log"""
However, this makes for a new gotcha – running the command directly in the Exec method doesn’t work even though it is supposed to as per the documentation. However, if you wrap it in a batch file and instead exec a batch file, the redirection will happen.

The console window will no longer emit the output – it’s all going to a file now. Even though it’s still running and the file is busily being written to, we still can open it and read from it concurrently:
Set objExec = objShell.Exec(strCommand)
'Do NOT grab a reference to any of Std***!
'Set objStdOut = objExec.StdOut
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextStream = objFSO.OpenTextFile("C:\temp\batch.bat", ForWriting, True)
objTextStream.Write strCommand
objTextStream.Close
Set objTextStream = objFSO.OpenTextFile("C:\temp\output.log", ForReading, True)
Set objExec = objShell.Exec("C:\temp\batch.bat")

Do While objExec.Status = WshRunning
If Not objTextStream.AtEndOfStream Then
Me.txtOutput.Value = Me.txtOutput.Value & objStdOut.ReadLine & vbNewLine
End If
Loop
So even though we didn’t end up using WshExec’s Std*** properties, we were still able to monitor its running status and collect the output, and display it in our pretty Access form without blocking the process at all.

If I have to go, you must go too

If our user got bored, they were free to simply interrupt the tool. For example, if they close the form, we can ensure that the console tool shuts down:
Private Sub Form_Close()
objExec.Terminate
End Sub
This is useful when you don’t want the console tool to keep running in the background or to ensure it shouldn’t be running, maybe because it’d be interfering with other areas of the application, so you don’t want users to get errors when they try to use other areas due to the hidden instance still whirring away and blocking the resources.

All’s well that ends well

Even though I ended up writing a bit more code, I was fairly happy that I still had a fairly high-level interface for managing console tools and without having to derive into APIs or dealing with 32-bit vs. 64-bit differences. I also liked the fact that we can redirect the output into a control on our form and provide some sort of monitoring beyond simply just waiting for an exit code (and wondering what it really means since the meanings is defined by the author). Though this is a rough sample, I hope others may find this useful for situations where Shell or ShellAndWait just won’t do.

Complete Code Sample
'This code would be placed on the module behind an Access form.
'NOTE: error handling code NOT included for brevity's sake

‘The code also assumes that certain controls are present:
‘ A textbox control named “txtOutput
‘ A command button control named “cmdRun”
‘ A command button control named “cmdClose”

‘Module-level object variables; late-binding is assumed
‘To get intellisense, reference the Windows Script Host Model
‘at %systemroot%\system32\wschom.ocx
‘and Scripting Runtime Library
‘at %systemroot%\system32\scrrun.dll
Private objShell As Object ‘IWshRuntimeLibrary.WshShell
Private objExec As Object ‘IWshRuntimeLibrary.WshExec

‘Constants to support late-binding
Private Const WshRunning As Long = 0
Private Const ForReading As Long = 1
Private Const ForWriting As Long = 2

‘Constant for command & log file, including full path to the desired console tool
Private Const MyBatchFile As String = _
“C:\temp\my.bat”
Private Const MyOutputLog As String = _
“C:\temp\log.out”
Private Const MyCommand As String = _
“””C:\Windows\system32\ipconfig.exe”” /all > “”” & MyOutputLog & “”””

‘Temporary copy of Registry value (if any) for windows position
Private lngOriginalPosition As Long

‘Click Event handler for cmdRun
Private Sub cmdRun_Click()
Dim objFSO As Object ‘Scripting.FileSystemObject
Dim objTextOutput As Object ‘Scripting.TextStream

‘Prevent repeated clicking of the button
Me.cmdRun.Enabled = False

‘Clear the previous output
Me.txtOutput.Value = vbNullString

‘Reset any previous running instances just in case
TerminateShell

Set objFSO = CreateObject(“Scripting.FileSystemObject”)
Set objTextOutput = objFSO.OpenTextFile(MyBatchFile, ForWriting, True)
objTextOutput.Write MyCommand
objTextOutput.Close

Set objTextOutput = objFSO.OpenTextFile(MyOutputLog, ForReading, True)

Set objShell = CreateObject(“WScript.Shell”)
HideCommandWindowPosition
Set objExec = objShell.Exec(MyBatchFile)

Do While objExec.Status = WshRunning
If Not objTextOutput.AtEndOfStream Then
Me.txtOutput.Value = Me.txtOutput.Value & objTextOutput.ReadLine & vbNewLine
End If
Loop

If Not objTextOutput.AtEndOfStream Then
Me.txtOutput.Value = Me.txtOutput.Value & objTextOutput.ReadAll & vbNewLine
End If
Me.txtOutput.Value = Me.txtOutput.Value & “Exit Code: ” & objExec.ExitCode

RestoreCommandWindowPosition

Me.cmdRun.Enabled = True
End Sub

‘Click Event handler for cmdClose button
Private Sub cmdClose_Click()
TerminateShell
DoCmd.Close acForm, Me.Name
End Sub

Private Sub HideCommandWindowPosition()
On Error Resume Next
lngOriginalPosition = objShell.RegRead(“HKCU\Console\WindowPosition”)
objShell.RegWrite “HKCU\Console\WindowPosition”, &H1000, “REG_DWORD”
End Sub

Private Sub RestoreCommandWindowPosition()
On Error Resume Next
If lngOriginalPosition > 0 And lngOriginalPosition <> &H1000 Then
objShell.RegWrite “HKCU\Console\WindowPosition”, lngOriginalPosition, “REG_DWORD”
Else
objShell.RegDelete “HKCU\Console\WindowPosition”
End If
End Sub

Private Sub TerminateShell()
‘Reset both objExec and objShell
‘If necessary, terminate any process already running
If Not objExec Is Nothing Then
If objExec.Status = WshRunning Then
objExec.Terminate
End If
Set objExec = Nothing
End If
If Not objShell Is Nothing Then
Set objShell = Nothing
End If
End Sub

Have you found other uses for WshShell object or WshExec object? If so, share it in comments!