run batch code in VBA to open a specific Access database file in runtime mode

Question

How would I run the following in a shell statement in MS Access?

 "C:\Program Files (x86)\Microsoft Office\Office14\msaccess.exe" "http://stoneplastics/Departments/Quality/Databases/LabelsNewRelease.accdb" /runtime

It works fine in a batch file. I have tried for 3 hours to get this to work and I am having trouble

Have a look at this vba procedure: http://stackoverflow.com/questions/37822050/cant-run-dir-from-wscript-shell-in-vba — Tim, Jul 22 '16 at 17:56

score 3 · Accepted Answer · answered Jul 25 '16 at 22:35

Since you will be running the VBA code from within an Access session, you can use SysCmd(acSysCmdAccessDir) to determine the folder where MSACCESS.EXE is located. That means you don't have to hard code file paths for multiple Access versions and 32 vs. 64 bit paths, and then use Dir() to check whether MSACCESS.EXE exists there.

Here is a working example from an Immediate window session in Access 2010:

strExe = SysCmd(acSysCmdAccessDir) & "MSACCESS.EXE"
strDb = "C:\share\Access\Database2.accdb"

strCommandLine = """" & strExe & """ """ & strDb & """ /runtime"
Debug.Print strCommandLine
"C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE" "C:\share\Access\Database2.accdb" /runtime

Shell strCommandLine, vbMaximizedFocus

When you adapt that as a VBA procedure, it will be simpler than what you have now. And it is more flexible because you needn't specify where to find MSACCESS.EXE; that is automatically discovered at runtime.

I used your code and it works like a clock. Not only will this be good for when new versions of access hit the market changing directories, but it also improved performance. Thank you very much :) — Daniel L. VanDenBosch, Jul 26 '16 at 18:36

score 0 · Answer 2 · answered Jul 25 '16 at 14:36

