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
sarahellisAsked:
Who is Participating?
 
Brad Sims, CCNANetwork AdministratorCommented:
Nice...my 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.  


AccessReference.bmp
0
 
Brad Sims, CCNANetwork AdministratorCommented:
Here's an example from Microsoft:
http://support.microsoft.com/kb/131921


      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





0
 
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. http://support.microsoft.com/kb/177760.  I'm using Access 2007.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sarahellisAuthor Commented:
And that link is the basis for my code above.  
0
 
Rory ArchibaldCommented:
What does the macro do? (e.g. where does SQL server come into this?)
0
 
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.
Thanks!
0
 
Rory ArchibaldCommented:
Have you tried saving the password when you set up the links?
0
 
sarahellisAuthor Commented:
No I haven't.  Not sure if I know how.  I'll take a look at it when I get in.  Thanks.  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.