Run query in another database

[RoBo]

Could anyone help me with the following problem:

I created a form in a database. This form is used to specify criteria for a
number of queries. The queries are executed by clicking a button on this
particular form. The queries being executed reside in the same database as
the form.

Is it possible to pass the query criteria to a number of queries that reside
in another database? If yes, how do I do this.
E.g. In the annual database I analyse data in the first quarter of the year
and in the annual-1 database, I would like to run the same queries to compare
the same quarter of the previous year. Now I have to run the queries in both
databases with exactly the same query criteria. It would be easier to input
the criteria once and run the same queries in both databases.

Thank for any suggestion/solution in advance,

Ron

 

[Bob Barrows]

Could anyone help me with the following problem:

I created a form in a database. This form is used to specify criteria
for a number of queries. The queries are executed by clicking a
button on this particular form. The queries being executed reside in
the same database as the form.

Is it possible to pass the query criteria to a number of queries that
reside in another database? If yes, how do I do this.
E.g. In the annual database I analyse data in the first quarter of
the year and in the annual-1 database, I would like to run the same
queries to compare the same quarter of the previous year. Now I have
to run the queries in both databases with exactly the same query
criteria. It would be easier to input the criteria once and run the
same queries in both databases.

Thank for any suggestion/solution in advance,

You would need to use a form to collect the parameter values and VBA to
pass them to the remote queries.
Of course, part of the problem is using separate databases for each
year. You're making much more work for yourself.

 

[RoBo]

Hi Bob,

The problem is that the data, information from an SAP database, is so large
that we need databases for separate years. Where can I find information on
how to pass the criteria to the queries in the other database?

Ron Bouma

 

[Bob Barrows]

Well, I would ask in the VBA forum for details. This could be done with
either ADO or DAO. If the remote database is Access, then I would us DAO.

Again, create an unbound form to collect the parameter values, put a button
on the form and in the buttons onclick event handler, place the code to run
the queries in the local and remote databases.

When you ask for details in the VBA group, you will need to provide more
details such as:
1. Do the queries return data or are they action queries?
2. Verify that both the remote and local databases are Access mdb files

An alternative you might wish to consider (you still will not avoid writing
VBA code, but it will simplify the code) is to create a linked table in the
local database to the table in the remote database, and create a second
local query to act against the linked table. That will allow you to use only
one database object in your VBA code to execute the querydefs.