Solved

ADO and Join of two tables located on different servers

Posted on 2004-08-27
5
366 Views
Last Modified: 2010-05-18
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.
0
Comment
Question by:anneg2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 4

Expert Comment

by:computerg33k
ID: 11917613
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
 

Author Comment

by:anneg2
ID: 11917781
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
 
LVL 5

Accepted Solution

by:
rsriprac earned 250 total points
ID: 11918679
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MacOS and programming in React 7 47
VbScript to countdown to New Year's Day 6 79
How to Convert a Quote to an Order in SalesForce 2 125
Selenium docs api java index 3 69
This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question