BUILDING A CLIENT/SERVER APPLICATION

Up to this point, I've laid the pieces of the Access Project puzzle on the table and clarified what they are. Now it's time to start fitting them together to make a usable, productive application.

Architecturally, an Access Project combines application objects and pointers to server objects through a data link. Within the Access Project (.adp) file are stored the forms, reports, Data Access Pages, macros, and modules. On the server reside the tables, views, database diagrams, and stored procedures. By using the data link, the Access Project causes the server to execute code in stored procedures or to return records from a view or table.

The Access Project retrieves data from the server in one of several ways:

• By selecting records into a table or view datasheet

• By retrieving records for use in form or report controls

• By creating recordset objects from server data through VBA code calling the ActiveX Data Objects (ADO) components

The rest of this chapter uses the sample Project file NorthwindCS.Adp that ships with Access. Locate and open the Project now if you haven't done so already; it's in the Program FilesMicrosoft OfficeOfficeSamples folder. When you first open this Project file, it will look for a matching database (NorthwindCS) on your local MSDE SQL Server installation. If your local SQL Server service isn't started, Access will attempt to start it for you. If you don't have MSDE or a SQL Server Desktop installation on your computer, Access will ask you for the server name, user name, and password needed to log on to a server on your domain. If Access doesn't find the sample database on the server you specify, it will ask whether you want the database to be created (see Figure 25.10).

Working with Tables

The Access Database window for a Project enables you to design and modify tables on the linked SQL Server database. Simply highlight the Tables icon in the Database window and click New. Unlike working in Jet-based application, you must enter a valid table name in the pop-up Choose Name dialog before you can design the table (see the following section “Naming Conventions for Objects” for information on table naming conventions). Figure 25.11 shows the table design window.

Enter a column name and data type for each column that you want to create. Press Tab to move around in the design window. For each column, the name and data type are required; the other attributes are optional. Table 25.2 describes the attributes of a table column.

Tables can also be created and modified from a database diagram. You can open the Relationships diagram in the NorthwindCS sample Project to see an sample database diagram and experiment with table design in this tool.

Naming Conventions for Objects

Objects' names in SQL Server are slightly different from those for Jet-based Access Projects. Generally, the rules for object names in SQL Server are broader than those in Access/Jet, so if you are familiar with the limitations and naming conventions from past Access releases, you will have no trouble adapting to SQL Server's tastes, nor will you have trouble upsizing any existing databases to SQL Server.

In SQL Server, the term identifier refers to any object that can have a name, including tables, columns, views, indexes, constraints, rules, triggers, and stored procedures. Identifiers are either regular or delimited:

• A regular identifier conforms to the naming conventions described in this section and is used verbatim in table design, stored procedures, or VBA code.

• A delimited identifier is one that doesn't conform to the SQL Server naming conventions but is allowed in the database as long as every instance is quoted (with double quotes—"") or bracketed (with []).

For example, the object name Order is a reserved word in SQL Server (part of the Order By clause syntax). If you change the table name Orders to Order in the sample database and then try to create a new stored procedure by using the new table name Order, a syntax error results. On the other hand, if you delimit the object name Order in your stored procedure, the code runs as expected. Listing 25.1 shows an example of this.

Listing 25.1. Comparing a Stored Procedure's Regular and Delimited Identifiers

Alter Procedure prcIdentifiers
As
/* This table name is acceptable per SQL Server's naming conventions */
  Select * From Orders
/* This table name is not acceptable and must be delimited */
  Select * From [Order]
  Select * From "Order"
  Return

These object name rules for SQL Server 7.0 names qualify as regular (non-delimited) identifiers:

• Object names (even if delimited) must be 128 characters or fewer if they are regular identifiers, or 126 characters if they're delimited (to allow two characters for the delimiter as part of the name).

• The first character must be a letter from A to Z (either case, or their equivalent in a foreign language) or one of these characters—_, @, or #. Other characters and embedded spaces are disallowed.

• Subsequent characters can be any allowed first character, a decimal number, or the $ character.

• The identifier can't be a Transact-SQL reserved word (Transact-SQL is the scripting language used in SQL Server stored procedures).

