Join 2 tables from 2 different databases in ADO.net

[H_M_A_M_A]

I am tying to build a COMMAND that joins two tables from two different databases, one database is stored in SQL Server, the other one is stored in MS Access, the problem is I can not make the SQL statement in one command, since the connections are different.

Example:

Dim MyCommand AsNew SQLCommand("SELECT T1.Col1,T2.Col4
FROM T1,T2 WHERE T1.Col1 = T2.Col2",SQLConnection)


T1 is a table in MS Access Database (Database1)
T2 is a table in SQL Server database (Database2)

In addition, I have two connections:
MSAccessConnection
SQLConnection

I tried to load the two tables into a dataset and select the data, but my tables are too big and the relationship is too complicated, which makes it impossible.


Please, is there any way to join these tables in one COMMAND?, and that COMMAND can retrieve the desired data and load it into a dataset.

Thanks in Advance.

 

[H_M_A_M_A]

sorted out


I have found a way to join two data sources in one Statement, I used
OPENROWSET query from MS SQL Server 2000.

in my example:

Dim MyCommand AsNew SQLCommand("SELECT T1.Col1,T2.Col4
FROM T2, OPENROWSET('Microsoft.Jet.OLEDB.4.0',' C:\Database1.mdb";;, T1) as T1
WHERE T1.Col1 = T2.Col2",SQLConnection)

T1 is a table in MS Access Database (Database1)
T2 is a table in SQL Server database (Database2)