Solved

ADO and Join of two tables located on different servers

Posted on 2004-08-27
5
345 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
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sorting in Excel with Group Headers if the Exist 2 82
groovy example issue 10 90
What is Python programming? 3 103
Looking for example pivot year code used in Y2K 4 62
A short article about a problem I had getting the GPS LocationListener working.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

896 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now