Running a Distributed Query with Visual Basic Using ADO - Running a query against the Linked Server
(Page 3 of 4 )
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.
Next: Probing the Linked Server >>
More Visual Basic.NET Articles
More By Jayaram Krishnaswamy
