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
Solved

ADO and Join of two tables located on different servers

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I know it’s not a new topic to discuss and it has lots of online contents already available over the net. But Then I thought it would be useful to this site’s visitors and can have online repository on vim most commonly used commands. This post h…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
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…

860 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