Single query of multiple external databases?

[ats]

I have a table that contains the database names of external databases.
Each one of these external databases contain a table (which has the
same structure) that I would like to query together as one list.

For example, say each external db has a table of names.

tbNames (external db structure)
-------
name as text
- example db1.mdb
John Doe
Jane Doe
Alan Smith

- example db2.mdb
Jim Jackson
Jack Lane
Peter North

- example db3.mdb
Kim Lane
Mike Arnet
Mat Smith

tbDBName
--------
dbName as text
- example:
c:\db1.mdb
c:\db2.mdb
c:\db3.mdb

How do you query all of these external tables together to form a
complete list?
Desired example query results:
John Doe
Jane Doe
Alan Smith
Jim Jackson
Jack Lane
Peter North
Kim Lane
Mike Arnet
Mat Smith

[Neil]

You would use a union query to compile them together into a single list, and
you'd use "IN" to specify an external database --

select name from tbNames in c:\db1.mdb
union select name from tbName in c:\db2.mdb....

If your database names are stored in a table, then you'll need to generate
your SQL in code, looping through the list of names to create the SQL.

Neil
"ats" <at*@MariettaScientific.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...

I have a table that contains the database names of external databases.
Each one of these external databases contain a table (which has the
same structure) that I would like to query together as one list.

For example, say each external db has a table of names.

tbNames (external db structure)
-------
name as text
- example db1.mdb
John Doe
Jane Doe
Alan Smith

- example db2.mdb
Jim Jackson
Jack Lane
Peter North

- example db3.mdb
Kim Lane
Mike Arnet
Mat Smith

tbDBName
--------
dbName as text
- example:
c:\db1.mdb
c:\db2.mdb
c:\db3.mdb

How do you query all of these external tables together to form a
complete list?
Desired example query results:
John Doe
Jane Doe
Alan Smith
Jim Jackson
Jack Lane
Peter North
Kim Lane
Mike Arnet
Mat Smith

[Alan Webb]

ATS,
The other poster got it pretty much right. Here is some sample SQL that
with changes to reflect your database should work:
--Pull people names from the first database
"SELECT PERSON.NAME FROM
PERSON_TBL AS PERSON IN 'C:\DOCUMENTS AND SETTINGS\SOMEGEEK\MY
DOCUMENTS\DB1.MDB'

--Now the second database.
UNION SELECT PERSON.NAME FROM
PERSON_TBL AS PERSON IN 'C:\DOCUMENTS AND SETTINGS\SOMEGEEK\MY
DOCUMENTS\DB2.MDB'"

If the source tables don't change you can create this once and every time
you run it you will get whatever is in the source tables.

"ats" <at*@MariettaScientific.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...

I have a table that contains the database names of external databases.
Each one of these external databases contain a table (which has the
same structure) that I would like to query together as one list.

For example, say each external db has a table of names.

tbNames (external db structure)
-------
name as text
- example db1.mdb
John Doe
Jane Doe
Alan Smith

- example db2.mdb
Jim Jackson
Jack Lane
Peter North

- example db3.mdb
Kim Lane
Mike Arnet
Mat Smith

tbDBName
--------
dbName as text
- example:
c:\db1.mdb
c:\db2.mdb
c:\db3.mdb

How do you query all of these external tables together to form a
complete list?
Desired example query results:
John Doe
Jane Doe
Alan Smith
Jim Jackson
Jack Lane
Peter North
Kim Lane
Mike Arnet
Mat Smith

[Alan Webb]

ATS,
The other poster got it pretty much right. Here is some sample SQL that
with changes to reflect your database should work:
--Pull people names from the first database
"SELECT PERSON.NAME FROM
PERSON_TBL AS PERSON IN 'C:\DOCUMENTS AND SETTINGS\SOMEGEEK\MY
DOCUMENTS\DB1.MDB'

--Now the second database.
UNION SELECT PERSON.NAME FROM
PERSON_TBL AS PERSON IN 'C:\DOCUMENTS AND SETTINGS\SOMEGEEK\MY
DOCUMENTS\DB2.MDB'"

If the source tables don't change you can create this once and every time
you run it you will get whatever is in the source tables.

"ats" <at*@MariettaScientific.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...

I have a table that contains the database names of external databases.
Each one of these external databases contain a table (which has the
same structure) that I would like to query together as one list.

For example, say each external db has a table of names.

tbNames (external db structure)
-------
name as text
- example db1.mdb
John Doe
Jane Doe
Alan Smith

- example db2.mdb
Jim Jackson
Jack Lane
Peter North

- example db3.mdb
Kim Lane
Mike Arnet
Mat Smith

tbDBName
--------
dbName as text
- example:
c:\db1.mdb
c:\db2.mdb
c:\db3.mdb

How do you query all of these external tables together to form a
complete list?
Desired example query results:
John Doe
Jane Doe
Alan Smith
Jim Jackson
Jack Lane
Peter North
Kim Lane
Mike Arnet
Mat Smith

[ats]

Thanks to all, but this doesn't address the file names in a table. The
given solutions work fine for predefined databases, but if you had a
whole table of entries that change... The only way I've found was using
VBA to create the SQL getting the entries from the table and making the
UNION. I guess I was looking for a single query that could do the same.

[Neil]

That's what I said -- "If your database names are stored in a table, then
you'll need to generate
your SQL in code, looping through the list of names to create the SQL."
Using VBA is the only way you can do it. But, once it's written, it should
be very simple to execute. Just have your code get the querydef of the query
you want to use, apply your generated SQL to the query, and then open the
query. Pretty simple.

Neil

"ats" <at*@MariettaScientific.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...

Thanks to all, but this doesn't address the file names in a table. The
given solutions work fine for predefined databases, but if you had a
whole table of entries that change... The only way I've found was using
VBA to create the SQL getting the entries from the table and making the
UNION. I guess I was looking for a single query that could do the same.

[Neil]

Actually, in Access the table alias isn't necessary. But, you're right, I
did forget the single quotes around the database filename.

Neil

"Alan Webb" <kn*****@hotmail.com> wrote in message
news:L6********************@comcast.com...

ATS,
The other poster got it pretty much right. Here is some sample SQL that
with changes to reflect your database should work:
--Pull people names from the first database
"SELECT PERSON.NAME FROM
PERSON_TBL AS PERSON IN 'C:\DOCUMENTS AND SETTINGS\SOMEGEEK\MY
DOCUMENTS\DB1.MDB'

--Now the second database.
UNION SELECT PERSON.NAME FROM
PERSON_TBL AS PERSON IN 'C:\DOCUMENTS AND SETTINGS\SOMEGEEK\MY
DOCUMENTS\DB2.MDB'"

If the source tables don't change you can create this once and every time
you run it you will get whatever is in the source tables.

"ats" <at*@MariettaScientific.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...

I have a table that contains the database names of external databases.
Each one of these external databases contain a table (which has the
same structure) that I would like to query together as one list.

For example, say each external db has a table of names.

tbNames (external db structure)
-------
name as text
- example db1.mdb
John Doe
Jane Doe
Alan Smith

- example db2.mdb
Jim Jackson
Jack Lane
Peter North

- example db3.mdb
Kim Lane
Mike Arnet
Mat Smith

tbDBName
--------
dbName as text
- example:
c:\db1.mdb
c:\db2.mdb
c:\db3.mdb

How do you query all of these external tables together to form a
complete list?
Desired example query results:
John Doe
Jane Doe
Alan Smith
Jim Jackson
Jack Lane
Peter North
Kim Lane
Mike Arnet
Mat Smith