Using a view with recordsets

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Steven Blair - 26 Nov 2004 16:53 GMT

Hi,

Here is a brief description of my problem:

I have two databases on two different servers. The boxes cannot
communicate with each other. The system I am developing can connect to
each database (one must be disconnected before I can connect to the
other).

I need to return the data from DB A in a recordset as well as the data
from DB B in a seperate recordset.

What I need to do do is create a view between the two recordsets.
Recordset A needs to be joined to Recorset B.

Can this be achieved by using SQL on the two recordsets?

If this is not possible, could someone suggest an alternative approach,
for example, would it be possible to select the data from DB A into
recordset A AND select the data from DB B, but put it into Recordset A
(remove the 2nd recordset from the problem).

Any help on this matter would be appreciated.

Regards,

Steven

Reply to this Message

Mark J. McGinty - 26 Nov 2004 18:52 GMT

> Hi,
>
[quoted text clipped - 12 lines]
>
> Can this be achieved by using SQL on the two recordsets?

A join? No, not really, short of installing a db engine on the client,
importing data from each server, and then joining the data locally.

If it's a union, rather than a join, where the fields in both tables are the
same, and each side represents a subset of the rows, and the number of rows
is relatively small (say 10K-100K rows,) and you can count of an ample
amount of RAM in client systems, and clients have fast network access to
both servers, there may be some reasonably efficient ways to manipulate
XML-persisted copies of the recordsets, so as to merge the two, but
pointless to gio into unless all of the many contingencies are workable...

Oh btw, you weren't going to need this recordset to be updateable were you?

:-)

You may be further ahead trying to work out the connectivity details. I'm
going to go out on a limb and guess that one server is connected via VPN?
If that's the case it's most definitely possible to be connected to both at
once -- it's not easy, particularly if the VPN server uses DHCP to assign
client addresses but it is fully possible. As such it should be possible to
connect the two servers -- the *real* answer to your question is of course,
linked servers.

-Mark

> If this is not possible, could someone suggest an alternative approach,
> for example, would it be possible to select the data from DB A into
[quoted text clipped - 9 lines]
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Reply to this Message

Steven Blair - 27 Nov 2004 13:41 GMT

Thanks for the reply.

Unfortunately the servers are outwith my control. They exist on seperate
servers which cannot speak to each other (even if its technically
possible lol)

Regards,

Steven

Reply to this Message

Jason Tost - 29 Dec 2004 18:01 GMT

> Thanks for the reply.
>
[quoted text clipped - 8 lines]
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

If the two db servers are not able to communicate with each other, I
don't know of a way you can combine results into a single recordset. As
Mark said, using joins, unions, etc will combine results from multiple
data sources, however the servers then do need to be able to talk to
each other. The most elegant solution is to allow the DBMS's to talk to
each other, combining query results on the server, rather than on the
client. Since a recordset object performs queries against a single
connection object, ie the recordset cannot be aware of multiple
connection objects at once against which it can query simultaneously,
and a connection represents a single connection to a single data source,
combining results in this way on the client is unlikely, unless as Mark
says, you install some kind of DB engine on the client that is capable
of performing this action for you.

An example of how to accomplish this would be to create an access
database on the client, set up linked tables within the access database
to your two data sources, and then perform all queries against your
access database.

Reply to this Message

VB Forum / Database Access / December 2004

Using a view with recordsets