Working with SQL Server Tables in the Project Designer

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

graphics/new.gif

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.


graphics/globe.gif

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.

graphics/20fig04.jpg

Project Designer's Table Design View

graphics/2002_icon.gif

graphics/design_view.gif 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.

graphics/20fig05.jpg

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 Properties

Property

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.

graphics/20fig06.jpg

The Table Properties Dialog

graphics/2002_icon.gif

graphics/properties_window.gif 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.

graphics/20fig07.gif

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.

  • graphics/2002_icon.gif 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.


  • graphics/2002_icon.gif Table Identity Column You can assign the identity property to a field from a drop-down list of field with numeric data types.

  • graphics/2002_icon.gif 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.

graphics/20fig08.jpg

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.

  • graphics/2002_icon.gif 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.

graphics/20fig09.jpg

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.

  • graphics/2002_icon.gif 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

graphics/2002_icon.gif

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.

graphics/20fig10.gif

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.

  • graphics/2002_icon.gif 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

graphics/2002_icon.gif

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.

graphics/20fig11.gif

Tip

graphics/plus.gif

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.




Special Edition Using Microsoft Office Access 2003
Special Edition Using Microsoft Office Access 2003
ISBN: 0789729520
EAN: 2147483647
Year: 2005
Pages: 417

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net