The example below demonstrates how we can run a Macro Access from another application or database using theOLE Automation.
We perform the following steps:
- let's create two Access databases calling them TestDB.mdb and TestDB2.mdb
- in the first database TestDB.mdb we create a macro calling it TestMsg with the following action: "Test Macro" Message Window
- in the second database TestDB2.mdb we create another macro calling it TestMsg with the following action: Message Window: "Test Macro2"
- in the application you will use, choose from the references Microsoft Access Object Library (from version 8.0 onwards)
Let's create the following Sub:
Sub RunMacroX()
Dim objACC As New Access.Application
Set objACC = GetObject("C:TestDB.mdb") 'open first database
objACC.DoCmd.RunMacro ("TestMsg") 'run macro
objACC.Quit
Set objACC = GetObject("C:TestDB2.mdb") 'open second database
objACC.DoCmd.RunMacro ("TestMsg") 'run macro
objACC.Quit
Set objACC = Nothing
End Sub
In case you get an Error like this: "The RunMacro action was canceled. Error 2501"Or"The RunMacro action was canceled".
The error is due to the existence of an Exit Macro or Stop Macro declaration. If you notice that, even in the presence of the error, the macro has completed its activity, you can very well use theOn Error Resume Next at the beginning of Sub.
Tested on Office 2003 and Office 2007.
