vba 访问：Shell cmd 打开 MDB

Access: Shell cmd Open MDB

提问by Rick

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

我一直在使用以下命令通过 VBA 打开另一个 MDB Access 文件：

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.

strNewFullPath 是 MDB 文件的完整路径。使用 Access 2010 时工作正常，但不能在 Access 2003 上运行。如果我在 XP DOS 终端中运行该命令，它确实会运行。

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

我可以使用哪些其他命令可以在 Access 2003及Access Runtime 上运行？

回答by JimmyPena

Try using Windows Scripting Host Object Model (WSHOM):

尝试使用 Windows 脚本宿主对象模型 ( 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.

Windows 文件关联应允许在其本机应用程序中打开这两种类型的文件。

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:

Run 方法有两个可选参数。请注意，其中大部分内容是从 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 Falsefor asynchronous code. The Run method returns control to the calling program before completing. Default is False.

1. intWindowStyle (integer) 一个从 0 到 10 的数字：

   0 - 隐藏窗口并激活另一个窗口。
   1 - 激活并显示一个窗口。如果窗口被最小化或最大化，系统会将其恢复到原来的大小和位置。应用程序应在第一次显示窗口时指定此标志。
   2 - 激活窗口并将其显示为最小化窗口。
   3 - 激活窗口并将其显示为最大化窗口。
   4 - 以最近的大小和位置显示窗口。活动窗口保持活动状态。
   5 - 激活窗口并以其当前大小和位置显示。
   6 - 最小化指定的窗口并激活 Z 顺序中的下一个顶级窗口。
   7 - 将窗口显示为最小化窗口。活动窗口保持活动状态。
   8 - 以当前状态显示窗口。活动窗口保持活动状态。
   9 - 激活并显示窗口。如果窗口被最小化或最大化，系统会将其恢复到其原始大小和位置。应用程序应在恢复最小化窗口时指定此标志。
   10 - 根据启动应用程序的程序的状态设置显示状态。

   我不知道此参数的默认值。请注意，有些程序会忽略您设置的任何值（我无法告诉您是哪些值）。

2. bWaitOnReturn（布尔值）

   对于异步代码，设置为False。Run 方法在完成之前将控制权返回给调用程序。默认值为False。

回答by HansUp

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

如果要使用 Access VBA 在另一个 Access 应用程序实例中打开数据库，可以执行以下操作：

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.

在过程完成后，将 UserControl 设置为 True 会使新的应用程序实例保持打开状态。

If you want the new Access instance hidden, include:

如果您希望隐藏新的 Access 实例，请包括：

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.

我建议使用这种方法，因为它还为您提供了一种通过 objApp 对象变量自动化新应用程序实例的方法。但是，如果您对自动化新实例不感兴趣，那么这种方法可能只有在您无法使用任何其他方法时才有用。

回答by JohnFx

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:

您可以使用 Win32 API 查找与文件类型关联的 EXE 名称，并将其添加到您的 shell 命令中，如下所示：

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

回答by Albert D. Kallal

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

shell 命令的问题是 cmd 提示符并不总是支持使用文件扩展名来启动程序。事实上，你最好使用

Start "path to some file with .extension"

开始“一些带有 .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.

但是，您真正想要做的是启动 msacces.exe 并提供文件的路径名以将其打开。运行时安装尤其如此。

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.

所以上面使用了 msaccess.exe 的完整路径名。它已在 xp、vista、win7 等上进行了测试，并且一直对我有用。

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.

如果有多个版本的 Access 或使用运行时，您可能不想使用扩展来启动文件。因此，这可确保您使用的是当前运行的相同版本和相同的 .exe。因此，上面的代码会拉取您正在使用的当前 msaccess.exe 路径，而不是基于文件扩展名的路径。

回答by Matt Donnan

I use this function when working in Access 2003:

我在 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 : )

这在运行时模式下确实有效:)

回答by PressMac

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

这是我用来使其与 accdr 一起使用的轻微修订，其中需要使用运行时开关。

 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