469,352 Members | 2,150 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,352 developers. It's quick & easy.

Call Macro from closed Data Base

5
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.
Apr 4 '07 #1
4 12754
MMcCarthy
14,534 Expert Mod 8TB
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
Apr 4 '07 #2
ADezii
8,800 Expert 8TB
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.
Expand|Select|Wrap|Line Numbers
  1. 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.
Expand|Select|Wrap|Line Numbers
  1. Set objAccess = CreateObject("Access.Application")
  2.  
  3. objAccess.Visible = True
  4.  
  5. 'Open Test.mdb exclusively
  6. objAccess.OpenCurrentDatabase "C:\Test\Test.mdb", True
  7.  
  8. 'Execute Macro1 which will open qryEmployees Maximized on screen
  9. objAccess.DoCmd.RunMacro "Macro1"
  10. objAccess.DoCmd.Maximize
__3. Destroy the previously created Instance of Access when you are finished with it.
Expand|Select|Wrap|Line Numbers
  1. Set objAccess = Nothing
Apr 4 '07 #3
AI Man
5
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
Apr 9 '07 #4
AI Man
5
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
Apr 9 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by MHenry | last post: by
27 posts views Thread by Ken Human | last post: by
reply views Thread by Mythran | last post: by
28 posts views Thread by Jack Morgan | last post: by
5 posts views Thread by Frederick Gotham | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.