Access: Shell cmd Open MDB

Question

I have been using the following command to open another MDB Access file via VBA:

Shell "cmd /c " & Chr(34) & strNewFullPath & Chr(34), vbHide

strNewFullPath is the full path of the MDB file. Works fine when using Access 2010, but doesn't run on Access 2003. If I run the command in a XP DOS terminal it DOES run.

What other command can I use that should work on Access 2003 up and with the Access Runtime?

Answer 1

You can use the Win32 API to find the EXE name associated with the file type and prepend it to your shell command like this:

Private Declare Function FindExecutable Lib "shell32.dll" Alias "FindExecutableA" (ByVal lpFile As String, ByVal lpDirectory As String, ByVal lpResult As String) As Long

Public Function GetExecutableForFile(strFileName As String) As String
   Dim lngRetval As Long
   Dim strExecName As String * 255
   lngRetval = FindExecutable(strFileName, vbNullString, strExecName)
   GetExecutableForFile = Left$(strExecName, InStr(strExecName, Chr$(0)) - 1)
End Function

Sub RunIt(strNewFullPath As String)        
   Dim exeName As String

   exeName = GetExecutableForFile(strNewFullPath)         
   Shell exeName & " " & Chr(34) & strNewFullPath & Chr(34), vbNormalFocus
End Sub