Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2002

    Unanswered: Running function from 1 database to another


    I have two databases. One of the databases i have a function, which runs lots of things.

    Database A has functions
    Database B has other information

    Is it possible to link the function in Database A with a button in database B, because there are lots of queries and tables which i do not want to bring in to Database B, I just bring in the linked tables from database A once the function has been run.

    I do not want to run 2 systems, just run the function from Database A in the background when i click on the button on Database B

    This might sound confusing.

    I would appreciate any ideas

    Thank you

  2. #2
    Join Date
    Dec 2004
    Madison, WI
    Not typically done since it's much easier to simply have the functions (in modules) in the same database. Otherwise you make it more complex. I can't say for sure if it's possible (I'm sure there's probably a way somehow but it'll probably be a lot of attempts to get the code right), but again, you make it much more difficult doing it this way.

    If your insistent on doing it this way (again, not recommended), you may want to look at opening MSAccess using parameters as this is the only way I can think of doing it. You would open the 2nd database (with the functions), using a parameter to call the right function. Again, I would discourage this route. It can be a pain trying to maintain 2 MSAccess files.

    But perhaps I'm misreading your intentions.
    Last edited by pkstormy; 11-24-09 at 14:35.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Mar 2009
    Provided Answers: 14
    If you do not need to get return parameters from the called function, this is rather simple:
        Dim appAccess As Access.Application
        ' Create instance of Access Application object.
        Set appAccess = CreateObject("Access.Application")   
        appAccess.OpenCurrentDatabase "<Full path to Db 2>", False
        ' Run Sub (or function) procedure.
        appAccess****n "<Name of the procedure to be run in Db 2>"
        Set appAccess = Nothing
    Note:appAccess****n stands for a p p A c c e s s . R u n (without spaces)
    Have a nice day!

  4. #4
    Join Date
    Feb 2004
    Chicago, IL
    Yes it can be done. I am assuming the functions in Database A are in modules. To access the functions in A from B you need to add a Reference in Database B to Database A. That will make all of the modules available. There are a couple of warnings:

    1. You will have problems if you have functions each database with the same name.
    2. You also may have issues developing in either database. When you open Database B it uses Database A like another user would. That means if you have B open and you try to edit a function or code in A it will warn you that you do not have exclusive access. Close B and then open A for editing.
    3. Debugging code can be a little more flaky. You can step through the code and everything works as expected, but occasionally (more often than normal) the database will blow up (close unexpectedly). Not a huge deal.
    4. Also, get familiar with CurrentProject versus CodeProject when you have a reference in place. Sorry if this gets a little confusing... If you open B with a reference to A and you run a module in A CurrentProject.Connection will reference your tables in B. To reference the tables in A use CodeProject. In other words, CodeProject uses the tables of the database that the code is running in. Since you are executing code in A you will be using the tables in A. CurrentProject uses the tables in B while running the code in A.

    Some other notes... you can use forms and reports from a referenced database as well. I have a database that is nothing but modules (string manipulation, common code, etc.) then I reference that database in all the databases I create. I have put a generic progress meter form in that database and I have a Class Module that I call to display it with custom labels. Even though the form itself is in another database, I can control it and it looks like it is in the current database.

    It took me a little while to understand how to use this so if you have a specific question I'll do what I can to answer it.

Posting Permissions

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