Running a Distributed Query with Visual Basic Using ADO

Introduction

In a series of tutorials on ADO (visit the ASPFREE.Com web site and search), the power and extent of the ActiveX Data Objects was described. Although this series is far from complete, readers of this tutorial can benefit from reviewing the previous tutorials first. In this tutorial a connection will be established to the SQL Server and, using an ADODB connection and recordset objects, a series of stored procedures on the server will be executed. These procedures add a named linked server to the Linked Servers folder on the SQL Server and also set up logins. The tutorial, by way of a Visual Basic (ver 6.0) project, also shows how certain properties of the linked server(s) can be obtained.

Create a Visual Basic Standard EXE Project

Create a standard EXE project. In this tutorial it is called LinkServer.vbp. Create a form similar to the one shown in this picture. It has three command buttons and four radio buttons.

The data grid in the above image is added after adding the Data Grid Control. You can add this ActiveX Control MSDATGRD.OCX to your toolbox from Project-->Components and choose the Data Grid Control 6.0 after scrolling in the list as shown. After you add this to the toolbox, drag and drop it on the form and resize it to your requirements.

Since you will be calling upon the ADODB objects you need to add a reference to the ADO library before you run any code, otherwise you will get a program error. You can add a reference to the ADO library by going to Projects-->References... which opens up a dialog from which you choose, just like you did for the components, by scrolling down the list and placing a check mark against the item as shown. You may or may not have this version, but at least it should be version 2.1. You may download the latest version from Microsoft's web site. For download links refer to my previous tutorials, or search on the Internet.

Adding a linked server to the SQL 2000 server

The code shown in the next paragraph allows you to make the connection to the SQL 2000 Server and run two stored procedures in the SQL Server. In the present tutorial, the author being the owner (admin) of the computer and the dbo of the database, things will work without a problem. The author is also authenticated by the Windows authentication for the SQL 2000 Server. If your situation is different, you may need to adjust the code.

Insert the code shown in the click event of the Command1 button. Also add the two declarations at the top of the form's code in the (Declarations) area of the code page. Code completion is aided by intellisense; just insert the object name and a period and you should see a pick list drop giving you all the relevant methods and properties.

Once you have the connection string you can open a connection. With the connection open you can execute command texts against the database. In this case they are the two stored procedures, sp_addlinkedserver which adds a linked server called "GoodLink" whose data will be in the external source, which is a Microsoft Access database. The Microsoft Access database directory location is known. The "GoodLink" server will be added to the Link Servers on the "XPHTEK" SQL Server 2000. Further you will be accessing this server (=this MS Access database) with the username: admin and an empty string for password. The Product Name, which is the second argument after the linked server name, appears to be mandatory, but can be anything ("trash" has been used to show that it does not matter).

Dim adocon as New ADODB.CONNECTION
Dim adors as New ADODB.Recordset
Private Sub Command1_Click()
Dim connstr As String
connstr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" _
& "Security Info=False; Initial Catalog=Northwind;" _
& "Data Source=XPHTEK"
adocon.Open connstr
adocon.Execute "Exec sp_addlinkedserver" _
& "'GoodLink', 'trash', 'Microsoft.Jet.OLEDB.4.0'," _
& "'C:Documents and Settingscomputer userMy DocumentsNorthwind.mdb'"
adocon.Execute "Exec sp_addlinkedsrvlogin" _
& "'GoodLink', false, NULL, 'Admin',''"
MsgBox ("GoodLink Server added")
adocon.Close
End Sub 

After running this code you may verify in the SQL Server 2000 Enterprise Manager that the "GoodLink" server is added to the Linked Servers folder as shown.

The security tab has taken the values in your code as shown in the next picture. The product name is a required item, but can be anything, it appears.

Running a query against the Linked Server

You can run queries with two kinds of command text. The four-part syntax to access all the columns from the Employee table in the linked server is: 'SELECT * from GoodLink...Employees'. Although this is supposed to consist of  four parts including  catalog and schema, these are empty, as Microsoft Jet does not support these objects.

