Run Access Macro from Excel

Posted on 2011-10-05
Last Modified: 2012-05-12
I am trying to run a Access Macro from Excel using the following. I'm getting a run-time error 7866.
"Microsoft Office Access can't open database because its missing, or opened exclusively by another user, or is not an ADP file.

Private Sub CommandButton4_Click()
  ' Initialize string to database path.
    Const strConPathToSamples = "C:\Users\szyynr\Documents\Allocation_Constraint_Database_.accdb"

    strDB = strConPathToSamples & "test.mdb"
    ' Create new instance of Microsoft Access.
    Set appAccess = _
    ' Open database in Microsoft Access window.
    appAccess.OpenCurrentDatabase strConPathToSamples
    ' Run Access macro.
    appAccess.DoCmd.RunMacro "mac_Download GMNA Final Constraint Report", 1

End Sub

Open in new window

Question by:mato01
    LVL 10

    Accepted Solution

    What line exactly does it crash on?

    When you run the macro do you have the Access Database open in the background??
    At the end of your macro make sure you close Access and set the variable to nothing

    Set appAccess = Nothing

    Open in new window


    Author Comment

    No, I do not have Access open in the background.  And that is at the bottom.

    objAccess.OpenCurrentDatabase (strDbName) ----- This is where the error is occuring.
    'to run a macro
    objAccess.DoCmd.RunMacro strMacro

    Set objAccess = Nothing
    End Sub

    Author Comment

    Hi Experts,

    Need further asssitance to run a access macro from excel.

    Author Closing Comment

    I will accept this answer because it was the relevant to the solution.  Thanks

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now