• The identifier cannot contain embedded spaces.

Keep these considerations in mind when naming objects in your databases:

• Variables, parameters (arguments), and names in stored procedures use the strings @ and @@ in defined ways, so using this string at the beginning of an object name might be confusing.

• SQL Server uses the # or ## character strings to begin the name of a temporary table or temporary procedure, so a naming convention that begins object names with this character is discouraged.

Server Data Types

SQL Server column data types are conceptually similar to those in Jet, but the data type names are different, and the characteristics of a SQL Server data type may not perfectly mirror those of the related Jet data type. Table 25.3 describes the SQL Server data types.

Although Access/Jet table columns are now Unicode-enabled by default (meaning that they can contain characters from non-English display languages), you must explicitly use the Unicode-aware data fields in SQL Server if you intend to store non-English data. The SQL Server data types beginning with n (nchar, ntext, and nvarchar) are used for this purpose. However, although you may be tempted to use the Unicode-enabled data types in all cases to enable your application to handle any type of data in the future, be aware that each character stored in the Unicode data types consumes 2 bytes of storage instead of 1, even if the data isn't Unicode. This means that the disk space requirements of your database will grow much faster when Unicode data types are used.

Table 25.4 shows the SQL Server data types and compares the data type to the nearest Jet type.

Using Constraints

In SQL Server, constraints provide the functionality that you get from validation rules in a Jet-based application—they test and limit the values that can be entered into a column. The constraint model is quite complex and, therefore, very powerful. Properly used, constraints can save quite a bit of validation coding on your forms.

You can create five types of constraints for a table column.

Check Constraints

A check constraint limits the value that can be entered into a column. Generally, a check constraint takes a column value and compares it to another column value, a literal, or an expression by using this syntax:

[columnname] operator (comparisonvalue_or_expression)

Table 25.5 lists valid SQL Server expression operators.

If a check constraint names a single column, it's a column-level constraint and is bound to the named column. A check constraint that references more than one column (even if the same column name is repeated) is bound at the table level.

The expression portion of a check constraint can use wildcard characters that are valid for a SQL Like phrase, including % (match any number of characters), _ (match a single character), and [range] (match a single character in the range if the syntax is [x-x] or in the set if the syntax is [xxxx]). Because the constraint runs on the server, it can utilize Transact-SQL syntax but cannot reference VBA expressions or procedures.

For example, you have a column-level check constraint named CK_BirthDate, from the Employees table in the NorthwindCS sample database, and the constraint references the SQL Server getdate function:

([BirthDate] < getdate())

Another example is a table-level (multi-column) check constraint named CK_Discount, from the Order Details table in the NorthwindCS sample database:

([Discount] >= 0 And [Discount] <= 1)

Here's an example of a column-level check constraint that could be built-in the Order Details table in the NorthwindCS sample database. The constraint allows only state abbreviations for Washington and West Virginia by specifying that the first character of Region is w and the second character is a or v:

([Region] Like '[w][av]')

Here are some basic considerations for check constraints:

• A single column can have any number of check constraints.

• A check constraint for a column can reference only the column itself in the test expression, not other columns or tables.

• If multiple check constraints exist for a single column, they execute in the order they were created.

• A single table can have any number of check constraints.

• A table-level check constraint can reference only columns in the same table.

To create a new constraint, follow these steps:

Default Constraints

A default constraint is a value entered into a column if no other value is provided before the record is inserted into the table. Default constraints are entered into the Default Value attribute of the table design window.

A default constraint can contain a constant literal value (delimited with single quote marks or specified with the keyword Null) or an expression that evaluates to the data type of the constraint's column. When using an expression to create a default, any SQL Server functions such as user_name() and getdate() can be used. See the SQL Server Books Online topic “Functions (T-SQL)” for a complete list.

Defaults can be applied to columns of any data type except a timestamp column or any column marked as an identity column.

Primary Key Constraints

A primary key constraint is a column or multiple columns that, taken together, uniquely identify a single record in a table. Each table can have only one primary key constraint, and generally no table should be without a primary key because the Access Project uses the primary key when updating records in the server table.

Columns that participate in a primary key constraint can't allow Null values, and the table design window creates a unique index for the combination of columns in the constraint.

