Trying to Run Access Macro from

Question

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.

There must be a way to do this, right.

Erik A · Accepted Answer · 2017-08-02 19:29:01Z

2

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.

answered Aug 2, 2017 at 19:29

Erik A

32.8k13 gold badges48 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ASH Over a year ago

Thanks Scott and thanks Erik. I tried both solutions, and with each one I keep getting this message: Microsoft is waiting for another application to complete an OLE action The message pops up after about 4-5 minutes. The following line of code throws things off: .DoCmd.RunMacro Any thoughts? I tried a couple other ideas before posting my question. The result is always the same.

Erik A Over a year ago

You're probably having a problem with the macro requiring user interaction. Did you open the macro with Set warnings to false? If you replace your macro with a simple Set Warnings False, Run SQL CREATE TABLE A, does it work?

Scott Holtzman · Accepted Answer · 2017-08-02 19:30:07Z

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

AdzzzUK · Accepted Answer · 2017-08-03 09:39:25Z

0

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

answered Aug 3, 2017 at 9:39

AdzzzUK

2051 silver badge5 bronze badges

1 Comment

Erik A Over a year ago

Note that triggering it that way leads to undesirable behaviour, such as Access actually showing up and not exiting after finishing the macro, not being able to detect errors and more. While you may call it "going around the houses", it's actually often more work down the line.

Collectives™ on Stack Overflow

Trying to Run Access Macro from

3 Answers 3

2 Comments

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Linked

Related