Here's a sticky wicket - joining two tables on two diffrent servers with ADO 
Author Message
 Here's a sticky wicket - joining two tables on two diffrent servers with ADO

Howdy folks,

vb6, ado2.1, sql7:

I am trying to do a SQL join on two tables, not a problem if they reside in
the same database, then they both use the same connection object, I say
execute(strSQL) and shazam.  How do you do it if the two tables live in two
different databases on two different SQL7 servers?  I can't use the same
connection object (different machines, different db names, different logins,
different passwords).  Access handles this nicely, I can just link the
external tables, and Access treats them like they are from the same db.  I'm
sure there's a way to do this via ADO, just haven't been able to find it.
Any help is appreciated.

bigjim



Mon, 02 Sep 2002 03:00:00 GMT
 Here's a sticky wicket - joining two tables on two diffrent servers with ADO

I only have about fifty seconds to write this, so excuse it if it makes no
sense.

Off the top of my head, I would fetch and persist the subsets from the two
data stores and then disconnect them using the .ActiveConnection=Nothing
approach. This would have the effect of eliminating the possibility that
either server could drop you in the mud. With the two disconnected
recordsets in the same application memory space you should then have a
relatively easy job in executing a second query (the JOIN) against them.
It's not elegant, and I'm sure there are other better solutions, but it
would at least get you started.

Also, upgrade to ADO 2.5 (available at MS's UDA site). It handles remote
recordsets much more adroitly.


Quote:
> Howdy folks,

> vb6, ado2.1, sql7:

> I am trying to do a SQL join on two tables, not a problem if they reside
in
> the same database, then they both use the same connection object, I say
> execute(strSQL) and shazam.  How do you do it if the two tables live in
two
> different databases on two different SQL7 servers?  I can't use the same
> connection object (different machines, different db names, different
logins,
> different passwords).  Access handles this nicely, I can just link the
> external tables, and Access treats them like they are from the same db.
I'm
> sure there's a way to do this via ADO, just haven't been able to find it.
> Any help is appreciated.

> bigjim



Tue, 03 Sep 2002 03:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Can I join Two tables from diffrent databse

2. Joining two tables from two servers using Execute ...

3. Help..join on two table , Selecting records from two tables

4. Why I can't update two tables which joined in the ADO recordsets

5. Retrieve data from two tables, from two databases, in two registered servers

6. Join two tables from two different SQL databases

7. Error when joining two tables by two columns

8. Error when trying to join two tables on two different SQLServer

9. SQL Query - join two tables in two different databases

10. join by two columns on two tables

11. inner join query between two table of two different database

12. ...inner join query between two table of two different database


 
Powered by phpBB® Forum Software