The SQL Distributed Management Objects (SQL-DMO) model exposes objects, methods, properties, and events through a COM interface to control SQL Server administrative tasks. SQL-DMO has a COM interface, so Visual Basic for Applications (VBA), Visual Basic (VB), and C++ developers can readily use SQL-DMO to build administrative tasks into their custom applications. This article is the first in a series about SQL-DMO programming. The series' objective is to give Microsoft Access and other developers a good grasp of SQL-DMO so that they can program custom solutions that contain administrative functions for SQL Server.
SQL-DMO is particularly appropriate for developers who work with the Microsoft SQL Server Desktop Engine (MSDE) that ships with Access 2000. This database server is a scaled-back version of SQL Server 7.0. Although MSDE provides less functionality than SQL Server 7.0, MSDE ships without charge as part of Microsoft Office 2000. However, the MSDE version that ships with Office 2000 doesn't include Enterprise Manager. So, MSDE users don't have the benefit of a GUI with which to manage database objects. (For more information about differences between MSDE and SQL Server, see "What Is MSDE?" February 2000.) In this article, you'll see some approaches to enumerating SQL Server objects with SQL-DMO. You'll also learn how to empower users to explore SQL Server tables and their columns from an Access form.
Installing the MSDE that ships with Office 2000 automatically installs sqldmo.dll, which implements SQL-DMO for use with SQL Server 7.0 and MSDE. However, Office 2000 doesn't include the SQL-DMO Help file, sqldmo.hlp. Fortunately, developers working with MSDE have other alternatives. For one, if you have a spare client license for SQL Server 7.0, you can install the client-management tools; this installation process also implements sqldmo.hlp. Another alternative, SQL Server 7.0 Books Online (BOL), offers many Help pages on SQL-DMO. Also, a couple of articles can help you understand SQL-DMO programming better: Jason Fisher, "SQL-DMO Picks Up Where Enterprise Manager Leaves Off," February 2001, and my article "Programming SQL-DMO from Access," July 2000, which introduces SQL-DMO for Access developers.
Enumerating Database Specifications
Before you run the listings in this article, you must start an Access 2000 project that references the SQL-DMO library. You can use the SQL Server Database Wizard to create a new SQL Server database for your project. Then, in the Database window, select Objects, Modules in the Objects, and click New to open an empty VBA project. From the Visual Basic Editor window, choose Tools, References, Microsoft SQLDMO Object Library. Then, close the References dialog box. Your VBA project is now ready to run the listings in this article.
Listing 1 shows a basic SQL-DMO program that prints specifications for all the databases on a server. The code creates a list that correlates each database with its creation date, as well as with the number of tables, views, and stored procedures within the database. After declaring a SQLServer object from the SQL-DMO library, the program instantiates the object, then connects it to the cabxli server with a systems administrator (sa) user ID (UID) and a blank password. (Of course, you need to change the server name, UID, and password so that they are appropriate for your needs.) Generally when you develop with SQL-DMO, you need a UID with broad server permissions because you use SQL-DMO to program administrative functions. (For more information about security, see my columns "Access Granted," December 2000, "Securing SQL Server Tables," January 2001, "Homegrown Security Solutions," February 2001, and "Programming SQL Server Security," March 2001.)
Next, the code in Listing 1 scans a server's databases to calculate a value that's 1 greater than the number of characters in the longest database name. The code uses an error trap to determine this value by estimating the length of the longest database name. The error trap extends the current estimate until no name is larger than the estimate. The application can align the information for all rows by knowing the longest database name, even when the database names on different rows are of substantially different lengths. Before using this maximum length for database names, the application prints a column-header row to describe the database rows. This line's expression confirms how to use the maximum length to distribute values across the Immediate window through the Debug object's Print method. Choose View, Immediate Windows to see the Immediate window.
The code's final loop looks through the databases again to gather the print information for each row. The Do loop reuses the Databases collection expression saved in obj1 to enhance performance. Within the loop, the syntax for the Print method's argument shows that the Databases collection has other collections that are hierarchically dependent on it. These collections determine how the code in Listing 1 computes items such as the number of tables.
Using SQLObjectList Objects
VBA and VB developers generally have substantial experience working with the members of hierarchically related collections. However, SQL-DMO lets you map the members of many collections by using a basic element called a SQLObjectList object. This object shows the names of SQL Server component members without exposing the collection's actual members. Working with SQLObjectList objects is preferable to working with the collection's members when you need only a simple enumeration of SQL Server database object names. If you need to manipulate the members or need information other than names, the SQLObjectList object isn't appropriate.
SQL-DMO provides a variety of ways to return SQLObject-List objects. One of the most versatile is the ListObjects method. With this method, you can return lists for user-defined tables, views, and stored procedures, as well as all database objects including user-defined and system-defined objects. The items in a SQLObjectList object need not all have the same type, as the items in a collection must. Fourteen SQL-DMO methods return SQLObjectList objects for SQL Server elements such as columns, keys, and permissions. Refer to BOL for information about all the ways to return SQLObjectList objects.
Listing 2 displays two procedures that apply the ListObjects method to return a SQLObjectList object. The procedures use the object to enumerate all of a database's user-defined tables. The first procedure specifies the database name and prints the list that the second procedure, which is a function procedure, returns. Listing 2 works with the cabxli server. You must change both the server and database name specifications so that they're appropriate for your environment. In this example, the ListObjects() method applies to the database collection member you designated in the first procedure. The method's argument instructs it to return all user-defined tables. The function procedure uses the For...Each loop to iterate through the list items as objects, but the function appends the items to a string variable, str1. The line immediately after the loop strips the "; " delimiter from str1 for the next list item name. (You could use a list in this format to designate the elements in a list box or combo box on an Access form.)
Creating a Database Objects Explorer
Figure 1 shows an Access form serving as an explorer for SQL Server tables and their columns. (To download this form, see the More on the Web box.) The combo box shows a list of tables within a server's database. After the user chooses a table, the application populates the list box with the selected table's column names. If the user chooses another table from the combo box, the form automatically updates the list box's contents to the newly selected table's column names.
The form contains a label control along with two other unbound controls: a combo box and a list box. Procedures in a programming module behind the form control the behavior and contents of the combo box and list box controls. Listing 3, which shows the code that produces Figure 1's Access form, contains three main components: a general area and two event procedures. Callout A in Listing 3 marks the general area, which precedes the Form_Load() and Combo0_AfterUpdate() event procedures. The general area includes some constant and object declarations. You need to change the constant declarations so that they contain the names of a server and a database in your environment. Users can specify the database they want to use, but I kept the code simple for tutorial objectives.
The object declarations work in any environment. By declaring objects in the general area, the application makes them available to all the module's procedures. The OpenConnection() subprocedure doesn't need to reside in the general area. Placing the subprocedure's code outside both event procedures reinforces the idea that the procedure assigns object references that any procedure in the module can use.
The Form_Load() event procedure in Callout B in Listing 3 runs before the form loads. This event procedure displays a message that says the procedure is preparing the explorer for use. This message serves as a subtle warning that the form takes several moments to open. Connecting the srv1 object reference to the database server and assigning the dbs1 and cols1 object references take up most of the wait. These activities occur in the OpenConnection() subprocedure.
The OpenConnection() subprocedure assigns a value to the cols1 object reference, in the same way as the Combo0_AfterUpdate() event procedure in Callout C in Listing 3 performs that function. Making the object reference assignment in the OpenConnection() subprocedure substantially reduces the time required to make the assignment initially in the Combo0_AfterUpdate() event procedure. The design assumes that waiting for a form to open is better than waiting after a form is opened and supposedly ready for use. Besides, you must update the cols1 collection in the Combo0_AfterUpdate() event procedure to reflect the table that the user selected.
After calling the OpenConnection() subprocedure, the remainder of the Form_Load() event procedure populates the combo box with the target database's table names. The target database's name is in DbsName. The code assigns a SQLObjectList object with all the user-defined table names in the obj1 object reference. Then, the code iterates through the list items and creates a string in a format suitable for the Combo0 RowSource property. The event procedure concludes by making two property assignments for the combo box. The first property assignment tells the control how to interpret the table- name list, and the second assignment is the list of table names.
The Combo0_AfterUpdate() event procedure automatically runs immediately after a user makes a selection from the combo box. This event procedure performs two tasks. First, it compiles a list of column names for the table that the user selected. Second, it assigns that list as the source for the list box. Then, the procedure iterates through the Columns collection members for the selected table because a SQLObjectList object has no way to return only column names.
Basic Skills
These examples explain the basic skills for using SQL-DMO in custom SQL Server solutions and give you a simple explorer for the table and column names in a database. Understanding SQL-DMO programming is particularly important for MSDE developers, who can't expect that their custom-application clients have access to Enterprise Manager or other client-management tools.
