Solved

ADO and Join of two tables located on different servers

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

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.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

746 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

12 Experts available now in Live!

Get 1:1 Help Now