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

Question

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

I'm using Excel and Access 2007. Here is my code in 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.

I get:

Runtime error '2485':
Microsoft Access Office can't find the object '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).

Answer 1

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

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

Answer 2

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

Answer 3

How about this:

appAccess.Modules.Application.Run "macro_name"

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

Answer 4

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

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.

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

Answer 5

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

Answer 6

This doesn't specifically address the "RunQueries" version, but this works in 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).

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.

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.

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."

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