Manipulating oledb tables in conjunction with foxpro (or other) tables

I'm converting a lot of FOXPRO/FOXWEB files to ASP.NET with VB.NET. The underlying database will remain FOXPRO (for now).

Given two tables in two different databases that share a common field. Say the first table i开发者_开发问答s "table1" and the second is "table2" and the name of the common field is "cid"

Say I have used oledb to extract an oledb table from table1 called "interestingrows" using a line of code something like:

adapter.Fill(MyDataSet, "interestingrows")

I would like to say something like

sql = "DELETE FROM interestingrows WHERE cid in (SELECT cid FROM TABLE2)"

... except the sql cmd is being sent to my adapter which is connect to a foxpro db and "interestingrows" is an oledb table.

It would be very nice, though not absolutely necessary, if the solution will work when we eventually decide to switch underlying database to MS SQL (or whatever customer decides on).

Based on your comment feedback, you might want to create a SECONDARY "Connection" that points to the common root folder to both tables... Otherwise I expect your query would fail by trying to gain access one level UP from where the current connection is... It might fail you on that. So, with a new rooted connection path, your query can reference the paths and tables in one query... something like...

C:\Somewhere\CommonPath\
C:\Somewhere\CommonPath\PrimaryData\
C:\Somewhere\CommonPaht\AlternateData\

and your current connection is going to

C:\Somewhere\CommonPath\PrimaryData\

create a SECONDARY connection pointing to

C:\Somewhere\CommonPath\

Then your query can be something like

delete from PrimaryData\Table1
   where cid in ( select cid from AlternateData\Table2 );

How about issuing the SQL command directly to the underlying tables?

DELETE FROM Table1 WHERE cid in (SELECT cid FROM Table2)