Call Macro from closed Data Base

[AI Man]

Greetings,

I have been searching the web and Access relentlessly trying to find a way to call an Access macro from a different Access data base.

Example - The form button gets clicked and the first macro executes. Then a second macro is selected from a closed data base so that the query in the second database can run. Alternatively open the second data base and call the macro.

In other words I need to use several Access databases and want the one with the form to control the others via the onclick command macro.

[MMcCarthy]

Greetings,

I have been searching the web and Access relentlessly trying to find a way to call an Access macro from a different Access data base.

Example - The form button gets clicked and the first macro executes. Then a second macro is selected from a closed data base so that the query in the second database can run. Alternatively open the second data base and call the macro.

In other words I need to use several Access databases and want the one with the form to control the others via the onclick command macro.

Fistly, you can't just call a macro from another database. The best you could hope for is to do some pretty complicated VBA code to open the other database and preform actions duplicating what is currently being done by the macro.

Mary

[ADezii]

Greetings,

I have been searching the web and Access relentlessly trying to find a way to call an Access macro from a different Access data base.

Example - The form button gets clicked and the first macro executes. Then a second macro is selected from a closed data base so that the query in the second database can run. Alternatively open the second data base and call the macro.

In other words I need to use several Access databases and want the one with the form to control the others via the onclick command macro.

What you are referring to is Automation Code utilizing Access as an Automation Server.

The following code will run Macro1 in the C:\Test\Test.mdb Database from the current Access Database. As far as I known, Automation is the only way to effectively control Objects in External Access Databases from a Current Access Database. Here are the steps which must be followed in strict sequence.

__1. In a Form's Declarations Section, declare a variable as Access.Application. This variable will soon be a legitimate reference to an external Access Application.

Dim objAccess As Access.Application

__2. Place the following code which will create a New Instance of Access and run the Macro in an appropriate Event Procedure.

Set objAccess = CreateObject("Access.Application")
 
objAccess.Visible = True
 
'Open Test.mdb exclusively
objAccess.OpenCurrentDatabase "C:\Test\Test.mdb", True
 
'Execute Macro1 which will open qryEmployees Maximized on screen
objAccess.DoCmd.RunMacro "Macro1"
objAccess.DoCmd.Maximize

__3. Destroy the previously created Instance of Access when you are finished with it.

Set objAccess = Nothing

[AI Man]

This is a listing of some of my coding. Hope it helps people.

'Create a second Access session
Public Sub OpenAccess()
Dim appAccess As New Access.Application
Set appAccess = Access.Application
appAccess.OpenCurrentDatabase "\\Foldername\Databasename.mdb"

appAccess.DoCmd.RunMacro "Macro Name", , ""
' Can't remember exact wording for the above line of code. I think whats shown is correct. Might be a straight DoComd.RunMacro

appAccess.Visible = True
End Sub



To run Excel Macro's
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add("\\Folder name\Report name.xls")
xlApp.Visible = False
xlApp.Application.Run "Name of macro"
xlApp.activeworkbook.Close
Set xlApp = Nothing
Set xlBook = Nothing



To email MULTIPULE documents from Access.
***Must save file first and then attach it to an email.***

Dim olapp As Object
Dim olns As Object
Dim olfolder As Object
Dim olitem As Object
Dim olattach As Object

Set olapp = CreateObject("Outlook.Application")
Set olns = olapp.GetNamespace("MAPI")
Set olfolder = olns.getdefaultfolder(6)
Set olitem = olapp.createitem(0)
Set olattach = olitem.attachments

olitem.To = "Fred Flintstone"
olitem.CC = "Mr. Slate"
olitem.Subject = "Quarry Productivity"
olitem.body = "Please find enclosed the weekly Productivity Reports" & Chr(13) & Chr(10)

olattach.Add "PathTo1stFile", 1
olattach.Add "PathTo2ndFile", 1
olattach.Add "pathTo3rdFile", 1

olitem.display
olitem.send

Set olitem = Nothing
Set rs = Nothing
Set db = Nothing
Set olfolder = Nothing
Set olns = Nothing
Set olapp = Nothing

[AI Man]

The last post has some minor items to clarify. Here is the same data with more clarification. Specifically clarifing how to use the path ways.

'Create a second Access session
Public Sub OpenAccess()
Dim appAccess As New Access.Application
Set appAccess = Access.Application
appAccess.OpenCurrentDatabase "\\Path to folder holding Database\Database name.mdb"

appAccess.DoCmd.RunMacro "Macro Name", , ""
' Can't remember exact wording for the above line of code. I think whats shown is correct. Might be a straight DoComd.RunMacro

appAccess.Visible = True
End Sub



To run Excel Macro's
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add("\\Path to folder holding workbook\workbook name.xls")
xlApp.Visible = False
xlApp.Application.Run "Name of macro"
xlApp.activeworkbook.Save
xlApp.activeworkbook.Close
Set xlApp = Nothing
Set xlBook = Nothing



To email MULTIPULE documents from Access.
***Must save file first.***

Dim olapp As Object
Dim olns As Object
Dim olfolder As Object
Dim olitem As Object
Dim olattach As Object

Set olapp = CreateObject("Outlook.Application")
Set olns = olapp.GetNamespace("MAPI")
Set olfolder = olns.getdefaultfolder(6)
Set olitem = olapp.createitem(0)
Set olattach = olitem.attachments

olitem.To = "Fred Flintstone"
olitem.CC = "Mr. Slate"
olitem.Subject = "Quarry Productivity"
olitem.body = "Please find enclosed the weekly Productivity Reports" & Chr(13) & Chr(10)

olattach.Add "PathTo1stFile", 1
olattach.Add "PathTo2ndFile", 1
olattach.Add "pathTo3rdFile", 1

olitem.display
olitem.send

Set olitem = Nothing
Set rs = Nothing
Set db = Nothing
Set olfolder = Nothing
Set olns = Nothing
Set olapp = Nothing