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