Creating Tables with SQL-DMO

In "SQL-DMO: Learning the Basics," April 2001, I introduced how to use SQL Distributed Management Objects (SQL-DMO) to enumerate database objects. The SQL-DMO model exposes objects, methods, properties, and events through a COM interface to control SQL Server administrative tasks. In this article, I show you how to create tables by using SQL-DMO with Visual Basic for Applications (VBA) in Microsoft Access projects. Because SQL-DMO exposes its functionality through a COM interface, the techniques in this article apply to any programming environment that can manipulate a COM interface.

"SQL-DMO: Learning the Basics" showed how databases reside within SQL Server objects. Similarly, Tables collections reside within databases on a server. Figure 1 shows a graphical overview of a Tables collection within a database. Table objects, in turn, have hierarchical collections and objects. Any table can have a Columns collection, but tables can have only one PrimaryKey object. A table's Keys collection references the table's primary key and foreign key constraints.

Let's examine three SQL-DMO code samples that demonstrate programmatic solutions to successively more sophisticated table design topics: instantiating a table and adding columns, creating a table with a primary key, and creating a table with foreign keys.

Creating a Table and Adding Columns

The VBA code in Listing 1 creates a table named Products, which has four columns. This code contains two procedures that give a general framework for the other code samples in this article. This code also demonstrates specific syntax rules for assigning int, varchar, money, and decimal data types to a table's columns.

The first procedure in Listing 1 specifies arguments that define where to create the table and what columns go in it. The second procedure creates the table and adds columns according to the arguments that the first procedure passes to it. The arguments that designate the individual column properties pass between the first and second procedures as the elements of a parameter array. Because parameter arrays don't require you to designate a precise number of arguments, using a parameter array facilitates the passing of arguments for a variable number of columns with different data types that require a variable number of settings.

Let's work through the code in Listing 1 in more detail. The code at callout A performs two functions. First, the code declares a set of string variable types. Most of these types pertain to general information, such as the names of the SQL Server and the database that contains the table, as well as the login name and password that users will use to create a connection to the server. Using a login name that belongs to the sysadmin fixed server role is a good practice when you create a table that you want to be visible to other users. Callout A designates the SQLMagTablesSQL database on a server named cablat and specifies the sa login with a blank password.

The code at callout B in Listing 1 assigns table-specific values to variables and passes those variables, and the arguments that callout A defined, as arguments to the second procedure. The first procedure passes to the second procedure between two and four arguments to specify each column data type. The ProdID column has an int data type; the ProdName column has a varchar data type. When you specify a varchar or nvarchar data type, you must identify the maximum number of characters that a column's value can have. Callout B in Listing 1 shows a maximum length of 25 characters for ProdName.

The code at callout C in Listing 1 shows the syntax you use to accept the scalar arguments and the elements of the parameter array. This section of code also creates a connection to the server that will store the database. In addition, the code references the specific database that will contain the table on the server. Finally, the code instantiates a Table object, tbl1, and assigns its Name property.

The code at callout D in Listing 1 demonstrates the general approach to defining columns for the table and shows some variations that can occur for alternative data types. The general approach requires three steps. First, your code must instantiate the column. Second, your code must set the column properties by assigning such values as Name and DataType. The procedure in this callout uses the elements of the parameter array to set column property values. The properties can vary according to a column's data type. For example, a column with an int data type requires just two property settings—one for the name and the other for the data type. However, a column with a varchar data type requires setting three properties—Name, DataType, and Length. The third step in creating a column is to invoke the Add method for the table's Columns collection.

The code at callout E in Listing 1 first adds the table to the database you specified in the last line of callout A. Before adding the table, the code removes any earlier version of the table in the database. Then, the code cleans up instantiated objects.

Creating a Table with a Primary Key

