Columns & Rows

Using the SQL-DMO Object Model from Access

By Rick Dobson, Ph.D.

Microsoft is clearly trying to entice Office developers who use the Jet database engine to evaluate Microsoft SQL Server as a database server for their custom solutions. Microsoft even gives away the Microsoft Data Engine (MSDE) with any version of Office 2000 that includes Access.

The MSDE shipping with Office 2000 builds on SQL Server 7 technology, making it easy to transfer your solution to a more scalable SQL Server version if needed. In addition, Access 2000, with its new Access project data type (.adp), includes new graphical tools for creating SQL Server databases and populating them with tables, views, and stored procedures. It also tightly integrates Access forms, reports, and data access pages with these SQL Server database objects.

Previous Microsoft Office and VBA Developer articles introduced you to Access projects. This article drills down on administering SQL Server databases with the SQL Distributed Management Objects (SQL-DMO) object model. This model is interesting to Office developers for three reasons:

• It allows you to programmatically manipulate SQL Server with a hierarchical object model. Most intermediate and advanced Access developers have substantial experience programming hierarchical object models working with the Office models. This means they can easily come up to speed on SQL-DMO.
• Microsoft used SQL-DMO to program SQL Server's Enterprise Manager. This graphical tool offers capabilities for SQL Server databases similar to those Database window offers for Jet databases. However, Enterprise Manager is not shipped with MSDE. Therefore, developers working with MSDE can build subsets of Enterprise Manager capabilities into their solutions by developing programs with SQL-DMO.
• SQL-DMO exposes COM interfaces. This enables Access developers to program SQL-DMO as easily as creating a reference to its library and manipulating its objects - just as you do the object models of other Office components, such as Excel or Word.

The SQL-DMO Object Model

FIGURE 1 shows selected portions of the SQL-DMO object model. It displays selected objects referenced throughout this article while it presents an overview of the model's structure. At the top of the SQL-DMO model is the Application object. This is the source for all other SQL-DMO objects and collections.

FIGURE 1: Abridged SQL-DMO object model.

Right below the Application object is the SQLServers collection. The members of this collection point at any SQL Server to which your workstation connects. Use a SQLServer object's Connect method to have the object reference a specific SQL Server, such as the one for the local workstation, which can be an MSDE server. In any computing environment, you can have multiple SQL Servers. My office has MSDE on one workstation, SQL Server 7 on a couple of other workstations, and SQL Server 2000 on two more workstations. Mid-sized and large organizations will often have one or more SQL Servers as development servers, and other SQL Servers that service a community of users for normal operating requirements. In these environments, developers sometimes need to copy databases for use on the road. These on-the-road applications often work best with disconnected computers. MSDE can be cost effective for these applications with disconnected computers, and SQL-DMO can help to efficiently administer those database applications.

On any given server, you can have one or more databases. Each SQLServer object has its own Database collection. Within each database are database objects, such as tables, views, and stored procedures. Views and stored procedures roughly correspond to stored queries for Jet databases. The Tables collection in a SQL Server database behaves similarly, but not identically, to the Tables collection in a Jet database. For example, any given table can have columns, keys, and indexes. A Column object enables you to manipulate the properties of a single column in a SQL Server table. You can also add and remove individual columns in a table with the Column object.

SQL Server also supports Key objects that can serve as the primary key and a foreign key for a table. Unlike Jet, SQL Server primary keys can be clustered, so records are physically stored in primary-key order. This greatly expedites retrieval. You have the choice of enabling the Clustered property of any other index for a table. Of course, because records can be physically ordered just one way on a storage device, you can enable the Clustered property of just one index per table.

Getting Started with SQL-DMO

You can work with the SQL-DMO object model from either a traditional Access database file (.mdb), or an Access project file (.adp). This is because you don't necessarily rely on the active connection for an Access project with a SQL-DMO program. Instead, you can forge a connection from your SQL-DMO program to a SQL Server. Throughout the balance of this article, I explicitly reference an Access project, but you should understand that the code works from an Access database file as well.

