SQL Server Magazine

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, page 24, 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.

   Prev. page   [1] 2     next page