Running a Distributed Query with Visual Basic Using ADO
(Page 1 of 4 )
A distributed query is a query run against an external database. Linked servers in SQL servers are virtual servers serving the external data. These can be manipulated from the Enterprise Manager and Query Analyzer in SQL 2000 and by the Microsoft SQL Server 2005 management studio. However, managing linked servers from Visual Basic adds an extra, powerful tool for managing or querying linked server objects on SQL servers. This article shows how you may do this.
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.
Next: Adding a linked server to the SQL 2000 server >>
More Visual Basic.NET Articles
More By Jayaram Krishnaswamy