To program SQL-DMO in a VBA project, you must create a reference in the VBE window with an Access database file or Access project. Choose Tools | References to open the References dialog box. Then select Microsoft SQLDMO Object Library.

You get the library by installing MSDE, SQL Server 7, or SQL Server 2000 on a workstation. There are some slight incompatibilities in terms of SQL-DMO between these versions. The file you designate when you reference the SQL-DMO library is sqldmo.dll. A file with this name loads when you install either SQL Server 7 or SQL Server 2000. You get the DLL file whether you load the server and client tools, or just the client tools. While the DLL file has the same name, it's actually different in the two SQL Server versions. This is because SQL-DMO for SQL Server 2000 is a superset of the version for SQL Server 7. Microsoft programmed the two DLL files so that SQL-DMO running on a computer with SQL Server 2000 can run the SQL-DMO program on a computer running SQL Server 7. However, the reverse is not the case. MSDE loads the same DLL file as the one that ships with SQL Server 7. If your applications work in an environment with SQL Server 7 and SQL Server 2000 servers, consider developing SQL-DMO code on SQL Server 7, so computers with the DLL for SQL Server 2000 can run them.

The Help file for SQL-DMO is named sqldmo.hlp for SQL Server 7, and sqldmo80.hlp for SQL Server 2000. Recall that Help is different between the two versions since the SQL-DMO model is not identical across the versions. You can also access the Help file from the Object browser in the VBE window of a VBA project by pressing [F1] with your cursor resting on a SQL-DMO object in the Code window.

A typical program that uses SQL-DMO will manage something on a SQL Server. This can be as simple as counting the items in a collection, or as complex as creating new objects and managing security. It's normal to require a connection to a SQL Server for these tasks. Your SQL-DMO program can authenticate itself to the SQL Server as a SQL Server login or by the Windows NT or Windows 2000 user ID of the user running the SQL-DMO program. The SQL Server login name is equivalent to the User name field value on a Data Link Properties dialog box, or the user ID term in a SQL Server connection string. When you use a SQL Server login, your program can specify the login and password without having to rely on a user's Windows ID, which may not have permission to perform the tasks programmed with SQL-DMO. If the security of the login name and password is an issue, you can encrypt the contents of the Code window by transforming the .mdb or .adp file to a .mde or .ade file. Alternatively, you can place password security on the viewing of a VBA project associated with an Access database or project file.

A SQL-DMO program must perform three steps to connect to a SQL Server. First, it must declare a SQLServer object with a Dim statement. The following statement declares srv1 as a SQLServer object:

Dim srv1 AsSQLDMO.SQLServer

Second, the program must instantiate the object representing the SQL Server. This is done in the typical way for instantiating any object with a Set statement and a New keyword. The format for the instantiation of a SQLServer object is:

Set srv1 = New SQLDMO.SQLServer

Third, the program must connect to a SQL Server. Your program can accomplish this with the Connect method. When using a SQL Server login, the Connect method takes three arguments: server name, login name, and password. You use a string variable to express each argument. The format for the Connect method in this context is:

srv1.Connect "server_name", "login_name", "password"

Counting and Enumerating Tables

Listing One is a simple SQL-DMO program that performs three tasks. (This and all other source discussed in this article is available for download; see end of article for details.) First, it connects to a SQL Server. Second, it counts the items in the Tables collection of the NorthwindCS database on the server. The NorthwindCS project file is one of the sample files for Access 2000 you can install with Office 2000. When you initially open the Access project file, it automatically installs the NorthwindCS database on computers running MSDE. On a computer with SQL Server 7 or SQL Server 2000, the project connects to the Northwind database on the server. If your NorthwindCS project connects to the SQL Server version of the Northwind database, then replace the references to the NorthwindCS database with references to the Northwind database in the article. Third, the SQL-DMO program enumerates all the user-defined tables in the Tables collection. For each enumerated table, it shows the number of columns, keys, and indexes. The enumeration specifically excludes any tables defined by the SQL Server system.

The first Dim statement in the listing declares srv1 as a SQLServer object. After instantiating the object, the program uses it to connect to the "cabxli" server. When you apply this code in your computing environment, you will need to change this to a server name that is available on your LAN.

