VBA: How to run another application from MS Access

Question

I've been trying to get this issue figured out, and it seems that I cannot find the solution to the problem anywhere. Here was the first part: VBA Shell command always returns "File Not Found" In that question, it wasn't finding the application in the %APPDATA% folder for some odd reason, likely a security setting.

I have since moved the import tool to the same directory that I store the database in, with the small hope of getting it to work correctly.

My objective is to click a button in MS Access and have it run my import tool directly. Right now, I have it opening the folder that holds the tool. This works on my development machine which has admin privileges, but does not work on other machines without admin privs. The code right now looks something like the following:

Dim hProcess as Long
Dim myPath as String
Dim ex as String

ex = "C:\WINDOWS\explorer.exe "
myPath = Environ("ProgramFiles(x86)") & "\mytool\"

hProcess = Shell(ex & myPath, vbNormalFocus)

This allows the folder that holds the file to be opened, but only on machine accounts which have full administrator privileges. When running this on a machine account that has less than full privileges, it simply does nothing.

I have also tried the following:

Dim hProcess As Long
Dim myPath as String

myPath = Environ("ProgramFiles(x86)") & "\mytool\mytool.exe"
hProcess = Shell(myPath, vbNormalFocus)

This section "seems" to work in that it loads the application "mytool.exe" when I look at the process manager. However, after a few seconds (maybe 20), a dialog pops up stating that the application "mytool.exe" has stopped working.

One thing to note here is that I have admin privileges on my development machine, but I have all privileges on my home machine. On my home machine, this second code works with no issue whatsoever. At my development machine, it crashes, while on a restricted user machine, it doesn't do anything at all.

Are there any suggestions on how to open this application from MS Access while using less-than-admin privileges? Either to run the application directly or to at least open the folder in which said application resides.

Thanks!

P.S. I have tried both ShellAndWait and RunApplication code found on stackoverflow, neither of which works in this instance.

Answer 1

Check

Shell szFileName, vbNormalFocus

with some other executable. It can be some problem with your tool.

Answer 2

I always use ShellExecute from the Windows API when I need to execute something in VBA.
As far as I know, it works on machines without full privileges as well.

Example:

Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, _
    ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, _
    ByVal lpDirectory As String, ByVal lpnShowCmd As Long) As Long


Public Sub ShellEx(ByVal Path As String, Optional ByVal Parameters As String, Optional ByVal HideWindow As Boolean)

    If Dir(Path) > "" Then
        ShellExecute 0, "open", Path, Parameters, "", IIf(HideWindow, 0, 1)
    End If

End Sub

Now you can call ShellEx to run pretty much anything:

'run executable
ShellEx "c:\mytool.exe"

'open file with default app
ShellEx "c:\someimage.jpg"

'open explorer window
ShellEx "c:\"

Note that ShellEx has two optional parameters as well.
I didn't show this in the above examples, but you can:

• pass parameters to executables
• hide the window of the called executable

Answer 3

follow this link

MS ACCESS: LAUNCH AN APPLICATION FROM ACCESS 2003/XP/2000/97

here is an example of running an application in ms access

Private Sub Command1_Click()
Dim myPath As String
myPath = "C:\Program Files\mytool\mytool.exe"
Call Shell(myPath , 1)
End Sub

i wish it helps you

Answer 4

Just messing about with http://www.mombu.com/microsoft/scripting-wsh/t-vista-uac-problem-with-wscriptshell-run-method-1508617.html. I tried this on Windows 7 home PC.

Set objShell = CreateObject("Shell.Application")
myPath = Environ("ProgramFiles(x86)") & "\mytool"
Set objFolder = objShell.Namespace(myPath)
Set objFolderItem = objFolder.ParseName("mytool.exe")
objFolderItem.InvokeVerb "runas"

It might give you some ideas.