Programming SQL-DMO from Access

SQL Distributed Management Objects (SQL-DMO) is SQL Server's object model for building SQL Server management applications. Because SQL-DMO is similar to the Microsoft Office hierarchical object models, many Access developers will feel at home building custom solutions with it. However, the general principles apply to any developer who can program COM objects. SQL-DMO is important for Microsoft SQL Server Desktop Engine (MSDE—formerly Microsoft Data Engine) developers because Enterprise Manager isn't part of MSDE. Whenever you want database users to examine or manipulate a database's structure without the unlimited accessibility that Enterprise Manager offers, SQL-DMO is the best choice for creating custom solutions.

Getting Started with SQL-DMO

Before your applications can use SQL-DMO to manipulate and examine SQL Server databases in an Access project, the applications must have two features. First, the Visual Basic for Applications (VBA) project associated with an Access project needs a reference to the SQL-DMO library file. Without this reference, your code can't manipulate SQL-DMO objects. Second, your application must create a SQLServer object. This special object offers programmatic access to SQL Server database servers and the objects they contain.

To create a reference to SQL-DMO in an Access project, go to the Visual Basic Editor (VBE) menu. To access this menu, double-click any module in the Database window or select any form in the Database window and click the Code icon on the Database toolbar. In the VBE window, start your reference to SQL-DMO by choosing Tools, References. In the References dialog box, select the check box next to Microsoft SQLDMO Object Library, then click OK to form the reference.

A SQLServer object in SQL-DMO points to a database server. A SQLServer object from SQL Server 7.0 can't connect to a SQL Server 2000 database server, but SQL Server 2000's SQLServer object can connect to database servers from either 2000 or 7.0. As you roll out SQL Server 2000 across your organization, your applications will need to account for these differences. Fortunately, building solutions that adapt automatically to either version is fairly straightforward, as the following example demonstrates.

Pointing a SQLServer object at a database server can slow an application while it connects to the server. Therefore, your applications will benefit from reusing a connection after you create it. Two strategies can improve the availability of SQLServer objects by increasing the opportunities to reuse those objects. First, declare a server object outside an individual procedure at the module level whenever possible. This declaration at the module level lets two or more procedures in a module reuse the same SQLServer object. Second, store a reference to the SQLServer object in a Public variable so you can use it later. Doing so extends the SQLServer object's availability beyond the module in which your application declares the object.

Listing 1, which you can download along with the other listings for this article at the top of the page using the "Download the Code" link, presents a model function procedure for creating a SQLServer object in your SQL-DMO applications. The procedure uses a Public statement to declare the SQLServer object outside the procedure. The function's return value reports the connection status with True for success and False for failure. An attempt to connect to a server can fail for several reasons, but the procedure isolates one of these reasons that relates especially to SQL-DMO. If the srvname string variable points at a SQL Server 2000 server from a SQL Server 7.0 server, the attempt to connect will fail. So, the procedure traps this error and presents an error message with a reminder to upgrade the SQL Server 7.0 server to 2000. In other cases—for example, when the connection to the server is down—the logic for trapping errors merely echoes the system error number and description.

A Custom Explorer Application

Figure 1 shows an example application based on an Access form for listing the text of a stored procedure. The procedure displays a pair of drop-down menus—one box enumerates servers and the other lists the stored procedures within the Pubs database on any server a user selects. I've restricted the example to the Pubs database to simplify the code behind the form, but you can easily extend the logic so users can pick any database on any server.

When users open the form, the application determines the servers that it can explore based on the server it runs from. After a user selects a server, the application populates the drop-down menu that lists the stored procedures in Pubs. This drop-down menu displays the four stored procedures that ship with SQL Server and any custom stored procedures you've built in Pubs. After a user selects a procedure from the top right drop-down menu, the main text box presents the selected stored procedure's text.

Listing 2 presents the code behind the form in the example explorer application; the code consists of a constant declaration and three short event procedures. The constant declaration (Callout A) points the form at the Pubs database. You can choose another database name if you didn't install Pubs or if you removed it from your server. Or, you can programmatically assign the value of dbsname to change in response to user input or other environmental factors.

The Form_Open event procedure (Callout B) fires when a user opens the form. This procedure assigns the RowSource property of the cboServerList control, which is the top left drop-down menu. With the drop-down menu's properties dialog box, I set the control's RowSourceType property to Value List so that a simple semicolon-delimited list can designate the contents of the drop-down menu. The server_list subprocedure in Module1 computes this list for the Form_Open event procedure. Module1 is a standalone module in the Modules folder for the VBA project associated with the Access project.

The second event procedure (Callout C) launches when a user selects a server from the top left drop-down menu. This procedure assigns the RowSource property for the cboStoredProcList drop-down menu that lists stored procedures in the Pubs database for the server a user selects. Again, the drop-down menu's RowSourceType property is equal to Value List. Notice that the stored procedure for computing the list passes all server and database names to a VBA procedure in Module1.

The final event procedure (Callout D) copies the text for the selected stored procedure into the box in the form's lower center portion. This procedure starts by setting the focus to the text box that it updates. It then uses the cboStoredProcList control value and the Text property for a SQL-DMO StoredProcedure object to assign a value to the txtStoredProcText control in the bottom center part of the form. The expression for referencing the stored procedure depends on a SQLServer object, srv1. The procedure for computing the list of stored procedures creates the srv1 object by calling the function procedure in Listing 1—which, for this application, resides in Module1.

Listing 3 contains two procedures that support the example application. These two procedures reside within Module1 of the VBA project associated with the Access project. The server_list function procedure uses error-trapping logic to detect whether the server that the procedure runs on has SQL Server 2000 installed. Depending on the answer, the procedure assigns a list of SQL Servers that are running just SQL Server 7.0 or a list of servers running SQL Server 2000 or 7.0. You can customize these lists for your environment. Using the SystemObject property for a StoredProcedure object, the second procedure, stored_proc_list, generates a list composed exclusively of user-generated stored procedures.

The server_list function procedure begins by attempting to make a connection to the cab2000 server, which runs the SQL Server 2000 beta in my test environment. (Update the server name appropriately for your computing environment. Your server must be running SQL Server 2000.) If the Connect method succeeds, the procedure lists server names delimited with semicolons. Here, the first server runs SQL Server 2000, but the second two run SQL Server 7.0. If the Connect method fails, the procedure tests whether the failure resulted from an attempt to connect to a SQL Server 2000 server from a SQL Server 7.0 server; if so, the procedure creates a list of servers running just SQL Server 7.0. If the Connect method failed for any other reason, the procedure echoes the system error number and description in a message box.

The stored_proc_list function procedure is noteworthy for two reasons. First, it demonstrates how to loop through a SQL-DMO collection. Whenever you want a list of the members in a SQL-DMO collection, you can use this general approach—namely, use a For...Each loop that passes through the members of the collection. Second, the procedure illustrates how to use object properties—in this case, the SystemObject property—to control the items that you extract from a collection. The SystemObject property returns False for all user-created objects, such as stored procedures.

This example application illustrates some of SQL-DMO's flexibility and demonstrates how developers can readily build custom solutions for examining and manipulating a database's structure. SQL-DMO lets developers working in any environment that can control a COM object programmatically administer SQL Server and MSDE database servers. You can readily adapt the techniques demonstrated here for other SQL-DMO collections, such as tables, views, logins, and users.