The first Debug.Print statement after connecting to the "cabxli" server contains an expression that counts all the tables in the NorthwindCS database. The expression for computing the count of tables in the database is:

srv1.Databases("NorthwindCS").Tables.Count

This expression walks the SQL-DMO object model. It starts with a SQLServer object, srv1. Then, it references a specific database, namely the NorthwindCS database, from the Databases collection on that server. Within the NorthwindCS database, the expression designates the Tables collection. Finally, it returns the number of items in the Tables collection with the Count property.

The Debug.Print statement containing the expression to count the tables performs a couple of tasks. First, it embeds the return value from the Count property in a sentence. This makes it easy to determine what the expression returns. Notice the count is for system-defined and user-defined tables. The Database window in an Access database file or Access project shows the user-defined tables by default. You can change this by selecting the System objects check box on the View tab of the Options dialog box. The Debug.Print statement prints the column headings for the user-defined table names along with the item counts of the Columns, Keys, and Indexes collections for the tables.

The For loop after the first Debug.Print statement passes through all the tables in the NorthwindCS database. The Dim statement for tbl1 at the beginning of the procedure isn't strictly necessary, but it enables IntelliSense for the tbl1 object. Without this Dim statement, a developer would have to know the precise spelling and syntax for properties and collections of the Table object referenced within the loop. IntelliSense automatically identifies that the return value of the SystemObject property is a Boolean value. When its return value equals False, the tbl1 table is user-defined. The loop prints the table's name along with the count of items in the Columns, Keys, and Indexes collections for the current user-defined table. The string arithmetic with the table's name helps align the columns independently of the number of characters in a table's name allowing a neat debugging window. Adjust the value of intMaxLength so it is greater than the number of characters in the longest table name.

The procedure concludes by cleaning up the objects that it uses. First, it disconnects the "cabxli" server and then it sets the srv1 pointer equal to Nothing. This releases the resources assigned to the pointer. Notice that it does not set tbl1 equal to Nothing. The procedure never instantiated the object, so there's no instance of it to remove from memory.

Enumerating the Columns for Each User-defined Table

The hierarchical nature of the SQL-DMO object model and the capability of VBA to nest For Each statements makes it easy to enumerate the items of one collection within another. Listing Two reveals a pair of procedures that enumerate all user-defined tables within a database. For each user-defined table, the code sample provides the name of each column in the table along with selected properties for the columns, such as its data. Studying the data types for table columns in the NorthwindCS database is one way to learn the equivalences between SQL Server and Jet data types. SQL Server replaces the Jet counter data type with an Identity column. Identity columns have a data type that translates to an integer. It is common to represent them with an int SQL Server data type, which corresponds to the Long Integer data type in Jet. A column with an Identity property of True has IdentitySeed and IdentityIncrement properties for specifying the starting value of the counter and the amount by which the counter grows with each step. The default value for IdentitySeed and IdentityIncrement property settings is 1.

The procedures in Listing Two are interesting for another reason. They demonstrate how to generalize the return set from a SQL-DMO program for any database on any server. While Listing One specifically addressed the NorthwindCS database on the "cabxli" server, the procedures in Listing Two work readily for any database on any server to which a workstation can connect. The first procedure defines two string variables, srvname and dbsname, and passes their values by reference to the second procedure. In addition to manually revising the values, you can readily adapt the code so fields on an Access form provide the values. In fact, it's even possible to enumerate a specific set of servers to which a workstation can connect as well as the databases on the server. By supplying the server and database names as the sources for two combo boxes on a form, you can let a user choose the server name and the database name from a list of legitimate values.

The second procedure starts by declaring and instantiating a SQLServer object that connects to the server name passed to it from the first procedure. You can further generalize the sample application by passing the login name and password as strings from the first procedure.

The second procedure uses two nested For Each statements. The outer loop passes through all the items in the Tables collection from the database and server specified in the first procedure. If it finds a table with a SystemObject property value of False, then the current table is a user-defined table. When the outer loop discovers the current table is user-defined, it prints the table's name and the header row for reporting values for the enumerated columns from the inner For loop. This inner loop passes through all the columns in the current table. For each column, it prints the name and list of property values.

