querying two databases at the same time

  • Thread starter Thread starter UB
  • Start date Start date
U

UB

Is there a way to write VB code to query a table in one
database and join it using a common ID field to another
table in another database
 
UB said:
Is there a way to write VB code to query a table in one
database and join it using a common ID field to another
table in another database
Click to expand...


The quick answer is "no"

ADO recordset, command, and connection objects support only one
connection. To get a table joined with another table of a different
database would require two separate connections.

If both databases are in a single server of, say, SQL Server, then you
would be able to construct a view on the server and have access link to
that view as if it were a table.

However,

you CAN link to each table of the disparate databases. Then you can
refer to those linked tables and use the 'CurrentProject.Connection' as
the connection. That way, Access will execute the 'Join' not one of the
servers.

Do you know how to link to tables in other databases?

Doug
 
UB said:
Is there a way to write VB code to query a table in one
database and join it using a common ID field to another
table in another database
Click to expand...

Yes, here's a round-about way without linking tables...

1. Create a Query1 in current database pointing to data
in external database1:

Select DistinctRow tblTable1.*
From tblTable1 In 'C:\ExternalDatabase1.mdb'

2. Create a Query2 in current database pointing to data
in external database2:

Select DistinctRow tblTable2.*
From tblTable2 In 'C:\ExternalDatabase2.mdb'

3. Lastly, create a Query3 in current database to marry
all data from both external databases:

Select Query1.Data, Query2.OtherData
From Query1
Inner Join Query2 On Query1.ID = Query2.ID

That should do it!

Calvin Smith
http://www.CalvinSmithSoftware.com - Automation Code
http://www.SpanglesNY.com - Fendi, Prada, etc - 60% off
 
What if external database is password protected?
Is there some syntax for IN clause to work with external
password protected databse?
 
Not sure, haven't tried it. You may want to add something like the following
example to the end of the path string and give it a try:

,False, False, "MS Access;PWD=YourPassWord"
 
It doesn't work.
It is strange:
Access help (also MSDN library) doesn't have info how
should be constructed connection string for IN clause, if
external DB is passworded. So much the "bette" there is no
info about-does IN clause supports external DB with
password.
:(
 
Back
Top