Q: "Linked Queries"

[MarkD]

Using Access 2000.

I have two reporting databases: dbA and dbB.
dbA has queries used to make a pipeline report
dbB has queries used to make a company dashboard report
dbA and dbB have some common queries.

What I'd like is to have dbB link to the queries on dbA so
that we're only using one set of queries (and so, any
modifications need only to be done in one place).

How would you approach this problem? I don't think there's
a way to link queries. I was thinking of refreshing
queries when the db opens (ie. have an autoexec macro drop
the dbB queries and reimport the dbA queries), but that
doesn't seem like the best way to do things.

Thanks!
-Mark

 

[Marshall Barton]

Using Access 2000.

I have two reporting databases: dbA and dbB.
dbA has queries used to make a pipeline report
dbB has queries used to make a company dashboard report
dbA and dbB have some common queries.

What I'd like is to have dbB link to the queries on dbA so
that we're only using one set of queries (and so, any
modifications need only to be done in one place).

How would you approach this problem? I don't think there's
a way to link queries. I was thinking of refreshing
queries when the db opens (ie. have an autoexec macro drop
the dbB queries and reimport the dbA queries), but that
doesn't seem like the best way to do things.

You can sort of "link" a query in dbB to either a table or a
query in dbA by using the IN phrase in the FROM clause:

If you have a query named queryname in dbA and you wish to
use it in dbB, create a new query (in dbB) named queryname:

SELECT X.*
FROM [path\dbA.mdb].queryname AS X

Thus, you can make it look like the queries in dbB are the
same as the corresponding query in dbA.

 

[John Vinson]

Using Access 2000.

I have two reporting databases: dbA and dbB.
dbA has queries used to make a pipeline report
dbB has queries used to make a company dashboard report
dbA and dbB have some common queries.

What I'd like is to have dbB link to the queries on dbA so
that we're only using one set of queries (and so, any
modifications need only to be done in one place).

How would you approach this problem? I don't think there's
a way to link queries. I was thinking of refreshing
queries when the db opens (ie. have an autoexec macro drop
the dbB queries and reimport the dbA queries), but that
doesn't seem like the best way to do things.

Thanks!
-Mark

I'd suggest (if you are not already doing so) having the *tables*
linked, and all the Queries in one frontend database.

An alternative - which I have NOT tried, and it might not work this
way - would be to have a .mda library database with the common
queries, which you would include as an Add-In.

John W. Vinson[MVP]

 

[MarkD]

Hi John,

Thanks for the reply.

We had thought about doing that, but it's not just 2 dbs,
but multiple dbs which use a common subset of "base
queries". Having them all in one db would get
complicated... though I guess we could group the queries
together.

-Mark

 

[MarkD]

Hi Marsh,

Thanks, this is just what I'm looking for.

-Mark

-----Original Message-----

Using Access 2000.

I have two reporting databases: dbA and dbB.
dbA has queries used to make a pipeline report
dbB has queries used to make a company dashboard report
dbA and dbB have some common queries.

What I'd like is to have dbB link to the queries on dbA so
that we're only using one set of queries (and so, any
modifications need only to be done in one place).

How would you approach this problem? I don't think there's
a way to link queries. I was thinking of refreshing
queries when the db opens (ie. have an autoexec macro drop
the dbB queries and reimport the dbA queries), but that
doesn't seem like the best way to do things.

You can sort of "link" a query in dbB to either a table or a
query in dbA by using the IN phrase in the FROM clause:

If you have a query named queryname in dbA and you wish to
use it in dbB, create a new query (in dbB) named queryname:

SELECT X.*
FROM [path\dbA.mdb].queryname AS X

Thus, you can make it look like the queries in dbB are the
same as the corresponding query in dbA.