This simple program makes it a snap to report on the tables in any database. You can readily add additional filters to restrict the output in different ways and remove the filter for system-defined tables to show the names of system-defined tables along with their column names and properties as well.

Creating a Table

In addition to enumerating SQL Server objects, you can also create them with SQL-DMO. This is convenient for all kinds of purposes. For example, manually creating an identical table on two more databases can be tedious and error-prone. You can alleviate the tedium by programmatically creating tables and you will be certain the tables have the same design. Another benefit of programming a table is that you can easily see all the elements of a table's design in the script. This is particularly useful for table design features, such as constraints and indexes, which do not appear on the visual designer for tables in Access projects.

Listing Three presents a pair of procedures for creating a table in any database on any server to which a workstation can connect. Just by changing either server name or database name in the first procedure, a user can change the location for the creation of the new table. After specifying the new table's destination, the first procedure concludes by invoking the second procedure and passing along the destination parameters. The second procedure completes four special tasks to add a new table to the destination. First, it declares a Table object and related objects, such as Column and Key objects, for the table's design. Second, it instantiates the Table object, tbl1, and assigns a value to its Name property to identify the table. Third, it defines the table's design. This will always involve adding columns to the table. Other design elements are optional. In this case, the table's design includes the specification of a primary key. If you want to permit the manual entry of data to a SQL Server table, you must designate a primary key. Fourth, it appends the table to the Tables collection of the destination database on the server designated in the first procedure.

The table creation process takes place in the second procedure. After the object declarations, this procedure forges a connection to the server name passed to it from the first procedure. This connection ultimately permits the addition of the new table to the target database. The first procedure passes the name of this database.

The first two lines of code after the program connect to a server instantiate and name the new Table object. Its Name property setting is TableNames. After instantiating a pointer variable, such as tbl1, you can start to populate the table it represents with columns and other elements. The pointer variable's name is your point of contact with the table as you design it. The table's Name property is not relevant to this task. However, the Name property setting appears when you enumerate tables and list their names.

The next two blocks of code create a new column and add it to the table. The first block begins by instantiating the column as col1. Then it assigns properties to the column. Since this column acts like a counter field, the code gives it an int data type and sets its Identity property to True. The IdentitySeed and IdentityIncrement property settings are defaults. You are not restricted to a seed of 1 or an increment of 1. The second block of code consists of one statement that appends the defined column to the tbl1 table.

After adding the first column with two blocks of code, the procedure repeats the process for the second column. The addition of the second column again requires two blocks of code. The first block begins by instantiating col1 as a new Column object. Re-instantiating col1 makes it possible for you to assign the Column object new property settings and append it to tbl1 as a new column. The second block adds the new column to the table. This second column stores table names from a target database. Therefore, its properties must be consistent with those for a table name in SQL Server (see the SQL Server Books Online documentation for these specifications). This column allows null values so you can create a row for a table name that you specify later.

To finish the table's design, the program needs to designate a primary key. Again, this is a two-step process. First, the procedure instantiates and defines the Key object, key1. Part of the key's definition is its designation as a primary key. Second, the procedure appends key1 to the table's Keys collection. After instantiating key1 as a Key object, name it for future reference. Then, assign the constant name SQLDMOKey_Primary as its Type property setting. IntelliSense makes specifying the constant as easy as picking an item from a list. To optimize the performance of the primary key for the table, set the key's Clustered property to True. Since you can base a primary key on one or more columns, Key objects have a KeyColumns collection. The procedure uses the Add method for the KeyColumns collection to make ColID the primary key. Notice that the syntax uses a string value. In fact, it is the Name property setting for the table's first column. After defining these key settings, the procedure appends key1 to the tbl1 object's Keys collection. This is the second step that completes the addition of the primary key to the table.

