vba 打开 Access 数据库并从 Excel 运行其宏之一

Open an Access database and run one of its Macros from Excel

提问by oob

From Excel, I need to open an Access database and run one of the database's macros.

在 Excel 中，我需要打开一个 Access 数据库并运行该数据库的宏之一。

I'm using Excel and Access 2007. Here is my code in Excel:

我正在使用 Excel 和 Access 2007。这是我在 Excel 中的代码：

Sub accessMacro()

   Dim appAccess As New Access.Application

   Set appAccess = Access.Application

   appAccess.OpenCurrentDatabase "C:\blah.mdb"

   appAccess.Visible = True

   appAccess.DoCmd.RunMacro "RunQueries.RunQueries"
   appAccess.CloseCurrentDatabase

End Sub

In the Access database, there is a procedure named RunQueries in a module named RunQueries.

在 Access 数据库中，名为 RunQueries 的模块中有一个名为 RunQueries 的过程。

I get:

我得到：

Runtime error '2485':
Microsoft Access Office can't find the object 'RunQueries.'

运行时错误“2485”：
Microsoft Access Office 找不到对象“RunQueries”。

I also tried

我也试过

appAccess.DoCmd.RunMacro "RunQueries" 

and I get the same errors message.

我收到相同的错误消息。

I argued against it, and I have to do it this way (meaning, I have to use Excel as a frontend to open several Access dbs and run their macros).

我反对它，我必须这样做（意思是，我必须使用 Excel 作为前端来打开多个 Access dbs 并运行它们的宏）。

回答by Patrick Honorez

What about this syntax ?
appAccess.run "RunQueries.RunQueries"

这个语法怎么样？
appAccess.run "RunQueries.RunQueries"

By the way, I always avoid naming a module like a procedure. This is looking for trouble.

顺便说一句，我总是避免将模块命名为过程。这是在找麻烦。

回答by Rami

Sub accessMacro()

   Dim appAccess As Access.Application

   Set appAccess = New Access.Application

   appAccess.OpenCurrentDatabase "C:\blah.mdb"

   appAccess.Visible = True

   appAccess.DoCmd.RunMacro "Macro Name"  '<-- As it appears in the Macro Group in the Access Interface.   
   appAccess.CloseCurrentDatabase

End Sub

回答by Damian Fennelly

How about this:

这个怎么样：

appAccess.Modules.Application.Run "macro_name"

The macro name doesn't need the Module name to function for me.

宏名称不需要模块名称即可对我来说有效。

回答by Manibalan

Try this:

尝试这个：

Sub accessMacro()

   Dim appAccess

   Set appAccess = CreateObject("Access.Application")

   appAccess.OpenCurrentDatabase "C:\blah.mdb"

   appAccess.Visible = True

   appAccess.DoCmd.RunMacro "RunQueries.RunQueries"

   appAccess.CloseCurrentDatabase
End Sub

回答by Reverus

The msdn site didn't shed too much light, but I have a feeling that their disclaimer applies here. Here's what they mentioned:

msdn 站点没有透露太多信息，但我觉得他们的免责声明适用于此处。这是他们提到的：

If you run Visual Basic code containing the RunMacro method in a library database, Microsoft Access looks for the macro with this name in the library database and doesn't look for it in the current database.

如果在库数据库中运行包含 RunMacro 方法的 Visual Basic 代码，Microsoft Access 将在库数据库中查找具有此名称的宏，而不会在当前数据库中查找它。

Of course they don't mention how exactly to remedy this issue! But after reviewing the answers above I think it would be helpful to post a full answer:

当然，他们没有提到如何确切地解决这个问题！但是在查看了上面的答案之后，我认为发布完整的答案会有所帮助：

Sub accessMacro()

   Dim appAccess As New Access.Application

   Set appAccess = Access.Application

   appAccess.OpenCurrentDatabase "C:\blah.mdb"

   appAccess.Visible = True

   appAccess.Run "RunQueries"
   appAccess.CloseCurrentDatabase

End Sub

This worked when I ran it. Good luck! :D -Reverus

当我运行它时，这有效。祝你好运！:D -Reverus

回答by RIck_R

This doesn't specifically address the "RunQueries" version, but this works in Access 2019.

这并没有专门针对“RunQueries”版本，但这适用于 Access 2019。

Note that the Application object has to be created and initialized a bit differently than in the previous examples (and this ends with Set [object] = Nothing).

请注意，Application 对象的创建和初始化与前面的示例略有不同（以 Set [object] = Nothing 结尾）。

Although not mentioned, TXE_DEN.accdb has a tie-in to a separate library database MLO_Library.accdb and a lot of the subroutines in DEN access routines in Library. The macro in the example is in the TXE_DEN database, not the Library. If it were in the Library, I don't know whether it could be accessed through the TXE_DEN database as shown.

尽管没有提及，TXE_DEN.accdb 与单独的库数据库 MLO_Library.accdb 以及 Library 中 DEN 访问例程中的许多子例程相关联。示例中的宏位于 TXE_DEN 数据库中，而不是库中。如果它在库中，我不知道它是否可以通过 TXE_DEN 数据库访问，如图所示。

Also, in the Navigation Pane the example macro shows up in "Unrelated Objects". So in other cases--e.g., RunQueries--it might be necessary to include a module name in the identifier.

此外，在导航窗格中，示例宏显示在“无关对象”中。因此，在其他情况下——例如，RunQueries——可能需要在标识符中包含模块名称。

And just to avoid confusion--this macro does not do anything to anything in Excel. It's just "Well, I'm running THIS EXCEL stuff and I also need to run THAT ACCESS stuff, so I'll digress to Access and run that and then continue with my EXCEL stuff."

并且只是为了避免混淆——这个宏不会对 Excel 中的任何内容执行任何操作。它只是“好吧，我正在运行这个 EXCEL 的东西，我还需要运行那个 ACCESS 的东西，所以我将离题到 Access 并运行它，然后继续我的 EXCEL 东西。”

Sub Run_Access_Macro()

    Dim appAccess As Object
    Set appAccess = CreateObject("Access.Application")

    Dim AccessDB As String
        AccessDB = "F:\PATH WITH SPACES\TDN\TXE_DEN.accdb"

    ' format: module.macro
    Dim AccessMacro As String
        AccessMacro = "0 - Import TDN"

    appAccess.OpenCurrentDatabase AccessDB

    appAccess.Visible = True

    appAccess.DoCmd.RunMacro AccessMacro
    appAccess.CloseCurrentDatabase

    Set appAccess = Nothing

End Sub