Now, let's look at a code example that creates an Orders table that has a primary key based on a column with an Identity property setting. SQL Server table columns that have Identity properties behave as traditional Access table columns with an AutoNumber data type do. This code example resembles the code in Listing 1 in most other ways, except that this sample sets the AllowNulls property to True for the third column.

Listing 2 contains two procedures that create a table named Orders. The Orders table contains an OrderID column followed by OrderDate and ShippedDate columns. The OrderID column serves as the table's primary key, and the ShippedDate column can contain NULL values. The code at callout A in Listing 2 shows the syntax for creating a primary key based on the table's first column. To make SQL Server automatically set the primary key value for new rows, the procedure assigns a value of True to the Identity property. The seed value is 1000 with an increment of 10. After adding the column with an Identity property to the table's design, callout A in Listing 2 instantiates a Key object, key1, and assigns Name and Type properties for key1. The code sets key1's Clustered property to True so that the primary key's unique index is created as a clustered index for the Orders table. Before adding key1 to the Keys collection for the Orders table, you must designate at least one column on which to designate the primary key. The sample uses the col1 object, which has an Identity property setting.

All the columns in Listing 1 and the first two columns in Listing 2 require field values. However, the values in Listing 2's third column, ShippedDate, are different. A ShippedDate value is NULL before an order ships. Therefore, the code at callout B in Listing 2 assigns the value of True to the third column's AllowNulls property. The default value for this property is False.

Creating a Table with Foreign Keys

The Products table from Listing 1 and the Orders table from Listing 2 are in a many-to-many relationship. This relationship exists because any product can be included in more than one order, and any order can have more than one product. To include this relationship in this article's database design, the database needs two design updates. First, you need to redesign the Products table so that it has a primary key. Second, you need to add a new table to the database that links the Products and Orders tables by storing data common to the domains of both tables. For example, you can store the quantity of each product within a line item on an order in a new OrderDetails table that links the Products and Orders tables.

The sample Access project file for this article includes procedures that automate the process of adding a primary key to the Products table. (For download instructions for this file, see the More on the Web box.) To invoke these procedures, run the CallRemoveOriginalProductIDColumnAndAddNewProductIDColumn procedure from the Visual Basic Editor window for the project. This procedure calls two other procedures. The first called procedure removes the original ProdID column from the Products table. The second called procedure adds a replacement ProdID column; this replacement column has an Identity property that has a Key object defined on the column. The Key object has a Type setting for a primary key.

Web Listing 1 shows two procedures for creating the OrderDetails table. This table has foreign keys that link it to the Orders table and the Products table. Web Listing 1 also shows the syntax that you use to base a primary key on more than one column. The code at callout A starts by adding two columns to the table. The third block of code within the callout creates a foreign key. The code for the foreign key starts by instantiating a Key object. After naming the object, the code sets its foreign key Type property. Next, the code adds the Name property for the col1 object to the key's KeyColumns collection. This specification designates the OrderID column in the OrderDetails table as the local column for the foreign key. Next, the code designates the Orders table and its OrderID column as the referenced table and referenced column. After designating these property settings, the code for the first foreign key concludes by adding the key to the Keys collection of the OrderDetails table. The remainder of callout A creates a foreign key that points to the ProdID column in the Products table from the ProdID column in the OrderDetails table.

The code at callout B in Web Listing 1 shows the syntax for basing a primary key on more than one column. This syntax is similar to the approach that callout A takes, except that this time you invoke the Add method for the key's KeyColumns collection once for each column on which you define the primary key. For example, the code at callout B invokes the method once for the Name property value of the col1 object and a second time for the Name property value of the col2 object. Another distinction between this code sample for setting the primary key and the sample in callout A in Listing 2 is that this code specifies a nonclustered index.

SQL-DMO offers a powerful programming solution for automating the administrative tasks that you perform manually with SQL Server. You can use the three code samples in this article to develop your programming techniques for building tables. Practicing these techniques will help you think of new ways to use SQL-DMO to automate your processes.