After fully defining the table, the procedure takes one more step. It appends tbl1 to the Tables collection for the database on the server designated in the first procedure. The execution of the Add method for the Tables collection makes the TableNames table available in the database. At this point, users can manually enter data into the table. Just before closing, the procedure frees resources for objects used throughout the table creation process. Since the procedure instantiates objects named tbl1, col1, and key1, it is good practice to set the pointer variables for these objects to Nothing to free the resources. Next the procedure releases the srv1 resources as in preceding samples.

Populating a Table

The preceding sample creates a table for storing data, but it doesn't add data to the table. This is primarily because the sample highlights SQL-DMO programming techniques. A second reason is that the SQL-DMO model doesn't include an object that supports data access and updating. However, you can achieve this functionality with ActiveX Data Objects (ADO). In particular, the Recordset object provides cursors for reading and writing to data in a data source. The Recordset object requires two inputs. These are a data source for the recordset to offer read/write support through its cursor, and a connection to the data source. The TableNames table created in Listing Three can serve as the source. An ADO Connection object referencing the server and database for the TableNames table can provide a path to the source for the Recordset object.

Listing Four demonstrates how to apply these guidelines for populating a table. The listing uses two procedures. The first designates a server name and a database name before calling the second procedure and passing it the designated server and database names. This second procedure connects to the data source twice, once through SQL-DMO's Connect method, and again through an ADO Connection object. The connection through SQL-DMO makes it possible to loop through the names of user-defined tables in a database. The ADO Connection object provides a connection for the ADO Recordset object. This second connection provides a route for adding records to the TableNames table. Since the SQL-DMO connection doesn't work in ADO, and the ADO Connection does not work in SQL-DMO, you need two connections to the same database for this task.

After establishing the two connections, the second procedure instantiates rst1 as a Recordset object before opening the recordset on the TableNames table. The table remains empty at this point. In the final block of code before object cleanup, the second procedure loops through the items in the Tables collection of the database and server named in the first procedure. The For Each statement loops on the SQL-DMO connection to the database. The loop filters out system-defined tables so it adds only the names of user-defined tables to the TablesNames table. The AddNew and Update methods for the Recordset object permit each pass through the filter to add a new record to TableNames. Each new record has a TableName column value equal to the return value from the Name property for the current Table object in the loop.

With the approaches illustrated in Listings Three and Four, you can readily build and populate tables that serve as data sources for combo boxes showing the items in the collections within a SQL Server database. As long as the items don't change frequently, saving the items in a table is more advantageous than creating them each time that you need them. You can always refresh the table's contents on demand or at set intervals.

Conclusion

SQL-DMO is a hierarchical object model for administering Microsoft SQL Server and its databases. You can programmatically do just about anything with SQL-DMO that you can do with Enterprise Manager. Office developers can tap these vast powers from the VBA project associated with any Office file, such as an Access database file (.mdb) or an Access project (.adp). All you need to do is reference the SQL-DMO object library from the project. You can obtain the SQL-DMO library file without charge by installing the MSDE that ships with any Office 2000 version that includes Access 2000.

This article shows how easy it is to enumerate the items in various database collections with a special focus on tables. It also reveals that you can create new database objects, such as tables, with SQL-DMO. It closes with a sample demonstrating how to use SQL-DMO and ADO together to populate a table with item names from a collection within a database.