I figured it out. This was the code I used. (please note I changed the access database file.

 If Not Len(Dir("C:\Program Files\Microsoft Office\Office12\msaccess.exe", vbDirectory)) = 0 Then
 Call Shell(Chr$(34) & "C:\Program Files\Microsoft Office\Office12\msaccess.exe" & Chr$(34) & " " & Chr$(34) & "http://stoneplastics/Departments/Quality/Databases/ItemInspectionInstructionPrintOutRev3.accdb" & Chr$(34) & " /runtime", vbMaximizedFocus)
 End If
 If Not Len(Dir("C:\Program Files\Microsoft Office\Office14\msaccess.exe", vbDirectory)) = 0 Then
 Call Shell(Chr$(34) & "C:\Program Files\Microsoft Office\Office14\msaccess.exe" & Chr$(34) & " " & Chr$(34) & "http://stoneplastics/Departments/Quality/Databases/ItemInspectionInstructionPrintOutRev3.accdb" & Chr$(34) & " /runtime", vbMaximizedFocus)
 End If
 If Not Len(Dir("C:\Program Files\Microsoft Office\Office11\msaccess.exe", vbDirectory)) = 0 Then
 Call Shell(Chr$(34) & "C:\Program Files\Microsoft Office\Office11\msaccess.exe" & Chr$(34) & " " & Chr$(34) & "http://stoneplastics/Departments/Quality/Databases/ItemInspectionInstructionPrintOutRev3.accdb" & Chr$(34) & " /runtime", vbMaximizedFocus)
 End If
 If Not Len(Dir("C:\Program Files\Microsoft Office\Office15\msaccess.exe", vbDirectory)) = 0 Then
 Call Shell(Chr$(34) & "C:\Program Files\Microsoft Office\Office15\msaccess.exe" & Chr$(34) & " " & Chr$(34) & "http://stoneplastics/Departments/Quality/Databases/ItemInspectionInstructionPrintOutRev3.accdb" & Chr$(34) & " /runtime", vbMaximizedFocus)
 End If
 If Not Len(Dir("C:\Program Files (x86)\Microsoft Office\Office14\msaccess.exe", vbDirectory)) = 0 Then
 Call Shell(Chr$(34) & "C:\Program Files (x86)\Microsoft Office\Office14\msaccess.exe" & Chr$(34) & " " & Chr$(34) & "http://stoneplastics/Departments/Quality/Databases/ItemInspectionInstructionPrintOutRev3.accdb" & Chr$(34) & " /runtime", vbMaximizedFocus)
 End If
 If Not Len(Dir("C:\Program Files (x86)\Microsoft Office\Office12\msaccess.exe", vbDirectory)) = 0 Then
 Call Shell(Chr$(34) & "C:\Program Files (x86)\Microsoft Office\Office12\msaccess.exe" & Chr$(34) & " " & Chr$(34) & "http://stoneplastics/Departments/Quality/Databases/ItemInspectionInstructionPrintOutRev3.accdb" & Chr$(34) & " /runtime", vbMaximizedFocus)
 End If
 If Not Len(Dir("C:\Program Files (x86)\Microsoft Office\Office15\msaccess.exe", vbDirectory)) = 0 Then
 Call Shell(Chr$(34) & "C:\Program Files (x86)\Microsoft Office\Office15\msaccess.exe" & Chr$(34) & " " & Chr$(34) & "http://stoneplastics/Departments/Quality/Databases/ItemInspectionInstructionPrintOutRev3.accdb" & Chr$(34) & " /runtime", vbMaximizedFocus)
 End If
 If Not Len(Dir("C:\Program Files (x86)\Microsoft Office\Office11\msaccess.exe", vbDirectory)) = 0 Then
 Call Shell(Chr$(34) & "C:\Program Files (x86)\Microsoft Office\Office11\msaccess.exe" & Chr$(34) & " " & Chr$(34) & "http://stoneplastics/Departments/Quality/Databases/ItemInspectionInstructionPrintOutRev3.accdb" & Chr$(34) & " /runtime", vbMaximizedFocus)
 End If
 If Not Len(Dir("C:\Program Files\Microsoft Office 15\root\office15\msaccess.exe", vbDirectory)) = 0 Then
 Call Shell(Chr$(34) & "C:\Program Files\Microsoft Office 15\root\office15\msaccess.exe" & Chr$(34) & " " & Chr$(34) & "http://stoneplastics/Departments/Quality/Databases/ItemInspectionInstructionPrintOutRev3.accdb" & Chr$(34) & " /runtime", vbMaximizedFocus)
 End If

score 0 · Answer 3 · answered Jul 25 '16 at 16:08

Not sure how you figure "it didn't work" when you are passing the command to shell like dbmitch's answer post and enclosing it in quotes like dbmitch demonstrates in that post, but glad you got it working. :)

As an aside, since locating every possible path to access is apparently a requirement, why not just use Dir to find it in the first place? This would help error proof and future proof your code since the Office16 (32-bit) path is "\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE" and Office 17 will likely be different. If you create a Shell object and call the .Exec method, you can get the output of Command's DIR function (not VBA's DIR function...2 very different beasts!). This will be the path to Access. This also prevents any errors from a user installing Access/Office in a location other than default.

Dim oShell As Object 'New WshShell
Dim cmd As Object 'WshExec
Dim strPath as String 'Path to Access
Const WshRunning = 0

On Error GoTo wshError

Set oShell = CreateObject("Wscript.Shell")
'Note the two double quotes "". This causes VBA to insert one double quote.
'Its a little cleaner than " & Chr$(34) & "
'http://stackoverflow.com/questions/9024724/how-do-i-put-double-quotes-in-a-string-in-vba/38456820
Set cmd = oShell.Exec("cmd /c ""dir c:\*MSACCESS.EXE /a:-d /b /d /s""")
Do While cmd.Status = WshRunning
    'Do nothing
    DoEvents
Loop

strPath = cmd.StdOut.ReadAll
If strPath="" Then
    MsgBox "Access was not found."
    Exit Sub
End If

'We now have the path to Access so pass it to shell like you've done
Call Shell("""" & strPath & """" & _
    " ""http://stoneplastics/Departments/Quality/Databases/ItemInspectionInstructionPrintOutRev3.accdb"" /runtime" _
    , vbMaximizedFocus)

Set oShell = Nothing
Set cmd = Nothing
Exit sub

wshError:
On Error Resume Next
Msgbox cmd.StdErr.ReadAll
Resume Next

I like your answer better. I think when it came to dbmitch's solution, I was unable to get the proper syntax in place. When we upgrade, I will give the solution a shot :) — Daniel L. VanDenBosch, Jul 25 '16 at 19:03

run batch code in VBA to open a specific Access database file in runtime mode

3 Answers3