CODEWITHSUNDEEP
excelms-accessvba
ASH
ASH

Reputation: 20302

Trying to Run Access Macro from

I am trying to run an Access Macro from Excel. I set a reference to Microsoft Office 14.0 Access database engine Object Library. Now, I'm trying to run a small script like this.

Sub RunAccessMacro()
    Dim strDatabasePath As String
    Dim PathToDB As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    PathOfDatabase = ThisWorkbook.Worksheets("Updates").Range("PathToAccess")

    Set db = DAO.DBEngine.OpenDatabase(PathOfDatabase)
    Set qry = db.Execute("Macro_Run_Key")

    qry.Close
    db.Close

    MsgBox "Done!  All processes complete!!"

End Sub

The problem is, the db.Execute won't execute the Macro. I don't even see anything like RunMacro, or whatever it's called.

enter image description here

There must be a way to do this, right.

Upvotes: 0

Views: 2767

Answers (3)

AdzzzUK
AdzzzUK

Reputation: 205

If the macro is an Access Macro, you can actually trigger the macro with a single command rather than having to go around the houses. The /X command line switch will help - check this link: How can I schedule a Macro to run automatically in Access 2007

Upvotes: 0

Scott Holtzman
Scott Holtzman

Reputation: 27239

Setting a reference to the Access Object Library is useless unless you actually use it :) Seriously, the code written above uses DAO which is a different animal than calling Access directly through the Object Libary. DAO is strictly a database engine (like ADO) and does not know about macros and modules and such as defined in Office Apps.

Here is the code to use when using early binding:

Sub RunAccessMacro()

    Dim PathOfDatabase As String
    PathOfDatabase = ThisWorkbook.Worksheets("Updates").Range("PathToAccess")

    Dim accApp As Access.Application
    Set accApp = New Access.Application

    With accApp

        .OpenCurrentDatabase PathOfDatabase
        .DoCmd.RunMacro "Macro_Run_Key"
        .Quit

    End With

    MsgBox "Done!  All processes complete!!"

End Sub

Upvotes: 1

Erik A
Erik A

Reputation: 32632

The database engine only does database things (anything with tables and queries). If you want more than that, you will have to use the Access application through VBA:

Sub RunAccessMacro()
    Dim strDatabasePath As String
    Dim PathToDB As String
    Dim accApp As Access.Application
    Set accApp = New Access.Application



    PathOfDatabase = ThisWorkbook.Worksheets("Updates").Range("PathToAccess")
    accApp.OpenCurrentDatabase PathOfDatabase
    accApp.DoCmd.RunMacro "Macro_Run_Key"
    accApp.Quit
    Set accApp = Nothing


    MsgBox "Done!  All processes complete!!"

End Sub

Also, you will need to add a reference to the Microsoft Access Object Library, or you can adapt this code to use late bindings.

Upvotes: 2

Related Questions