How can I run a query from another database?

[rb9999]

I have a HUGE MDB file of various queries that I use to produce various reports. My queries are getting all jumbled together and stuff.

I am in the process of needing another rather complicated query written that will utilize several queries and tables etc.

I would like to write it in its own MDB file and call it from my other database. I don't want to link stuff, I actually want to start Access, run the query/reports and exit.

I cannot hard code the path to MSACCESS.EXE because different people will be running it and I cannot guarantee that all users will have the exe in the same directory.

Any help appreciated.

 

[HomeALone]

If you don't like linking, your remaining option is importing.

You can code and use the TransferDatabase method to carry out the TransferDatabase action in the procedure to import all the tables and queries you need to a new database everytime you open the database.

Try it and see whether this method can meet your need?

 

[FancyPrairie]

Is this what you are trying to do? You have several databases, and from each of these databases you want to run a "centralized" report. If so, here's how you could do it.

1. Create a new "library" database and give it a file extension of .mda (i.e. MyLibrary.mda) (rather than .mdb). (This is optional)
2. Open a new module and add the following function:
Function Lib_OpenReport(strReport as string...)
DoCmd.OpenReport strReport...
End Function

Note that the arguments for Lib_OpenReport should be the same as the OpenReport method.

3. Link your tables and create your query and create your report.

4. Close this new database
5. Open one of your other databases and set a reference to your "library" database.
6. Now, when you want to open the report that exists in your "library" database, simply call the function Lib_OpenReport and pass it the name of the report.

 

[rb9999]

That is kind of what I want to do. I don't want to clutter up my existing database (which has about 50 or 60 queries and I don't need 10 more in there, it is getting difficult to tell what goes with what). I was thinking that I could just create my new report and database and have it run using an autoexec macro. Then I could just have it exit automatically.

All I would need to do is open it from my other database, it will automatically run, it will automatically dump its reports, and automatically exit.

This way I can maintain it in a more orderly fashion (the tables and queries are better organized).

How would I call it from code without necessarily knowing where MSACCESS.EXE is located? What would be the command line?

 

[FancyPrairie]

You don't need to open another database, etc. Just create the "library" database and save your query and report in the library database and store the Lib_OpenReport function in the library database. Then, in all of your other databases, goto Tools|References and browse to your library database.

Now, if you want the report to automatically print when each of the other databases are open, simply call the function Lib_OpenReport. You can have autoexec call the function or have autoexec call a startup function that calls Lib_OpenReport and then opens the appropriate form for the user.

 

[rb9999]

There is no REFERENCES selection in my TOOLS menu. I am using Access XP.

 

[Veep]

You need to go into a code module to access the references that FancyPrairie is talking about. Open any form and click on the code button. Then go to TOOLS>REFERENCES> etc.