Joining tables from different Databases.

[bobbytom]

Dear Friends.

Select Emp.EmployeeCode, Emp.EmployeeName,Emp.DeptCode,Dept.DepartmentName
From Emp,Dept
Where Emp.DeptCode = Dept.DeptCode.

In this query Emp table lying in the Database_1, and Dept table tying in Database_2.

How i run this query in VB by using ADODB Connectivity. how i write the connection,lockType after the query; ie

rs.op 'sql......................' ? , ? , ?

I want to insert the result in another table that is in Database_3

how many databases connections i have to make ?. pl advise


Thanks
Bobby

[bobbytom]

is there anyone to solve this problem

[Mindcrime]

What database are you using?

If its Access, you create a Link Table and use that, just like it belongs to the ONE Database.

If you are using Oracle, then I don't know. Our Oracle system allows us to reference any Database instance...becuase its all under Oracle I would assume.

If you are wanting to mix Access and Oracle in the same Query, then there are ways to 'Fool' the system to let you do this using COM+...

...but if you don't even know ADO yet, I wouldn't even consider it.

Dim Conn as ADODB.Connection
Dim rst as ADODB.Recordset
Dim SQLScript as String

    Set Conn = New ADODB.Connection
    With Conn
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=UseYourDatabaseLocation;Persist Security Info=False"
        .Open
    End With

    SQLScript = UseYourSQL

    Set rst = New ADODB.Recordset
    With rst
        .ActiveConnection = Conn
        .CusrorType = adOpenDynamic
        .CusorLocation = adUseClient
        .LockType = adLockOptimistic
        .Source = SQLScript
        .Open
    End With