Call function in another database? (2002)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

Thanks, Hans.

For some reason, I can’t refer to it in references…okay, I’ll stop being lazy and open my database

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.

Reply | Quote

You’ll have to use Automation – see WendellB

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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

Reply | Quote