473,743 Members | 2,272 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

Nov 13 '05 #1
6 9610
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*@MariettaSc ientific.com> wrote in message
news:11******** *************@g 14g2000cwa.goog legroups.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

Nov 13 '05 #2
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\SOMEGE EK\MY
DOCUMENTS\DB1.M DB'

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

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*@MariettaSc ientific.com> wrote in message
news:11******** *************@g 14g2000cwa.goog legroups.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

Nov 13 '05 #3
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\SOMEGE EK\MY
DOCUMENTS\DB1.M DB'

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

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*@MariettaSc ientific.com> wrote in message
news:11******** *************@g 14g2000cwa.goog legroups.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

Nov 13 '05 #4
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.

Nov 13 '05 #5
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*@MariettaSc ientific.com> wrote in message
news:11******** **************@ l41g2000cwc.goo glegroups.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.

Nov 13 '05 #6
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*****@hotmai l.com> wrote in message
news:L6******** ************@co mcast.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\SOMEGE EK\MY
DOCUMENTS\DB1.M DB'

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

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*@MariettaSc ientific.com> wrote in message
news:11******** *************@g 14g2000cwa.goog legroups.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


Nov 13 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

16
7515
by: noah | last post by:
Does PHP have a feature to associate Cookie sessions with a persistent database connection that will allow a single transaction across multiple HTTP requests? Here is how I imagine my process: I have an series of interactive HTML forms. The user begins a Cookie session. A database connection is opened and a transaction is begun. After the user goes through any number of pages where they update the database they finish on a page where...
0
1800
by: sean peters | last post by:
Hi all, i've been weighing the pros and cons of running multiple concurrent mysqld's on one server, to have better control over what databases are on what physical disks. System: 4 processor sun box running solaris with eighteen 36Gb drives. The situation is that i have a bunch of databases on one server that can all be classified as either external use or internal use. The internal use databases are consistently hit pretty hard, and...
0
1610
by: Dave | last post by:
Hi all, I have a problem with a query. (well, I actually have a few problems...) Here is my query. select FOCUS.SiteName, FOCUS.URL, OTWAdCars.* , REGION.SiteName as RegionSite, REGION.URL as RegionUrl from OTWSite as FOCUS right join OTWSite as REGION
3
6999
by: G rumpy O ld D uffer | last post by:
This is probably a 'Low-Level' question to all the ACCESS experts but I've only been using ACCESS for a couple of weeks. I've been given 30+ (and counting) separate 'Weekly' Databases which all have a Table, in exactly the same Field format, in each of these 'Weekly' DataBases. I want to set-up a new 'Master' Database, so that I can write one Query to search all the 'Weekly' Databases and produce one new Table in the 'Master' Database...
7
2729
by: Daz | last post by:
Hi. I am trying to select data from two separate MySQL tables, where I cannot use join, but when I put the two select queries into a single query, I get an error telling me to check my syntax. Both of the queries work fine when I use them to query the MySQL server directly. My guess is that the MySQL extension only expects a single resource back from the database, but get's several, or that it just checks the statement first, and decides...
0
1061
by: rkreddys | last post by:
Tasks to Move SQL 2000 Databases into Single Server with multiple dabases then Upgrade SQL 2000 to 2005 and Merge multiple databases into single database. How to move Databases over WAN links How to make Single Server with Multiple Databases. I need to write a technical Design document for this project, please provide your usefull information.
12
2113
by: bhipwell via AccessMonster.com | last post by:
Hello, I have hit the "Cannot open any more databases" and "System resource exceeded" errors. Knew this was coming, but as I got closer to finishing the database, I hoped it wouldn't be an issue. My DB consists of hundreds of unique pieces of information (in atomic tables of course). However, I have many multiple queries that are required to compile information and conduct various calculations to put it in a workable format for...
1
1496
by: cvalarmathi | last post by:
Hi Friends, How to drop all databases in one query.I don't know.If anybody know the answer please reply me.....
4
3995
by: JoyceBabu | last post by:
Can anyone plz tell me the advantages and disadvantages of using multiple databases / single database for all tables. I have a site with more than a 100 tables. Current all the tables are in a single database, so that I can access it with a single Database Wrapper Object (PDO). Should I split it into multiple databases? Will it affect the speed?
0
8970
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9486
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9344
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9277
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9214
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6763
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4572
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4827
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
2195
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.