ADO and Join of two tables located on different servers

Using VB6 - ADO 2.7 - SQL Server:
Need to retrieve data from a join of two tables located on separate servers.  I know how
to open and retrieve from a single server ... but not from multiple servers.  Please provide the syntax for opening a recordset
from more than one server.
anneg2Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
rsripracConnect With a Mentor Commented:
ADOX lets you control a database's structure. In the first database, use ADOX to create a link to the table in the second database. Then you can perform a normal query joining the table and the link.  
 
 
' Note: This program includes a references to:
'
'   Microsoft ActiveX Data Objects 2.6 Library (ADO)
'   Microsoft ADO Ext. 2.6 for DDL and Security (ADOX)
'
Private Sub cmdExecute_Click()
Dim app_path As String
Dim db_file As String
Dim conn As ADODB.Connection
Dim adox_catalog As ADOX.Catalog
Dim adox_table As ADOX.Table
Dim rs As ADODB.Recordset
Dim i As Integer
Dim txt As String

    ' Find the application path.
    app_path = App.Path
    If Right$(app_path, 1) <> "\" Then app_path = app_path _
        & "\"

    ' Open the Depts database.
    db_file = app_path & "Depts.mdb"
    Set conn = New ADODB.Connection
    conn.ConnectionString = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & db_file & ";" & _
        "Persist Security Info=False"
    conn.Open

    ' Create a link to the Employees table
    ' in the Emp database.
    Set adox_catalog = New ADOX.Catalog

    ' This statement ties the linked
    ' table to the open connection.
    Set adox_catalog.ActiveConnection = conn

    Set adox_table = New ADOX.Table
    With adox_table
        Set .ParentCatalog = adox_catalog
        .Name = "LinkedTable"
        .Properties("Jet OLEDB:Link Datasource") = app_path _
            & "Emps.mdb"
        .Properties("Jet OLEDB:Link Provider String") = "MS " & _
            "Access"
        .Properties("Jet OLEDB:Remote Table Name") = _
            "Employees"
        .Properties("Jet OLEDB:Create Link") = True
    End With

    ' Add the table to the Tables collection.
    adox_catalog.Tables.Append adox_table

    ' Perform the query.
    Set rs = conn.Execute( _
        "SELECT * " & _
        "FROM Departments, LinkedTable " & _
        "WHERE Departments.DepartmentId = " & _
            "LinkedTable.DepartmentId", , _
        adCmdText)

    ' Display the results.
    Do While Not rs.EOF
        txt = txt & rs.Fields(0).Value
        For i = 1 To rs.Fields.Count - 1
            txt = txt & ", " & rs.Fields(i).Value
        Next i
        txt = txt & vbCrLf
       
        rs.MoveNext
    Loop
    rs.Close
    txtResults.Text = txt

    ' Delete the link from the Depts database.
    adox_catalog.Tables.Delete "LinkedTable"

    ' Close the database.
    conn.Close
End Sub
 
-Ram
0
 
computerg33kCommented:
Open an ADO Table Recordset:
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "c:/webdata/northwind.mdb"
set rs = Server.CreateObject("ADODB.recordset")
rs.Open "Customers", conn
%>Open an ADO SQL Recordset:

<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "c:/webdata/northwind.mdb"
set rs = Server.CreateObject("ADODB.recordset")
rs.Open "Select * from Customers", conn
%>

just create two connections--one for each server and just called the correct one when you need the data.
0
 
anneg2Author Commented:
The issue is that table1 is on server1 and table2 is on server 2 and the sql stmt (if the servers were linked) would be select server1.dbname.tablename T1, server2.dbname.tablename  T2 where T1.xx = T2.xx......
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.