The other method is to use the openquery() function which takes the name of the linked server, and a query that can be run against that linked server in a string format. You may not pass variables to this query. Both are shown in the code.

Once the connection is open you set some of the ADO Recordset properties such as CursorLocation and LockType. With these, and using the connection string, you can open the recordset as shown in the code. The commented code will write just one column of the Employees table to the immediate screen, stepping through all the records until it reaches the end. If you set the Datasource property of the DataGrid to the recordset, the data grid is automatically filled with all the columns and rows from the Employees table. When you are writing the code, make sure you use the intellisense feature; it simplifies coding, as shown here.

Private Sub Command2_Click()
Dim connstr As String
Dim strsql As String
'strsql = "SELECT * from GoodLink...Employees"
strsql = "Select * from openquery" _
& "(GoodLink, 'Select * from Employees')"
connstr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" _
& "Persist Security Info=False; Initial Catalog=Northwind;" _
& "Data Source=XPHTEK"
adocon.CursorLocation = adUseClient
adocon.Open connstr
if adors.state=1 then
adors.close
end if
adors.Open strsql, connstr, adOpenStatic, adLockReadOnly
'While Not adors.EOF
'Debug.Print adors.Fields(0).Value
'adors.MoveNext
'Wend
Set DataGrid1.DataSource = adors
adocon.close
End Sub

When you run the form and click on the Command2 button you will see the following screen. After the first time you click on the Command1 button you must not click on this button (Command1) again as it will try to create an existing object, and VB objects, by producing a run time error. If you want, you can execute another stored procedure to drop the object when the form loads, or better still drop it from Enterprise Manager. However, you will not be creating the linked server every time, so you may arrange for the Command1 button to be inactive.

Probing the Linked Server

Collected in the click event of the Command3 button are four of the stored procedures that can be run on the SQL Server 2000. They show some of the details about the Linked Server, its table, columns, Primary Keys, and so forth. This is not a complete list, but please refer to the SQL 2000 Server Book-on-line for other related stored procedures.

Private Sub Command3_Click()
'Choose the option and send the strsql to the sqlst Procedure
If Option1.Value = True Then strsql = "Exec sp_helplinkedsrvlogin" 

Call sqlst (strsql) ElseIf Option2.Value = True Then strsql = 

"Exec sp_tables_ex 'GoodLink'" Call sqlst (strsql) ElseIf Option3.Value = 

True Then strsql = "Exec sp_Columns_ex GoodLink, 'Employees' " Call sqlst 

(strsql) ElseIf Option4.Value = True Then strsql = "Exec sp_primarykeys 

GoodLink, 'Order Details' " Call sqlst (strsql) End If
End Sub

Sub sqlst (ByVal strg As String)
'sqlst passed the string to the recordset open() method as 
'an argument. Otherwise it is similar to the Command2 code
'described earlier. 
Dim connstr As String
Dim strsql As String
connstr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" _
& "Persist Security Info=False; Initial Catalog=Northwind;" _
& "Data Source=XPHTEK"
If adors.State = 1 Then adors.Close
End If adors.CursorLocation = adUseClient
adocon.Open connstr
adors.Open strg, connstr, adOpenStatic, adLockReadOnly
Set DataGrid1.DataSource = adors
adocon.Close
End Sub

In order to find the information choose one of the radio buttons. For the screen shot shown here, the "Examine the Linked Server" button  was clicked after choosing "Find all tables in the linked server." The grid is filled with the retrieved information as shown in the screen shot.

Summary

ADO objects provide a convenient way to add and manage a linked server on a SQL Server, and allow you to run distributed queries. Only some of the stored procedures were examined, but you may find several more in the Book On Line. While the Openquery() method returned the columns in their natural order, the four-part syntax returned the columns in alphabetical order. In the tutorial presented, Windows authentication for the SQL Server and remote authentication for an unsecured database were used. If these are different in your situation, changes must be made for the code.