Rick Dobson, Ph.D., and his wife, Virginia, operate a development and training consulting practice. Their practice specializes in SQL Server, Access, Office, and Web development. Rick is the author of two best-selling books: Programming Microsoft Access 2000 for Microsoft Press, and Professional SQL Server Development with Access 2000 for Wrox Press. Rick and Virginia jointly host national seminar tours on Access and SQL Server development. Rick contributes regularly to numerous computer periodicals and Web sites. Virginia is a frequent contributor to Microsoft Office & VBA Developer. You can reach Rick or Virginia at either of the two Web sites that their practice maintains (http://www.programmingmsaccess.com and http://www.cabinc.net).

Begin Listing One - Simple SQL-DMO program

Sub count_all_tables_enumerate_user_defined_tables()
  
  Dim srv1 As SQLDMO.SQLServer
  Dim tbl1 As SQLDMO.Table
  Dim strName As String
  Dim intMaxLength As Integer
  
  ' Instantiate srv1 object and connect to the cabxli
  ' server with a login of sa and a blank password. 
  Set srv1 = New SQLDMO.SQLServer
  srv1.Connect "cabxli", "<your_user_name>", "<your password>" 
  
  ' Print message for count of system and user-defined
  ' tables; also print header for info on user-defined
  ' tables. 
  intMaxLength = 20
  Debug.Print "Count of user-defined and " & _
    "system-defined tables in the NorthwindCS " & _
    "on the cabxli server is " & _
    srv1.Databases("NorthwindCS").Tables. _
    Count & "." & vbCr & vbCr & _
    "Name" & String((intMaxLength - 4), " "), _
    "Columns", "Keys", "Indexes" 
  
  ' Enumerate user-defined tables with name, and
  ' count of columns, keys, and indexes. 
  For Each tbl1 In srv1.Databases("NorthwindCS").Tables
     If tbl1.SystemObject = False Then
      strName = tbl1.Name & _
         String(intMaxLength - Len(tbl1.Name), " ") 
       Debug.Print strName, tbl1.Columns.Count, _
        tbl1.Keys.Count, tbl1.Indexes.Count
     End If
  Next
  
  ' Cleanup. 
  srv1.Disconnect
  Set srv1 = Nothing
End Sub

End Listing One

Begin Listing Two - Enumerate columns

Sub call_enumerate_all_columns_in_all_tables()
  
  Dim srvname As String
  Dim dbsname As String
  
  ' Designate server name and database for which
  ' you want to list the columns in all tables. 
  srvname = "cabxli" 
  dbsname = "NorthwindCS" 
  ' Call the procedure to enumerate the tables. 
  enumerate_all_columns_in_all_tables srvname, dbsname
End Sub
  
Sub enumerate_all_columns_in_all_tables( _
  srvname As String, dbsname As String)
  
  Dim srv1 As SQLDMO.SQLServer
  Dim tbl1 As SQLDMO.Table
  Dim col1 As SQLDMO.Column
  Dim strName As String
  Dim intMaxLength As Integer
  
  ' Instantiate srv1 object and connect to the designated
  ' server with a login of sa and a blank password (revise
  ' login and password, if appropriate). 
  Set srv1 = New SQLDMO.SQLServer
  srv1.Connect "cabxli", "<your_user_name>", "<your password>" 
  
  ' Loop through all tables in the designated database. 
  intMaxLength = 20
  For Each tbl1 In srv1.Databases(dbsname).Tables
     If tbl1.SystemObject = False Then
       Debug.Print tbl1.Name & vbCr & "Column Name" & _
         String((intMaxLength - 10), " "), "Data Type", _
         "Allows Nulls", "Identity", "Seed", "Increment", _
        "In PK" 
      ' Loop through all columns for any user-defined tables. 
       For Each col1 In tbl1.Columns
        strName = col1.Name & _
           String(intMaxLength - Len(tbl1.Name), " ") 
         Debug.Print strName, col1.DataType, _
          col1.AllowNulls, col1.Identity, _
          col1.IdentitySeed, col1.IdentityIncrement, _
          col1.InPrimaryKey
       Next
       Debug.Print
     End If
  Next
  
  ' Cleanup. 
  srv1.Disconnect
  Set srv1 = Nothing
  
End Sub

End Listing Two

Begin Listing Three - Create TableNames

Sub call_create_TableNames_table()
  Dim srvname As String
  Dim dbsname As String
  
  ' Designate server name and database for which
  ' you want to list the columns in all tables. 
  srvname = "cabxli" 
  dbsname = "NorthwindCS" 
  
  create_TableNames_table srvname, dbsname
End Sub
  
Sub create_TableNames_table(srvname As String, _
  dbsname As String)
  
  Dim srv1 As SQLDMO.SQLServer
  Dim tbl1 As SQLDMO.Table
  Dim col1 As SQLDMO.Column
  Dim key1 As SQLDMO.Key
  
  ' Instantiate srv1 object and connect to the designated
  ' server with a login of sa and a blank password (revise
  ' login and password, if appropriate). 
  Set srv1 = New SQLDMO.SQLServer
  srv1.Connect "cabxli", "<your_user_name>", "<your password>" 
  
  ' Instantiate tbl1 as a Table object
  ' and assign its Name property. 
  Set tbl1 = New SQLDMO.Table
  tbl1.Name = "TableNames" 
  
  ' Instantiate col1 as a counter field with an Identity
  ' property value of True; col1 settings support its use
  ' as a PK. 
  Set col1 = New SQLDMO.Column
  col1.Name = "ColID" 
  col1.DataType = "int" 
  col1.AllowNulls = False
  col1.Identity = True
  col1.IdentitySeed = 1
  col1.IdentityIncrement = 1
  
  ' Add col1 to tbl1. 
  tbl1.Columns.Add col1
  
  ' Re-instantiate col1 for re-use with settings for a new
  ' column; col1 settings support storage of table names. 
  Set col1 = New SQLDMO.Column
  col1.Name = "TableName" 
  col1.DataType = "varchar" 
  col1.Length = 128
  col1.AllowNulls = True
  
  ' Add second instance of col1 to tbl1. 
  tbl1.Columns.Add col1
  
  ' Instantiate key1 as primary key for tbl1 that points at
  ' ColID column and add the key to tbl1. 
  Set key1 = New SQLDMO.Key
  key1.Name = "PK_for_TableNames" 
  key1. Type = SQLDMOKey_Primary
  key1.Clustered = True
  key1.KeyColumns.Add "ColID" 
  
  ' Add primary key, key1, to tbl1. 
  tbl1.Keys.Add key1
  
  ' Now that the design for tbl1 is complete add it to the
  ' tables collection of the dbsname database on the srv1
  ' server. 
  srv1.Databases(dbsname).Tables.Add tbl1
  
  ' Cleanup. 
  Set key1 = Nothing
  Set col1 = Nothing
  Set tbl1 = Nothing
  srv1.Disconnect
  Set srv1 = Nothing
End Sub

End Listing Three

Begin Listing Four ­- Populate tables

Sub call_populate_TableNames()
  
  Dim srvname As String
  Dim dbsname As String
  Dim tblname As String
  
  ' Designate server name and database for which
  ' you want to list the columns in all tables. 
  srvname = "cabxli" 
  dbsname = "NorthwindCS" 
  populate_TableNames srvname, dbsname
End Sub
  
Sub populate_TableNames(srvname As String, _
                        dbsname As String)
  Dim srv1 As SQLDMO.SQLServer
  Dim cnn1 As ADODB.Connection
  Dim rst1 As ADODB.Recordset
  
  ' Instantiate srv1 object and connect to the designated
  ' server with a login of sa and a blank password (revise
  ' login and password, if appropriate). 
  Set srv1 = New SQLDMO.SQLServer
  srv1.Connect "cabxli", "<your_user_name>", "<your password>" 
  
  ' Instantiate an ADO Connection object and open it to the
  ' designated database on the designated server for the
  ' designated database. 
  Set cnn1 = New ADODB.Connection
  With cnn1
    .Provider = "sqloledb" 
    .ConnectionString = "data source = " & srvname & _
      ";" & "Initial Catalog = " & dbsname & ";" & _
      "user id = <your_user_name>; Password=<your password>" 
    .Open
  End With
  ' Instantiate Recordset object and open
  ' for TableNames table. 
  Set rst1 = New ADODB.Recordset
  rst1.Open "TableNames", cnn1, adOpenKeyset, _
    adLockOptimistic, adCmdTable
  
  ' Loop through user-defined tables in the designated
  ' database and add them to the TableNames table. 
  For Each tbl1 In srv1.Databases(dbsname).Tables
     If tbl1.SystemObject = False Then
      rst1.AddNew
      rst1("TableName") = tbl1.Name
      rst1.Update
     End If
  Next
  
  ' Cleanup. 
  srv1.Disconnect
  Set srv1 = Nothing
  rst1.Close
  Set rst1 = Nothing
  cnn1.Close
  Set cnn1 = Nothing
End Sub

End Listing Four