Run query against recordsets created from two different connection objects?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

James Houston - 29 Dec 2005 20:20 GMT

Here's what I need to do. I have 2 different databases, an Access 97 db and
a FoxPro 6 db. I need to run a query that finds records in the FoxPro db
that match a certain condition in the Access db. Is this possible using
ADO? If so, can someone provide an example? Thanks in advance.

Reply to this Message

Dmitriy Antonov - 30 Dec 2005 01:38 GMT

> Here's what I need to do. I have 2 different databases, an Access 97 db
> and a FoxPro 6 db. I need to run a query that finds records in the FoxPro
> db that match a certain condition in the Access db. Is this possible
> using ADO? If so, can someone provide an example? Thanks in advance.

You can't run one query against both of them. So you'll need to get a
recordset from one of them, build criteria using its values and then obtain
recordset from another DB based on those criteria. If you need to combine
them, then you can create in-memory recordset and populated it using values
of two other recordsets obtained from both DBs.

Another way, which I would prefer in my own case, is just to make a link in
the Access DB to Foxpro's table. Having a link, you can use linked tables
the same way as you use "native" ones - you will need to use standard
trivial SQL statement.

Dmitriy.

Reply to this Message

James Houston - 30 Dec 2005 16:04 GMT

Dmitriy

Thanks for the advice. I've tried linking to this Foxpro table in the past,
but have never been able to get it to work. Access freezes up when I try to
browse the linked Foxpro table. I've tried this on several different PCs,
all with the same result. I mentioned this in the Foxpro newsgroups and had
people tell me that they had heard of this happening, but didn't know what
the solution was. I've tried linking using both ADO and ODBC and haven't
had any luck with either. If anyone has any insight about this, please let
me know.

>> Here's what I need to do. I have 2 different databases, an Access 97 db
>> and a FoxPro 6 db. I need to run a query that finds records in the
[quoted text clipped - 14 lines]
>
> Dmitriy.

Reply to this Message

Dmitriy Antonov - 31 Dec 2005 04:17 GMT

> Dmitriy
>
[quoted text clipped - 6 lines]
> and ODBC and haven't had any luck with either. If anyone has any insight
> about this, please let me know.

Hmmm. My personal experience with Foxpro hardly exceeds couple hours
overall. I've just created simple Foxpro DB and tried to link its table in
the Access and... yes... it crashed. I think I could do it before, but not
anymore.
As I said, I know nothing about Foxpro, but it seems that you can create a
link (perhaps this is what's called Remote View there) in the Foxpro. So in
your case you could do it in opposite direction: create a link in the Foxpro
DB to Access's table. I've just did it and it works. I didn't try to work
with it programmatically though, but I could create a query with two tables
(one local table and another - remote view) joined.

Other than that, the only option remaining, that I could imagine, is to run
separate Recordsets and then process them programmatically, depending on
your task (see my previous post).

Dmitriy.

Reply to this Message

VB Forum / Database Access / December 2005

Run query against recordsets created from two different connection objects?