A primary key constraint is also used in referential integrity, the process of enforcing relationships between records in different but related tables.

To create a primary key constraint, select one or more column definitions in Table Design view; then click the Primary Key toolbar button or select Primary Key from the right-click menu.

Foreign Key Constraints

A foreign key constraint is a column or multiple columns that, taken together, identify a single record in a table for the purpose of relating it to the primary key of a record in a different table. A foreign key constraint maps a primary key in one table to a foreign key in a different table and enforces the relationship between the tables. It can, for example, ensure that no record entered into the foreign key table has a foreign key value that doesn't correspond to a primary key value in the related table.

You create a foreign key constraint in the database diagram designer by defining a relationship, which automatically creates the constraint. To define a relationship, follow these steps:

After a foreign key constraint is created through a relationship, you can't delete a record from the table in the primary key side of the relationship until all related records (those with a matching foreign key value) are deleted from the table on the foreign key side. Unlike Jet, SQL Server does not provide a property setting that enforces cascading deletions from a parent table into a child table. To create cascading deletions, you must add a delete trigger to the parent table.

Unique Constraints

A unique constraint validates that a value within the specified column is unique in the same table. A primary key, by definition, is also a unique constraint. A table can have multiple unique constraints.

To create a unique constraint, follow these steps:

Figure 25.14 shows this process in the table's Properties dialog.

Using Triggers

A trigger is stored procedure code that's run (triggered) by a delete, insert, or update event on a table record. A trigger applies validation rules or maintains data integrity. Triggers can contain most of the Transact-SQL statements allowed in stored procedures. If you are familiar with the event model for an Access form, you can think of a trigger as a BeforeUpdate event procedure.

The code in a trigger can affect rows in other tables, even if this causes other triggers to fire in a sort of chain-reaction cascade. As a result, an insert trigger can be used to provide validation that exceeds the capabilities of a table-level check constraint.

To create a trigger, choose Triggers from the shortcut menu for a table in the Database window. A trigger maintenance dialog appears. To create a new trigger, click New; the trigger design window appears, as shown in Figure 25.15.

In the trigger design window, remove the comment markers around the string "/* Insert, Update, Delete */" and keep one or more of the three values to define the type of trigger; the same trigger can be set to fire on more than one of the three trigger-able events. Enter a name for the trigger on the first line, and then modify the trigger code to achieve the desired result.

Listing 25.2 shows a sample trigger that has been added to the OrderDetails table in the sample database. This code fires whenever a new record is deleted, inserted, or updated in the table. The trigger updates (increments or decrements) the UnitsOnOrder field in the Products table for the product being affected by the order record. Review the code comments in the listing for more details.

Listing 25.2. A Trigger That Increments a Quantity Value in a Related Table

Create Trigger trgdiuOrderDetails
On dbo.[Order Details]
For Delete, Insert, Update
As
  /*
    Increment the on-order quantity in Products
      based on the order quantity in the insert buffer.
    The virtual table 'inserted' contains the records that
      were inserted or updated, if empty then this is a
      delete trigger.
  */
  If Exists(Select * From inserted)
    Update p
      Set p.UnitsOnOrder = p.UnitsOnOrder + i.Quantity
      From inserted i, Products p
      Where p.ProductID = i.ProductID

  /*
    Decrement the on-order quantity in Products
      based on the order quantity in the delete buffer.
    The virtual table 'deleted' contains the records that
      were deleted, if empty then this is an insert trigger.
  */
  If Exists(Select * From deleted)
    Update p
      Set p.UnitsOnOrder = p.UnitsOnOrder - d.Quantity
      From deleted d, Products p
      Where p.ProductID = d.ProductID

Notice in the listing that the name inserted was used to represent the actual record(s) being inserted, because the name refers to a virtual table called the insert buffer. Whenever new records are added to a SQL Server table or are updated (basically, deleted and added), the virtual table inserted is available in your triggers. Similarly, you can reference the delete buffer's virtual table by using the name deleted in an update or delete trigger to refer to the deleted record(s).

