Skip to main content
A
Anonymous
January 3, 2011
Question

Query Multiple Databases in same query

  • January 3, 2011
  • 2 replies
  • 1921 views

I have run into something that I haven't done before. I have three Access databases (I can not change this fact at this time, nor can I make a new database, combine them, or change them in any way.) each have distinct information, but I need to write queries that pull information from all of them.

For example:

Database1, tableA - ItemID, ItemName, ItemDescription, CategoryID

Database2, tableM - ItemID, ItemCost, ItemPrice

Database3, tableZ - CategoryID, CategoryName

I need to putput:

ItemName, ItemPrice, CategoryName

As I said, these databases are read only, and I can not create an intermediary database. I'd like to do the output in one query, instead of making sub queries that run in a loop to output the data. The databases are on the same server.

Is this even possible?

I can do it with looping, but the final output is about 2000 items, so that's a heavy server load.

Thanks!

Michelle

This topic has been closed for replies.

2 replies

M
meensi
Inspiring
January 4, 2011

Hi,

May be useful.

I think it will work

Database 1 table1; Database 2 table 2; Database 3 table 3;

Select

      t1.itemname as ITEMNAME,

     t2.itemprice as ITEMPRICE,

     t3.categoryname as CATEGORYNAME

from

     database1..table1 t1,

     database2..table2 t2,

     database3..table3 t3

where........ <condition>

Please check this, if its not working, i m really sorry..

ilssac
ilssac
Inspiring
January 3, 2011

IF you CAN NOT change the databases or make an intermediate database.  There is little you can do at the database level.

Access has the ability for database A, to read tables from database B remotely, but you would have to modify the mdb files to set that up, if it does not exist.

Asuming you truly can not do that, you left doing it at the applicaiton leve.

One option would be to query the three databases and use the query of a query feature to goin them together into a single query.

Another option would be to comibne them into some other data structure, such as a more basic structure and|or array.

Finally you could possible loop over the queries, combining the content of the output using fully qualified query references i.e. query["column"][row].  But this would probably be the last and most compicated choice.

mnp13 wrote:

I can do it with looping, but the final output is about 2000 items, so that's a heavy server load.

An output load of 2000 itterations is not really that much.  Now if you are including database access actions with each and every itteration, then yes, that would not be best.  But just looping 2000 times and outputing data would not cause me much concern at least not for another magnitude or two.

A
Anonymous
January 3, 2011

ilssac wrote

An output load of 2000 itterations is not really that much.  Now if you are including database access actions with each and every itteration, then yes, that would not be best.  But just looping 2000 times and outputing data would not cause me much concern at least not for another magnitude or two.

Yes, I'm including re-accessing the database twice for each row for the output. As in - query 1 outputs 2000 rows, and for each row output, it runs two queries to get the additional information from the other databases. OUCH.

Unfortunately, no, I can't change anything on the databases at this point. Perhaps in the future, but that's going to take an act of God to give me those permissions (and I wish I was kidding.)

I'll have to give QoQ a go... first time for everything! lol

Thanks! I'll let you know how it goes (or doesn't go...)

-
-__cfSearching__-
Inspiring
January 3, 2011

One more possibility. Most databases provide options for selecting information from multiple databases. Obviously the proper permissions must exist for it to work. But it may be worth a shot.

http://msdn.microsoft.com/en-us/library/bb177907%28v=office.12%29.aspx

http://office.microsoft.com/en-gb/access-help/in-clause-HA001231484.aspx

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices