Binaryworld - How to perform a distributed query using a linked server on SQL Server ... [ VB -> Database ]

One of the neatest features in SQL Server 7 and later versions is the capability to query external data sources through their OLE DB providers, in other words behaving exactly as an ordinary OLE DB data consumer. Queries performed in this way are known as distributed queries and can be implemented through different techniques (see SQL Server's Books on Line for more information):

the OpenQuery T-SQL function
the OpenRowset T-SQL function
the so-called four-part name syntax of T-SQL
The following VB code shows how it is possible to create a linked server that points to a Microsoft Access database. The linked server is created through the sp_addlinkedserver stored procedure (learn more about this procedure in Books on Line). The example also shows how you can use the sp_addlinkedserverlogin stored procedure, which is necessary to access password-protected databases:

Click here to copy the following block

Dim cn As ADODB.Connection

Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;Data Source=(local);Initial Catalog=master;User " _
  & "Id=sa;Password=;"
cn.Execute "EXEC sp_addlinkedserver 'MyLinkedServerName', 'Jet 4.0', " _
  & "'Microsoft.Jet.OLEDB.4.0', 'c:\program files\microsoft " _
  & "office\office\samples\northwind.mdb'"
cn.Execute "EXEC sp_addlinkedsrvlogin ' MyLinkedServerName ', FALSE, NULL, " _
  & "'admin', ''"
cn.Close
Set cn = Nothing

Once you have defined the linked server, you can perform queries on it, and these queries can even include JOIN operations with tables in other SQL Server databases. The code below shows how you can read data from the Customers table in Northwind.mdb, using the three techniques mentioned previously:

Click here to copy the following block

Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim SQL As String

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

' the OPENQUERY function
SQL = " Select a.* from OPENQUERY(MyLinkedServerName, 'Select * from " _
  & "Customers') a"

' the OPENROWSET function
SQL = "SELECT * From OPENROWSET('Microsoft.Jet.OLEDB.4.0','c:\program " _
  & "files\microsoft office\office\samples\northwind.mdb'; 'Admin';'', " _
  & "Customers)"

' the four-part syntax
SQL = "Select * from MyLinkedServerName...Customers"
cn.CursorLocation = adUseClient
cn.Open "Provider=SQLOLEDB;Data Source=(local);Initial Catalog=master;User " _
  & "Id=sa;Password=;"
rs.Open SQL, cn, adOpenStatic, adLockReadOnly
' ...
rs.Close
set rs = Nothing
cn.Close
Set cn = Nothing

As you see, it is a powerful technique, that empowers SQL Server developers with the capability to perform heterogeneous queries, that Access programmers have since a long time.