Calling function in one MDB from another MDB

Question

We have developed a consolidation function that will be used by other processes and want to position the function in its own MDB (call it "remote") so that it can be referenced and called from "caller.mdb" when its needed. The function is designed to return an array and works great when executed called directly from within "remote." However, with "remote" properly referenced in the "caller" VBA project, when "caller" makes the call the function returns errors. We get a variety of errors such as

3078: Jet cannot find the input table or query

QUESTION. Within "remote", how does one properly set references to the db and its local objects (e.g. one table and several queries including INSERT and UPDATE queries)? CurrentDB is apparently not the answer; we have also experimented with the AccessObject and CodeData objects. "Remote" and "caller" currently reside on the same drive, so that wouldn't seem to be the problem.

Answer 1

Instead of CurrentDb you could use with CodeDb wich points to the mdb currently executing the code.

Set db = CodeDb

Answer 2

Marcand gave you the answer to your immediate question. There are other problems and irritations when it comes to using add-ins or referenced Access databases. See my Add-in Tips, Hints and Gotchas page.

Answer 3

The way Access itself does this (with all the wizards, which are all programmed in Access), is to use Application.Run. It does mean the code you're calling has to be a function, though it doesn't matter what it returns. Application.Run requires no references, just a path:

  Application.Run("MyCodeDatabase.MyFunction()")

Obviously, if the code database is not in the path that Access uses (which includes its own app folders (including the app-specific folders in the user's profile) and the folder where your main application front end is stored), you'll need to specify the full path.

Application.Run() is a function that returns a value, but it is typed as variant. This may or may not work with your array. It's not clear from the object browser whether or not the arguments are passed ByVal or ByRef, but if they are ByRef (which is what I'd expect), you might just pass the array in and let the function work on it and then use it after the code in the remote database has completed.

On the other hand, the arguments are probably variants, so there's not much difference between that approach and just using the structure returned by Application.Run().