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. Joining two tables from two databases

2. Creating a join on tables in two different MSSQL 6.5 DB's

3. Subtract or Add from two diffrent Table

4. Subtract or Add from two diffrent Table

5. Table join between two tables in different databases

6. Joining two tables from different databases

7. How to Left join more than two table ?

8. SQL: Non-Equal Join of Two Tables

9. How to Join two tables thrice?

10. SQL: Non-Equal Join of Two Tables

11. Join two tables

12. Joining two tables on different length fields

 

 
Powered by phpBB® Forum Software