Querying Multiple databases

karthick79
karthick79 used Ask the Experts™
on
Hi,

wanted to know if its possible to query more than two databases, in ADO at the same time.
i am aware that 2 databases can be queried, but this doens't work for 3 or more databases.

This works :

SELECT dbdprice.mf_schcode,userportfolio.disname  FROM UserPortfolio, dbdprice in 'C:\PRICE.mdb'  where userportfolio.mf_schcode = dbdprice.mf_schcode

But this won't run :

SELECT *  From userportfolio, (dbdprice in 'C:\Price.MDB'), (dbdschm IN 'D:\VB Projects\Mutual Fund\MOTFULL.mdb') Where userportfolio.mf_schcode = dbdprice.mf_schcode And userportfolio.mf_schcode = dbdschm.mf_schcode

Nor Does this work :

SELECT dbdschm.SCH_NAME, dbdprice.NAVDATE, dbdprice.NAVRS, userportfolio.DisName FROM (dbdprice IN 'C:\Price.MDB' INNER JOIN dbdschm  IN 'D:\VB Projects\Mutual Fund\MOTFULL.mdb' ON dbdprice.MF_SCHCODE = dbdschm.MF_SCHCODE) INNER JOIN userportfolio ON dbdschm.MF_SCHCODE = userportfolio.MF_SCHCODE;

Pls. Lemme know abt this ASAP.
Thanx a million in advance.
KV.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
The best way to do this is to set up link tables in one of the databases.  You can do this in Access (right click in the table view and select link.  then browse to the folder and select the db and table you wish to link.)  You can also do it in DAO or with the ADOX object model:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoadoupdate_topic6.asp

Scroll down to creating a linked table (code excerpted here)

Sub ADOCreateAttachedJetTable()

   Dim cat As New ADOX.Catalog
   Dim tbl As New ADOX.Table

   ' Open the catalog
   cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=.\NorthWind.mdb;"

   ' Set the name and target catalog for the table
   tbl.Name = "Authors"
   Set tbl.ParentCatalog = cat

   ' Set the properties to create the link
   tbl.Properties("Jet OLEDB:Create Link") = True
   tbl.Properties("Jet OLEDB:Link Datasource") = ".\Pubs.mdb"
   tbl.Properties("Jet OLEDB:Link Provider String") = ";Pwd=password"
   tbl.Properties("Jet OLEDB:Remote Table Name") = "authors"

   ' Append the table to the collection
   cat.Tables.Append tbl

   Set cat = Nothing

End Sub

Commented:
Once you've set up your link tables, you can query against them as if they were all in the same database.

Author

Commented:
Thanx,

but is there any way of querying the three databases such that i get the output straightaway, rather than linking, as shown by the query to use 2 dbs.

Linking tables takes a lot of time, I've got a massive db of around 1.2GB to manage, and linking may slow down the application drastically, and the client may not like it :(
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Here's what I came up with:

SELECT * FROM address, [;Database=c:\mydb\contacts2.mdb].table1, [;Database=c:\mydb\contacts3.mdb].table2

Author

Commented:

SELECT DBDSCHCM.MCLASS,
[;Database=D:\VB Projects\Mutual Fund\motfull.mdb].dbdschm.SCH_NAME,
[;Database=c:\Price.mdb].dbdmfsch.SIZE
FROM DBDSCHCM,
[;Database=D:\VB Projects\Mutual Fund\motfull.mdb].dbdschm,
[;Database=c:\Price.mdb].dbdmfsch
WHERE
[;Database=c:\Price.mdb].DBDMFSCH.MF_SCHCODE
=
[;Database = D:\VB Projects\Mutual Fund\motfull.mdb].DBDSCHM.MF_SCHCODE
AND  
DBDSCHCM.SCHCLCODE
=
[;Database=c:\Price.mdb].DBDMFSCH.SCHCLCODE


Is This possible ??
Its giving an error .. "Method _Open of RS Failed"

Thanx again for ur suggestion..
Using twalgrave's soultion makes it easy to move data between databases Select .... Into etc. Using PaulHews's solution mkaes it easy to create queries from the linked objects.  It also has the advantage that your database is no longer limited to just 2GB.

Author

Commented:
That I am already using to tranfer data across 2 DBs, but is there a way to frame the query as in my earlier comment ..

I know .. linking is the last way out, but it sure is slow .. it'll be great if i can manage it dynamically ...

Thanx.

Author

Commented:
Hi .. figured that out myself ..
Anywayz thanx a million to InTheDark, TwAlGrave, PaulHews for their valuable suggestion.
Its done this way :

Select
DBDSCHCM.MCLASS, TDBDMFSCH.LAUNC_DATE, TDBDSCHM.SCH_NAME From
DBDSCHCM,
(Select * from dbdmfsch in 'D:\VB Projects\Mutual Fund\portfoli.mdb') as TDBDMFSCH,
(Select * from dbdschm in 'D:\VB Projects\Mutual Fund\motfull.mdb') as TDBDSCHM
where
tdbdmfsch.schclcode=dbdschcm.schclcode
and
tdbdmfsch.mf_schcode=tdbdschm.mf_schcode

In Case of an inner Join :

SELECT DBDSCHCM.MCLASS, TDBDMFSCH.LAUNC_DATE, TDBDSCHM.SCH_NAME
FROM ([Select * from dbdmfsch in 'D:\VB Projects\Mutual Fund\portfoli.mdb']. AS TDBDMFSCH
INNER JOIN [Select * from dbdschm in 'D:\VB Projects\Mutual Fund\motfull.mdb']. AS TDBDSCHM
ON TDBDMFSCH.MF_SCHCODE = TDBDSCHM.MF_SCHCODE)
INNER JOIN DBDSCHCM ON TDBDMFSCH.SCHCLCODE = DBDSCHCM.SCHCLCODE
WHERE
(((TDBDMFSCH.SCHCLCODE)=[dbdschcm].[schclcode])
AND ((TDBDMFSCH.MF_SCHCODE)=[tdbdschm].[mf_schcode]))
Cool!

Commented:
Creative solution, nice work. :)
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in Community Support that this question is:
- refund and close
Please leave any comments here within the
next seven days.
Points refunded and moved to PAQ

** Mindphaser - Community Support Moderator **

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial