Running Access Macro from Excel and getting RunMacro action was cancelled error.

I'm creating a subroutine in Excel to run a macro in Access.  I keep getting runtime error 2501:  "RunMacro action was cancelled."  Does anyone know what would cause this?  It does prompt me to enter the password for the SQL Server login, which I do.  Could that be the problem?  I'd love to be able to put the password in the code so that the user doesn't have to enter it.  Is that possible?  Thank you!

 Sub Call_Access()
      Dim A As Object
      Set A = CreateObject("Access.Application")
      A.Visible = False
      A.OpenCurrentDatabase ("C:\Radio Shack\RadioShack.accdb")
      A.DoCmd.RunMacro "UpdateAll"
   End Sub
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brad Sims, CCNACyberSecurity AnalystCommented:
Here's an example from Microsoft:

      Sub Run_Access_Macro()

         'Opens Microsoft Access and the file nwind.mdb
            Shell("c:\access\msaccess.exe c:\access\sampapps\nwind.mdb")
         'Initiates a DDE channel to Microsoft Access
            Chan = DDEInitiate("MSACCESS", "system")
         'Activates Microsoft Access
             Application.ActivateMicrosoftApp xlMicrosoftAccess
         'Runs the macro "Sample AutoExec" from the NWIND.MDB file
             Application.DDEExecute Chan, "Sample AutoExec"
         'Terminates the DDE channel
          Application.DDETerminate Chan

      End Sub

sarahellisAuthor Commented:
Thanks for the quick response.  I ran this and it doesn't recognize Shell.  This code is from Access 95.  I also found some code for Access 97.  I'm using Access 2007.
sarahellisAuthor Commented:
And that link is the basis for my code above.  
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Brad Sims, CCNACyberSecurity AnalystCommented: apologies.  I clicked on the wrong link.

I assume you have the references set in Excel for Access?  Your code looks the same as everything else I could find.  


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rory ArchibaldCommented:
What does the macro do? (e.g. where does SQL server come into this?)
sarahellisAuthor Commented:
I'll check the references library when I get to work.  The SQL Server is where the actual tables involved reside.  I have just set up links to them in Access using ODBC.  The macro runs 3 queries on those tables.
Rory ArchibaldCommented:
Have you tried saving the password when you set up the links?
sarahellisAuthor Commented:
No I haven't.  Not sure if I know how.  I'll take a look at it when I get in.  Thanks.  
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.