Run a macro in a seperate database

[justice8965]

Hey all

I'm trying to run a macro in a different database than the current one when the user clicks a button. I'd like to do this without the other database actually opening (ie the user doesn't see the database opening)

So far I have this:

Dim ms0 As New Access.Application

ms0.OpenCurrentDatabase ("C:\Documents and Settings\Matt.EMPIRE\My Documents\Billing.accdb")


ms0.DoCmd.RunMacro ("enrollmentProfile")

However, this opens the other database. Is there any way to do it without opening it?

 

[jdraw]

Hey all

I'm trying to run a macro in a different database than the current one when the user clicks a button. I'd like to do this without the other database actually opening (ie the user doesn't see the database opening)

So far I have this:

Dim ms0 As New Access.Application

ms0.OpenCurrentDatabase ("C:\Documents and Settings\Matt.EMPIRE\My Documents\Billing.accdb")


ms0.DoCmd.RunMacro ("enrollmentProfile")

However, this opens the other database. Is there any way to do it without opening it?

I just set up a test (Access 2003 -- I don't have 2007), one form with a button and some vba for the OnClick event. I opened a second database and printed the Table names. It doesn't appear to open the second database.
It wasn't a macro per se, but you might use this to test what you're tryinig to do.

Form has one button called Command0. Here is code on the OnClick event

 Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

   testdb
Exit_Command0_Click:
    Exit Sub

Err_Command0_Click:
    
    MsgBox Err.Description
    Resume Exit_Command0_Click
    
End Sub

'---------------------------------------------------------------------------------------
' Procedure : testdb
' Author    : Jack
' Created   : 12/8/2009
' Purpose   : Test opening second A2003 database.
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'------------------------------------------------------------------------------
'
Sub testdb()

Dim dbsCurrent As DAO.Database
Dim dbsSecond As DAO.Database
Dim tbl As DAO.TableDef
   On Error GoTo testdb_Error

Set dbsCurrent = CurrentDb
Set dbsSecond = DBEngine.Workspaces(0).OpenDatabase("d:\a2k\db1.mdb")
Debug.Print dbsCurrent.Name
Debug.Print dbsSecond.Name

For Each tbl In dbsSecond.TableDefs
Debug.Print "  " & tbl.Name
Next
Debug.Print Now()

   On Error GoTo 0
   Exit Sub

testdb_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure testdb of Module Module5"

End Sub

Here is output

D:\db1New2009.mdb
d:\a2k\db1.mdb
A_MrExcelTable
A2Kmdbs
ancestor
authors
Books
Booksx
ComparisonOutput
Contents
CopyOfDoctor
12/8/2009 10:21:00 AM

Hope it's useful to you.

 

[DCrake]

Alot will depend on what the macro is doing in the other mdb. You could be able to perform the same thing from your current mdb.

David

 

[justice8965]

jdraw,

Thanks for the help, but do you know how to call a macro from that? The DoCmd.RunMacro method isn't available for that object

 

[LPurvis]

No two entirely different object models there.
DAO's database object doesn't offer any application functionality - i.e. you can no more execute a macro than you could use it to run code in the called database.
Macros are application objects.
They're exposed in DAO as Scripts in the Containers collection - but you're not able to even determine the names or actions of each macro. (You'd have to go delving into the binary data of the system tables - who knows where it would be!).

The only way I know to determine the contents of a macro is to export it - but that still involves an Access application object... not a DAO database.


Since you ask "Is there any way to do it without opening it?"
Well, technically yes - if you were to include a wrapping VBA procedure which calls this macro and add the containing database as a Reference to your running database then you could execute that wrapping function, in turn calling the macro.
(I dare say similarly using an AddIn - though with more effort and not then application specific).

Not particularly flexible (you can add references at runtime only in an open MDB/ACCDB) and surely overkill.


As mentioned, the most sense would be to simply duplicate the actions it executes.
You'd need to know in advance what they were.
(What are they? ;-)

Cheers.

 

[justice8965]

Well the actions themselves involve a lot of code, tables and queries from the other database. I at first tried to copy all the required stuff over to the other database, but there was just too many little references here and there. It got quite messy so I abandoned that idea.

The way I have it now works. It still opens the other database, but only for like 5 seconds, then it closes it again, so its not very obtrusive. Its acceptable enough for my purposes