Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Call function in another database? (2002)

    Hey all,

    I know I've seen this here before, but I've searched and searched and can't find it anywhere. I think my brain is mush.

    I have a bunch of databases that download data periodically. I want to control them all through a single database (I don't need to open them or anything, just have them run their code and do their thing for the day/month, depending on the db). So what I'd like to do is run functions from the control database that exist in the separate databases with the data in them. I haven't gotten very far, because I'm not sure how you refer to the code?

    Function runExternalCode()
    Dim db As Database
    Dim strDB As String
    Dim strMacro As String
    Dim strFunction As String

    strDB = "c:MySmallerDb.mdb"
    strMacro = "mcrTestMe"
    strFunction = "TestMe()"

    Set db = DBEngine.Workspaces(0).OpenDatabase(strDB)
    ???db.DoCmd.RunMacro strMacro???

    End Function

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Call function in another database? (2002)

    If you have code that is stored in an external database, but you want to run it in the current database (affecting the current database), you can set a reference to the external database (in Tools | References...).

    If you want to run the code in the external database itself, affecting the external database, you will have to open it in Access. It's not enough to use the DAO OpenDatabase method, this opens the database at a low level that doesn't "understand" macros and VBA code. You can use Automation to open a second instance of Access, open the database in this instance, and run code in it.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Call function in another database? (2002)

    Thanks, Hans.

    For some reason, I can't refer to it in references...okay, I'll stop being lazy and open my database [img]/forums/images/smilies/smile.gif[/img]

    Edit: I can't do it in references, because I want all the action to be done in the external databases. I just want to be able to control them from one command center.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Call function in another database? (2002)

    You'll have to use Automation - see WendellB

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Call function in another database? (2002)

    Okay, automation is being used. I won't be stubborn anymore.

    Now, if I want to run code...what do I use?

    If I want to run a macro, apparently I can do DoCmd.RunMacro ("MyMacro")

    Do I need a macro for ever function I want to run this way? Or can I call the actual procedures?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Call function in another database? (2002)

    You can run code like this:

    Dim appAccess As New Access.Application
    appAccess.OpenCurrentDatabase "OtherDatabase.mdb"
    appAccess.Run "MySub"
    ...
    appAccess.Quit
    Set appAccess = Nothing

    MySub is the name of a VBA procedure or function in OtherDatabase.mdb.

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Call function in another database? (2002)

    Hah! So easy, but so hard to find.

    Perfect, that's exactly it!!!

    I think I wanna be just like Hans when I grow up [img]/forums/images/smilies/smile.gif[/img]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •