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

Try using Windows Scripting Host Object Model (WSHOM):

Sub RunFile(filename As String)
Dim oShell As Object
  Set oShell = GetShell
  If Not oShell Is Nothing Then
    oShell.Run filename
  End If
End Sub
Function GetShell() As Object   
  On Error Resume Next     
  Set GetShell = CreateObject("WScript.Shell")  
End Function 

The Windows file association should allow both types of files to open in their native application.

Sample Usage:

RunFile strNewFullPath

Optional Arguments:

There are two optional arguments for the Run method. Please note that much of this is copied from MSDN:

1. intWindowStyle (integer) A number from 0 to 10:

   0 - Hides the window and activates another window.
   1 - Activates and displays a window. If the window is minimized or maximized, the system restores it to its original size and position. An application should specify this flag when displaying the window for the first time.
   2 - Activates the window and displays it as a minimized window.
   3 - Activates the window and displays it as a maximized window.
   4 - Displays a window in its most recent size and position. The active window remains active.
   5 - Activates the window and displays it in its current size and position.
   6 - Minimizes the specified window and activates the next top-level window in the Z order.
   7 - Displays the window as a minimized window. The active window remains active.
   8 - Displays the window in its current state. The active window remains active.
   9 - Activates and displays the window. If the window is minimized or maximized, the system restores it to its original size and position. An application should specify this flag when restoring a minimized window.
   10 - Sets the show-state based on the state of the program that started the application.

   I am not aware of the default value for this parameter. Note that some programs simply ignore whatever value you set (I couldn't tell you which ones).

2. bWaitOnReturn (boolean)

   Set to False for asynchronous code. The Run method returns control to the calling program before completing. Default is False.