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?

MDB is the file extension for Access 2003, so how isn't it working? You mention it runs in a XP DOS terminal, so what operating system are you using when it **isn't** working? — JimmyPena, Jan 26 '12 at 21:11
I'm curious why you open the MDB in a hidden window. Does it run an autoexec macro and then close itself? Or something else? — HansUp, Jan 26 '12 at 22:18
@JP.I have a Windows 7 PC with Office 2010 and a Windows XP PC with Office 2003. The DOS command that works in XP and Win7 is `cmd /c "\\10.1.1.44\Share\myApp.mdb"`. using the shell command in VBA, it just opens the DOS propmt. — Rick, Jan 26 '12 at 23:34
@HansUp Since Access is known to corrupt files on multiple user access sometimes; I devised a sort of a loader app, which checks itself and its location and if all is OK then it creates a copy of the master MDB (for the specific user) and runs that copy and closes the Loader. I have the tables linked to SQL Server. — Rick, Jan 26 '12 at 23:38

JimmyPena · Answer 1 · 2012-01-27T00:30:30.960

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:

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).
bWaitOnReturn (boolean)

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

score 5 · Answer 2 · answered Jan 26 '12 at 21:56

If you want want to use Access VBA to open a database in another Access application instance, you can do this:

Dim objApp As Access.Application
Set objApp = New Access.Application
objApp.UserControl = True
objApp.OpenCurrentDatabase "C:\Access\sample.mdb"
Set objApp = Nothing

Setting UserControl to True leaves the new application instance open after the procedure finishes.

If you want the new Access instance hidden, include:

objApp.Visible = False

I'm suggesting this approach because it also gives you a way to automate the new application instance through the objApp object variable. But, if you're not interested in automating the new instance, this approach will probably only be useful if you can't make any other method work.

I originally had this code, but realized that "Access.Application" object isn't compatible with the Access Runtime. I edited my original post to include this. Thanks, — Rick, Jan 26 '12 at 22:54

JohnFx · Accepted Answer · 2012-01-27T15:22:35.000

3

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

edited Jan 27 '12 at 15:22

answered Jan 26 '12 at 23:04

JohnFx

34,542
18
104
162

Tested on both 2003 and 2010. Works great but had to change vbHide to vbNormalFocus, or else the Access Window wouldn't show except in the Task Manager.Would this also work in the Access Runtime? – Rick Jan 27 '12 at 01:02
It should behave exactly like it does in the corresponding version of Access in the runtime. I had vbHide just because that is what you used in the question. – JohnFx Jan 27 '12 at 01:46

score 1 · Answer 4 · answered Jan 27 '12 at 00:28

The problem with your shell command is the cmd prompt don't always support using the file extension to start a program. In fact, you better off to use

Start "path to some file with .extension"

The above is quite much the same as clicking.

However, what you really want to do is launch the msacces.exe and SUPPLY the path name to the file for it to open. This is especially the case with a runtime install.

So your code should look like this:

  Sub testjump()

     ' jumps to a mde file called "upgrade.mde"
     ' it exists in the same directly as the currently running program

     Dim strShellProg        As String
     Dim strCurrentDir       As String
     Const q                 As String = """"

     strCurrentDir = CurrentProject.path & "\"

    ' path to msaccess is required here
     strShellProg = q & SysCmd(acSysCmdAccessDir) & "msaccess.exe" & q

     strShellProg = strShellProg & " " & q & strCurrentDir & "RidesUpGrade.mdE" & q

     If Shell(strShellProg, vbNormalFocus) > 0 Then
        ' code here for shell ok
        Application.Quit
     Else
        ' code here for shell not ok
        MsgBox "Un able to run Rides upgrade", vbCritical, AppName
        Application.Quit
     End If

  End Sub

So the above uses the full path name to msaccess.exe. It been tested on xp, vista, win7 etc, and it always worked for me.

And in the case of more than one version of Access, or that of using a runtime, you may not want to use the extension to launch the file. So this ensures that you are using the SAME version and same .exe that you are currently running. So the above code pulls the current msaccess.exe path you are using, not one based on file extension.

score 1 · Answer 5 · answered Jan 27 '12 at 09:42

1

I use this function when working in Access 2003:

Public Function RunExternalMDB(MDBName As String, WG As String, UsrNm As String, Pwd As String)

        Shell "MsAccess.exe " & """" & MDBName & """" & " /wrkgrp " & """" & WG & """" & " /user " & UsrNm & " /pwd " & Pwd

End Function

This does work in Runtime mode : )

answered Jan 27 '12 at 09:42

Matt Donnan

4,933
3
20
32

If he's not using Access user level security, he would still need to supply the path to the workgroup information file and admin for /user. This also requires the exe's folder to be included in the user's PATH environment variable. – HansUp Jan 27 '12 at 14:45
@HansUp This is just something I use to create an Ms Access command line, it will still work if the references to the WorkGroup are removed, and ultimately the user could store the MS Access path in a global variable instead if they wish – Matt Donnan Jan 27 '12 at 16:01

score 0 · Answer 6 · edited Feb 27 '20 at 08:48

Here is a slight revision I used to make it work with accdr, where it is required that there be a runtime switch used.

 strShellProg = q & SysCmd(acSysCmdAccessDir) & "msaccess.exe" & q & " /runtime"

 strShellProg = strShellProg & " " & q & strCurrentDir & "spfe.accdr" & q

 If Shell(strShellProg, vbNormalFocus) > 0 Then
    DoCmd.Hourglass False
    ' DoCmd.Quit
    Application.Quit
 Else
    ' code here for shell not ok
    MsgBox "Unable to run upgrade", vbCritical, AppName
    DoCmd.Hourglass False
    Application.Quit
 End If

Access: Shell cmd Open MDB

6 Answers6

Sample Usage:

Optional Arguments:

Linked