ADP use a set of client/server graphical design tools called the da Vinci toolset during their development. Microsoft calls da Vinci MS Design Tools, but the most common name for Access's implementation of the toolset is the project designer. Access's project design mode lets you alter the structure of tables, relationships, views, functions, and stored procedures directly from the user interface. SQL Server and other client/server RDBMSs rely on SQL CREATE, ALTER, and DROP statements for design changes. The project designer executes the SQL statements for your design changes each time you confirm saving changes when exiting design mode. The ability to alter the design of the equivalent to linked tables, unavailable in prior versions of Access, is an important feature of projects. Note | The SQL panes of Access 2003's product designer don't include the CREATE VIEW|FUNCTION|PROC[EDURE] AS Name component of the T-SQL statement to create a new object. The project designer adds a CREATE... statement for a new object or an ALTER... statement for an existing object when sending the command to SQL Server. |
The Visual Studio .NET and 6.0 members also use the da Vinci toolset for client/server database design. Thus, the project designer brings Access 2003 into conformance with other Microsoft application design platforms, at least for client/server databases. Note Access data projects are an excellent learning aid and prototyping tool for large-scale client/server database projects. You can quickly and easily create new MSDE databases, add tables, establish relationships, design views, and write stored procedures in the project designer. Creating data-enabled forms, reports, and HTML pages with Access 2003 is a much faster process than that of other design platforms. After you've tested your prototype MSDE database design, you can deploy the .mdf and .ldf files directly to SQL Server running under Windows .NET/2000/NT Server. | Tables appear in conventional Access Datasheet view (see Figure 20.4). Date values appear in short date format, and money (Currency) fields default to the currency format you specify in the Currency page of Control Panel's Regional Settings tool. | Figure 20.4. The Datasheet view of NorthwindCS's Orders table demonstrates use of SQL Server extended properties. Project Designer's Table Design View | Table Design view differs dramatically from that of tables linked through Jet. Figure 20.5 shows the Orders table in the Access 2003 project designer, which differs from Access 2000's designer. Four basic column properties Column Name, Data Type, Length, and Nulls plus an extended property Description appear in the columns grid, and the Columns and Lookup properties pages display additional property values for the selected column. Property labels and text boxes are enabled only for those properties that are applicable to the column's data type. After you become familiar with the table-creation features of the project designer, you'll find that adding new SQL Server tables is almost as easy as creating new Jet tables. | Figure 20.5. Access 2003's project designer Table Design view has some features in common with Jet's Table Design view for local and attached client/server tables. NorthwindCS.sql doesn't propagate Description property values to NorthwindCS tables. SQL Server data type names are in lowercase, a holdover from Microsoft SQL Server's origin as the PC version of Sybase SQL Server for Unix. Table 20.1 lists the names and the correspondence of each project designer grid and the Columns properties page values you set in Access's Table Design grid and General properties page for Jet. Note Client/server databases commonly substitute the term column for field; this book uses field for tables and column for query result sets, views, functions, and data-returning stored procedures. Table 20.1. A Comparison of Project Designer and Jet Table PropertiesProperty | Correspondence to Jet Table Properties |
---|
Column Name | Same as Jet's Field Name. Spaces are permitted in SQL Server column names, but aren't recommended. | Data Type | Same as the combination of Jet's Data Type and Field Size, except data types use SQL Server terminology. | Length | Same as Jet's Field Size for text fields, except that char columns are fixed length. | Allow Nulls | The inverse of Jet's Required property; a check mark (the default) allows null values in fields. | Description | Same as Jet's Description property (an extended property) | Default Value | Same as Jet's Default Value. | Precision | Applicable primarily to numeric or decimal fields; specifies the total number of digits of the column (the precision property of int(eger) and money fields is fixed). | Scale | Applicable to numeric or decimal fields; specifies the number of digits to the right of the decimal point (the scale of money fields is fixed at 4). | Identity | Equivalent to Jet's AutoNumber field data type with Increment as the New Values property; Yes specifies that an int (same as Jet's Long Integer) field automatically creates a new value when appending a record. (SQL Server doesn't support Jet's Random option for AutoNumber fields.) | Identity Seed | Specifies the starting value of a field with the identity property set. | Identity Increment | Specifies the increment between successive identity values (usually 1). | Is RowGuid | Yes specifies that the row contains a globally unique identifier (GUID, pronounced "goo id") used primarily in conjunction with timestamp fields for replication. Jet has no direct counterpart. | Formula | For tables, the expression (formula) for creating a computed column value; Jet has no equivalent table property. | Collation | Sets the collating (sorting) sequence for character column; the default value is <database default>. Jet has no equivalent property. | Format | Lets you select a predefined display format from a drop-down list (extended property); same as Jet's Format property. | Decimal Places | Lets you select Auto or from 0 to 6 characters after the decimal point (extended property); same as Jet's Decimal Places property. | Input Mask | Lets you type a format string, such as >LLLLL for uppercase letters, or open the Input Mask Wizard to generate the string (extended property); same as Jet's Input Mask property. | The Lookup properties page (see Figure 20.6) lets you specify extended property values that are identical to those of Jet's Lookup page. The Row Source property value for a lookup field can be an SQL Server table, view, or function, or a value or field list. Figure 20.6. SQL Server extended properties in the Lookup properties page correspond exactly to Jet's Lookup properties. The Table Properties Dialog | The table Properties dialog for SQL Server tables also differs greatly from Jet's table properties dialog, and is a modified version of the Access 2000 designers dialog. In Table Design view, click the Properties button on the toolbar to open the new Properties dialog, which has five pages Table, Relationships, Indexes/Keys, Check Constraints, and Data. Access 2002 added the Check Constraints and Data pages. You set extended property values for Jet table and field properties that aren't included in Table 20.1 and the Lookup Properties page in pages of the Properties dialog. | The Tables Properties Page Figure 20.7 shows the Tables page of the Properties dialog for the Order Details table. The Order Details table is used for this and the following sections because this table has several unique properties. Figure 20.7. The Tables page only displays a few of the properties of the selected table. Note The Properties dialog doesn't have OK, Apply, or Cancel buttons. As you make changes on the four pages, the SQL statements to alter the properties accumulate in a cache. When you close the Table Design window or click the Datasheet View button, message boxes offer you the options of saving the table design or abandoning the changes. Following are brief descriptions of the elements on the Tables page: Selected Table Although Selected Table is a drop-down list, you can only select the currently open table from the list. Owner The default database owner (dbo) appears here as a read-only value. Table Name ANSI SQL doesn't allow table names with spaces or punctuation (except underscores), so field names containing illegal characters must be enclosed between square brackets. Note Microsoft opened a Pandora's box by allowing Jet databases to include spaces and other nonalphanumeric symbols in database, table, and field names. The Northwind.mdb developers finally removed spaces from field names in Access 9x, but the space remains in the Order Details table name. Access developers have complained long and loudly, but to no avail, about Microsoft's continuing use of spaces in Access object names.
Table Identity Column You can assign the identity property to a field from a drop-down list of field with numeric data types. Table ROWGUID Column If enabled, you can specify a field to contain automatically generated GUIDs. (Refer to the Is RowGuid property in Table 20.1.) Table Filegroup and Text Filegroup SQL Server 2000 lets DBAs create multiple operating system files for a single (usually very large) table. DBAs also can assign SQL Server text fields, the equivalent to Jet's Memo data type, to their own filegroup. Users of MSDE aren't likely to need to create filegroups. Description You can add a text description of the table as the value for this extended property, which corresponds to Jet's table Description property. Note Check Constraints, which appeared on the Tables page of Access 2000's Properties dialog, has been relocated to the new Check Constraints page. The Relationships Page Figure 20.8 shows the Relationships page of the Order Details table. Table relationships established by SQL DRI statements also appear in the Database Diagram for the database, which is the subject of the later "Diagramming Table Relationships" section. Many of the properties on this page have counterparts in Jet's Edit Relationships dialog. Figure 20.8. The two fields of the primary composite key of the Order Details table have foreign-key (FK) relationships with the primary keys of the Orders and Products table. To review Jet's Edit Relationships dialog, see "Establishing Relationships Between Tables," p. 189. Following are descriptions of the Relationship page's elements: Selected Relationship The Order details table has a composite primary key (OrderID and ProductID). These two fields have a foreign-key (FK) relationship with the Orders table's OrderID field and the Product table's ProductID field, respectively. The list box opens to select the FK_Order_Details_Products relationship. Relationship Name SQL Server automatically names the keys as FK_ TableName_ FieldName. Primary Key Table and Foreign Key Table, and fields lists Table names are read-only, except when you click Add to create a new relationship. The field lists are similar to those of Jet's Edit Relationships dialog. Check Existing Data on Creation If you mark this check box, data in the table is tested for relational integrity when you add a new relation. Jet always tests existing data when establishing a new relationship. Enforce Relationship for Replication Marking this check box requires replicated copies of the table to enforce the relationship. Jet doesn't have this property. Enforce Relationship for INSERTs and UPDATEs This check box has the same effect as marking Jet's Enforce Referential Integrity check box. Cascade Update Related Fields and Cascade Delete Related Records These two check boxes correspond to Jet's check boxes of the same names. The Indexes/Keys Page Figure 20.9 shows the Indexes/Keys page for the Order Details table, which displays the table's primary key by default. This page bears only a faint resemblance to Jet's Indexes dialog. Figure 20.9. The Indexes/Keys page for the Order Details table displays the properties of the composite, clustered primary key. Following are descriptions of the controls on the Indexes/Keys page: Selected Index Open the list to select an index on the table. Order Details has a primary-key (composite) and a foreign-key index (on ProductID). The Type label changes, depending on the type of index you select: Primary Key, Index, or Unique Constraint. Index Name SQL Server names indexes as PK_ TableName for the primary key and IX_ TableName for other indexes. You can rename the index, if you want. (The ProductID index doesn't have the IX_ prefix.) Column Name and Order These two list fields correspond to the Field Name and Sort Order columns of Jet's Indexes dialog. Index Filegroup This list is enabled only when adding a new index. Like text filegroups, it's uncommon to create a special filegroup for MSDE indexes. Create UNIQUE, Constraint, Index, and Ignore Duplicate Key These check boxes and options determine index properties when creating a new index. Create UNIQUE is equivalent to Jet's No Duplicates modifier. SQL Server lets you choose to enforce unique values with a CHECK constraint or an index; Jet relies on an index. The Ignore Duplicate Key property applies only to bulk insert operations, which aren't common for MSDE databases. Create as CLUSTERED This check box causes SQL Server to physically order the table records by the primary-key value. Clustered indexes improve performance of INSERT and DELETE operations. It's a common practice to specify a clustered index on each table's primary key to improve database performance. Fill Factor and Pad Index If records aren't added to the table in the order of the primary key, adding some empty space (usually 10% to 20%) to the index page can improve INSERT performance. Pad Index reserves empty space (two rows) in clustered tables. Do Not Automatically Recompute Statistics Marking this check box speeds creation of indexes on large tables at the possible expense of query performance. Validation Text This extended property sets the text of the error message you receive when attempting to INSERT or UPDATE a value that conflicts with the UNIQUE constraint. Jet has built-in message text for attempted violation of the No Duplicates rule. The Check Constraints Page | Figure 20.10 shows the Check Constraints page for the Order Details table. Access 2002 moved the constraint property settings to their own page and added a Validation Text property. | Figure 20.10. The Check Constraints page lets you establish the equivalent of Jet Validation Rule and Validation Text properties. Following are descriptions of the elements of the Check Constraints page: Selected Constraint CHECK constraints are the SQL Server equivalent of Jet table- and field-level validation rules. You can specify multiple CHECK constraints; the Order Details table has three CHECK constraints CK_Discount, CK_Quantity, and CK_UnitPrice which you select from the drop-down list. Constraint Name This text box lets you rename the default name assigned by SQL Server, CK_FieldName. Constraint Expression The CHECK expression must evaluate to TRUE or FALSE. You add new constraints by clicking the New button and typing the expression and name in the text boxes. Validation Text This extended property sets the text of the error message you receive when attempting to INSERT or UPDATE a value that conflicts with the selected constraint. The property corresponds to Jet's Validation Text property. Constraint properties You can test existing data for conformance to constraints, enable constraints for data addition and updates, and apply constraints to replicated data with the three check boxes at the bottom of the page. The Data Page | Figure 20.11 shows the Data page for the Order Details table. All elements on this page are SQL Server extended properties that provide ADP counterparts of Jet table features, such as subdatasheets. | Figure 20.11. The Data page has a collection of extended properties to support upsizing existing Jet tables. Tip | Avoid setting Jet-specific table features and properties, such as subdatasheets, lookup fields, links for subforms and subreports, Filter, and Order By in new ADP. These features and properties are intended to support upsizing Jet tables to SQL Server and have no counterparts in ANSI SQL. Browse-mode editing in Datasheet view with subdatasheets and lookup fields isn't recommended for production client/server applications because this type of editing increases the probability of data-entry errors. To prevent subdatasheet open buttons from appearing in Datasheet view, set the Subdatasheet Name property to [None]. Use views with WHERE criteria and TOP 100 PERCENT...ORDER BY statements to avoid use of the Filter and Order By properties. Filter and Order By property values, as well as sort orders and filters applied by toolbar buttons, are applied by Access to the locally cached copy of the table's Recordset, also called a local snapshot. |
|