Obviously, triggers can be a powerful tool for Access developers. A single table can have multiple triggers in any combination of the three types: delete, insert, and update. You will find that much of the complex data manipulation that you've been doing with VBA code and form expressions in Jet-based Projects can now be done with table triggers in your Projects.

Because you must change a table record or records to test a trigger, your development may be easier if you write your trigger code in a stored procedure first. You can test and debug the stored procedure by running it from the Database window, and then paste the code into a trigger when the development is completed. However, if you're writing a trigger that relies on the deleted or inserted virtual tables, as shown earlier in this section, you can't test code that refers to those two tables within your stored procedure. You can, however, create dummy tables that represent the deleted or inserted tables for testing purposes. For example, create a table called deleted_test that has the same structure as the table that will receive the trigger, and test your stored procedure against the dummy table. When moving the tested stored procedure code to a trigger, simply remember to rename the references from deleted_test to deleted.

Optimizing Data Access

As with your Jet-based applications, a good database design is critical to the maintenance and performance of the SQL Server databases attached to your Projects. Here are some basic guidelines:

• Use a standard, normalized database structure— You will probably find that your multiuser applications run much more quickly on SQL Server than they did under a Jet-based model. Consequently, you will probably find yourself cheating for performance less than you used to. In a Jet-based database environment, “cheating” for performance usually means to

  • Denormalize a database (flatten the structure) to produce fewer table joins that each Select statement will process.

  • Break larger tables into several smaller tables so that fewer index pages and records are passed across the network to process a Select statement.

  In a SQL Server model, all processing of table joins, indexed lookups, and record aggregation takes place on the server, thereby minimizing network and workstation burden. Consequently, the database design compromises that you might make to improve performance in your Jet-based applications may actually reduce the performance of your Access Project-based application.

• Use relationships— Creating relationships between associated tables generally provides the best performance, and data integrity goals are well served by the use of primary and foreign key constraints in related tables. If you are familiar with creating Jet-based relationships in the Access Relationships window, learning to create SQL Server relationships in a database diagram is only a slightly different process.

• Use stored procedures and triggers— Build as much of the code on the server as is practical. The term “is practical” is relative to your business needs and your development style, but a good rule of thumb is that you should place code on the server that manipulates one or more existing database records. In contrast, you should place code in the application that validates or manipulates unsaved records or otherwise leverages the richness of the VBA language.

  Storing application code on the server has three distinct benefits:

  A single copy of the exists.	Assume that you have a Jet-based Access application codewith 100 users, and a validation rule or maintenance routine requires a small change. You must change the code in a master copy of the application and then distribute the updated application to all 100 users. If the code is on the server, however, you change the code in one central place, and all users begin to instantly see the new code, with no workstation-based installation tasks required at all.
  The code is processed on the server.	If your code acts against table data, it will run most quickly if it's stored in the database with that same table data. Also, servers tend to have more horsepower than workstations, so even a busy server may well execute code faster than a single workstation with far more limited horsepower.
  The code is closer to the database.	Logistically, it makes good sense to have the code for a database travel with that database onto backup devices, be transferred to test machines, or be replicated across an enterprise.

  
  

• Balance your use of indexes. Some developers take the approach that “you can't have too many indexes.” This isn't at all a truism in the SQL Server environment. SQL Server's indexing models are quite well documented, and in many scenarios, an extra index will cost performance rather than add to it. If you have the opportunity, read about indexing in the SQL Server Books Online before you create your first complex Project. As a general rule, however, you will want to create an index on any field commonly used in a join, a Where clause, or an Order By clause.

• Use the most appropriate data type. Select the simplest storage type for numeric data and the smallest data width for textual data that you can, as dictated by each data column's specific needs. For example, if time accuracy to the minute will suffice and information about seconds isn't required, using a smalldatetime column format over a datetime format will save 4 bytes per record, which can add up to a measurable savings of disk and memory space on a large table.

• Always put a timestamp field in a table in an Access Project. A timestamp field provides a unique record identifier that's shared between Access and SQL Server to optimize concurrency and transaction control. Not only will the performance of deletes, inserts, and updates improve when a timestamp exists in a table, but the speed of simple tasks such as filling and refreshing a table datasheet or form also is improved.