Chapter 2: Implementing Microsoft Access 2000 Client/Server Databases

Article
06/14/2014

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Acey J. Bunch
Microsoft Corporation

February 2000

Applies To: Microsoft® Access 2000

Summary: Second in a series of three articles about developing client/server solutions with Microsoft Access 2000 project (.adp) files and tools, this article provides an overview of how to implement a database design. It starts with setting a connection to a Microsoft Data Engine (MSDE) database, and then discusses how you can create a database and its tables. It also discusses how to use the State University sample application and its associated files. (36 printed pages)

Click here to download this sample from the Downloads Center.

Introduction
The State University Sample Application
Creating the New Database
Creating Tables
Creating Relationships Between Tables
Creating Indexes on Tables
Creating Constraints on Tables
Creating Triggers
Importing Data
Summary

Introduction

This is the second in a series of three articles that describes how to develop client/server solutions with Microsoft® Access 2000. This article discusses making the connection to the database server, creating a new database, and then building and working with the tables in the database. The State University sample application demonstrates all the topics covered.

The remaining article in this series focuses on the user interface and programming aspects of Access client/server development.

The State University Sample Application

The State University sample application was developed by using an Access 2000 project file (.adp) with Microsoft Data Engine (MSDE). This sample application is designed to use a variety of techniques that are exclusive to Access projects, but it also demonstrates some concepts that are universal to Access development in general. Before discussing how to implement a database design in an Access project, I'll briefly describe the design of the State University application.

The Scenario

Suppose you are an independent software developer who has been called in to develop a database application for a small computer training company known as State University. The company has requested that the application be developed in Access 2000 because they just upgraded to Office 2000, and you decide that it is a perfect opportunity to make use of the new features provided by MSDE and Access project files.

The application will be used to track the instructors, students, and courses offered, and it will generate reports to assist the administrative staff in their tasks. It will need to be an application that is easy to use and maintain, but with all the power of a true client/server database platform.

The Database Entities

One of the first steps in designing a database is establishing the main entities or types of information that make up the tables of your relational database. The following four main entities make up the tables for the State University database:

Instructors—The people who teach the courses
Students—The people who take the courses
Courses—The courses offered
Enrollment—Which student takes which courses

After identifying the entities that will eventually become the tables of the database, you should list all columns (fields) of data that will be included in each table. "Appendix 1: The State University Application Architecture"**gives a complete description of each table's columns, data types, and descriptions, otherwise known as the table schema.

Application Business Rules

Certain business rules that control and validate the flow of data in the application are associated with some of the entities identified in the previous section. The following table details the main business rules associated with each entity.

Table 2.1. Business rules

Entity	Business rules
Students	If a student is removed from the database, his or her enrollment records must also be removed.
Courses	Courses are offered on a quarterly basis.
Enrollment	Students cannot enroll in more than three courses per quarter.
General business rules	All date and time fields must be validated. All ZIP Code fields must be validated in ZIP code+4 format; for example, 55555-5555. All phone fields must be validated with an area code; for example, (555) 555-5555.

In this chapter, we will be discussing only database design and implementation, not user interface development or programming. For information about building the user interface and programming, see "Chapter 3: Building Microsoft Access 2000 Client/Server Applications." For more detailed information about designing a database, see "Creating and Maintaining Databases" in SQL Server Books Online January 2004 Update.

**Note **The material in SQL Server Books Online applies to MSDE as well as SQL Server, even though SQL Server Books Online only occasionally mentions MSDE.

Installing the Sample Application

There are two ways of following along with this series of articles. You can build the sample application as it is detailed in each step-by-step procedure, or you can install the fully completed sample application and use it as a reference. If you want to build the sample application yourself, skip this section and start reading at the next section, "Creating the New Database." If you want to install the sample application, do the following:

Install Access 2000 (see the Office 2000 documentation).
Install MSDE (covered in "Chapter 1: Understanding Microsoft Access 2000 Client/Server Development").
Attach the State University database sample file (StateU.mdf) to MSDE and configure the StateU.adp connection information, as explained in the next section.

I'll assume that you have already installed both Access 2000 and the MSDE; if you haven't, please see "Installing MSDE" in "Chapter 1: Understanding Microsoft Access 2000 Client/Server Development."

Attaching the State University database file and connecting to a server

You can install a SQL Server/MSDE database (.mdf) file in three ways: you can do a restore of a backed-up database, you can attach the .mdf file to the database server by using the sp_attach_db system stored procedure, or you can use the Data Link Properties dialog box in Access. In the following exercise, we will use the Data Link Properties dialog box to attach the sample database and connect the State University Access project file to a SQL Server or MSDE database server.

After downloading the State University sample application self-extracting .exe file (available for download at the beginning of this article), unzip the sample files it contains to a folder of your choice.
Copy the StateU.mdf file to the data directory on the computer where you have installed SQL Server or MSDE. The default data directory for a local installation is C:\MSSQL7\Data.
In Access 2000, open the StateU.adp Access project that came with the sample files. When the Main Form of the application appears, click the Design button to close the form.

**Note **Since you have not yet configured the connection information, you may see the title "StateU (disconnected) : Database" in the Database window.
On the File menu, click Connection to open the Data Link Properties dialog box.
Do one of the following:
- If you have installed SQL Server or MSDE on your machine, change the server name to either (local) or your machine name.
  
  -or-
- If SQL Server or MSDE is installed on a server instead of your local machine, change the server name to the name of the server where SQL Server or MSDE is installed.
Enter the appropriate user name and password.

Note If you have a newly installed version of SQL Server or MSDE and you have not changed any security settings, type sa for the user name and do not enter a password.
Select the Attach a database file as a database name option and type StateU as the database name, and then type C:\MSSQL7\Data\StateU.mdf in the Using the filename box.
Click OK.

Once the database file has been attached and the Access project is connected to it, you should be able to open any of the database objects within the State University sample application.

Creating the New Database

Note that, throughout this and the remaining chapters, you will see a small graphic that looks like this:

It indicates that the following section is a step-by-step procedure that you can use to create some certain aspect or feature of the State University sample application.

Creating the Access Project File

The first step in creating a new Access project is to create the Access project file (.adp). You can create this file in one of two ways. One way is to click Access database wizards, pages, and projects in the Microsoft Access dialog box that is shown when you first launch Access (see Figure 2.1), and then click OK.

Figure 2.1. Microsoft Access dialog box

The other way is to click New on the File menu in an instance of Access that is already opened. In either case, the next dialog box you will see is the New dialog box, as shown in Figure 2.2. On the General tab, click the Project (New Database) icon, and then click OK.

Figure 2.2. New database dialog box

The next dialog box that you will see is the File New Database dialog box. This is where you will name your .adp file and save it to a specified location on your hard disk or network drive. Simply navigate to the desired folder, type a name for the file in the File name box, and then click Create.

After a few minutes, you will see the initial page of the Microsoft SQL Server Database Wizard, as shown in Figure 2.3. Here is where you select the SQL Server machine you would like to use for the database, enter your login ID and password, and give your server database a name.

Note The Microsoft SQL Server Database Wizard is not installed by default; therefore, you may need to have your installation CD-ROM available in case you are prompted for it.

Figure 2.3. Microsoft SQL Server Database Wizard

Create the State University Database

To create a new State University .adp file and server database, do the following:

Open Microsoft Access, click Access database wizards, pages, and projects in the Microsoft Access dialog box, and then click OK.
On the General tab of the New dialog box, click Projects (New Database), and then click OK.
In the File New Database dialog box, select the folder where you want to store the State University .adp file, type StateU.adp as the file name, and then click Create. This will launch the Microsoft SQL Server Database Wizard.
In the initial page of the Microsoft SQL Server Database Wizard, enter the following information:
- SQL Server name: Enter the name of your database server; if you are running MSDE on your local computer, either enter your computer's name (to view your computer's name if you are using Microsoft Windows® 95 or 98 and Microsoft Windows NT® Workstation 4.0, click Network in the Control Panel, and then click the Identification tab; to view your computer's name if you are using Microsoft Windows 2000, click System in the Control Panel, and then click Network Identification), or use (local) as the server name. If you are running MSDE or SQL Server on another computer, you must enter that computer's name.
- Login ID: Enter the default sa account name.
- Password: The default is blank, so do not enter anything here, unless you have previously assigned a password to the sa account.
- SQL Server database name: Enter StateU as the name of your new server database.
When you have finished entering this information, click Next.
The next page you see is simply a confirmation page, so click Finish to create the new server database.

At this point, Access displays a message box that informs you of the progress it is making in creating the new server database. Once it is finished, you will see the standard Access environment, including the Database window.

Connecting to MSDE

After you entered the connection information in the first page of the Microsoft SQL Server Database Wizard, the wizard took that information and used it to make the connection to the server. The connection information is stored in the Data Link Properties dialog box (shown in Figure 2.4), which you can open by clicking Connection on the File menu.

Figure 2.4. Data Link Properties dialog box

As mentioned in the first chapter, you can use the Data Link Properties dialog box to change or redefine the connection information for an Access project, and to test the connection. An example of when you might want to test the connection is when you are ready to deploy your application to SQL Server. After developing the application with MSDE, you could copy the database files to the SQL Server database, connect the database files to SQL Server, and then reconfigure the connection information in the .adp file to point to the SQL Server database. If you click Test Connection to validate your connection and it is successful, you will see the confirmation message shown in Figure 2.4.

You can also reconfigure the security information that is used to connect to the server database. With MSDE and SQL Server databases, there are two forms of authentication: Windows NT integrated and MSDE/SQL Server specific. Windows NT integrated authentication uses your user name and password as established in Windows NT; MSDE/SQL Server specific authentication uses your user name and password as established in the database server itself.

Note Windows NT integrated authentication is not supported when you are running MSDE on the Windows 95 or Windows 98 operating system.

Understanding the Database Files

When you created the State University database in the steps above, there were actually two separate files created for you: StateU.mdf and StateU.ldf. Both files were placed in the C:\MSSQL7\data folder (assuming that you installed MSDE in the default location), and each of the files was created with a default file size of approximately 2 MB.

The StateU.mdf file contains all of the application's server database objects and data (of which there is none yet), and it has a maximum file size limit of 2 gigabytes (GB). The StateU.ldf file is the transaction log file that is used by MSDE to record all transactions in case a system recovery needs to be performed.

Creating Tables

While the process of creating tables in Access projects is similar to creating them in Access databases, the tools that you use to do so are quite different. In an .adp file, you use a new set of database design tools for creating and working with the tables in MSDE and SQL Server databases. The tables themselves are also different from their .mdb file counterparts in that there is no column properties pane, all column properties are set in the table design grid, and there are many more data types to choose from when defining a column.

Note In an .mdb database, your tables consist of records and fields. But in an .adp database, records and fields are sometimes called rows and columns. This difference occurs because Access projects tend to follow the SQL Server naming conventions. For this discussion, I will be using rows and columns.

Opening Tables in Design View

There are three ways you can open a new or current table in Design view:

On the Insert menu, click Table.
In the Database window, click Tables under Objects, and then click New or Design on the Database window toolbar.
In a database diagram, do one of the following:
- On the Diagram menu, click New Table.
  
  –or–
- Right-click in the diagram, and then click New Table on the shortcut menu.

Note When you are creating a new table, Access will prompt you to name the table before allowing you work with its design.

When you open a table in Design view, the table schema is displayed in a grid format, as shown in Figure 2.5.

Figure 2.5. Table design grid

Naming the Server Database Objects

Every object that is saved in the server database must follow a set of naming conventions called the rules for identifiers. These rules establish how a name for the database object can be constructed by using a mixture of characters, numbers, and symbols. When naming your tables, columns, or other database server objects, note the following:

Identifiers can be from 1 to 128 Unicode characters in length, including characters, numbers, and symbols.
The first character must be [a-z], [A-Z], _, @, or #.
The symbols @, $, #, or _ can be used in subsequent characters.
The identifier must not be a Transact-SQL reserved word.
Embedded spaces are not allowed.

If the identifier does not follow these rules, it is then called a delimited identifier because it must always be delimited with either double quotation marks ("") or brackets ([]) when it is referenced. For example, if you include a space in a table name, such as My Table, the table name must be delimited when you reference the table in a Transact-SQL statement; for example, "My Table" or [My Table].

Note Certain symbols at the beginning of an identifier have special meanings. An identifier beginning with an at sign (@) denotes a local variable or parameter. An identifier beginning with a number sign (#) denotes a temporary table or procedure. An identifier beginning with double number signs (##) denotes a global temporary object. Some Transact-SQL functions have names that start with double at signs (@@). To avoid confusion with these functions, you should not use names that start with @@. For more information about Transact-SQL and variables, see "Chapter 3: Building Microsoft Access 2000 Client/Server Applications."

For more information about identifiers, search for identifiers in the SQL Server Books Online index.

Defining Column Properties

As you create a new table, each column in the table must be defined by a set of column properties, and there can be up to 1,024 columns defined per table. A table must have its column name, data type, and length properties defined before it can be saved to the server database. There are eleven properties that you can specify for each column that you add. The following table describes each of them.

Table 2.2. Column property definitions

Column property	Description
Column Name	The name of the column, which must follow the rules for identifiers.
Datatype	The data type of the column. For more information about column data types, see Table 2.3.
Length	The length of the data type. This value is set automatically based on the data type, but it can be changed for the binary, char, nchar, varbinary, varchar, and nvarchar data types.
Precision	For decimal and numeric data types only, this property sets the maximum number of digits used.
Scale	For decimal and numeric data types only, this property sets the maximum number of digits used to the right of the decimal point.
Allow Nulls	This property is checked if the column can receive null values.
Default Value	The value used when a new row is inserted into the table and a value is not explicitly provided.
Identity	This property is valid only for integer-based data types. If this property is checked, the column will contain a system-generated number that is automatically incremented for each new row. The Identity property functions much like an AutoNumber field in an Access database (.mdb), except that the automatically incremented number isn't added and displayed in the new row until the row is saved.
Identity Seed	The initial value used for the identity column.
Identity Increment	The value to increment by for each new row.
Is RowGuid	This property is checked if the column is a globally unique identifier (GUID).

For more information about column properties, type column properties or database diagram designer considerations in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search, or see SQL Server Books Online and search for columns, properties on the Index tab.

Column names

The column names that you define for each column must be unique within the table, and they must conform to the rules for identifiers.

Data types

The data types used in Access projects are similar to the ones used in Access databases, but there are some differences, and there are more data types to choose from. The data types that you can use are listed in the following table.

Table 2.3. Data-type property definitions

Data type	Default length	Description
binary	10	Fixed-length binary data of n bytes. n must be a value from 1 through 8,000. Storage size is n+4 bytes.
bit	1	Integer data type that can be 1 or 0.
char	10	Fixed-length non-Unicode character data with a maximum length of 8,000 characters.
datetime	8	Date and time data from January 1, 1753, to December 31, 9999, with an accuracy of three-hundredths of a second, or 3.33 milliseconds.
decimal	9	Fixed precision and scale numeric data from -10^38 -1 through 10^38 -1.
float	8	Floating-precision number data from -1.79E + 308 through 1.79E + 308.
image	16	Variable-length binary data with a maximum length of 2^31 - 1 (2,147,483,647) bytes.
int	4	Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).
money	8	Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit.
nchar	10	Fixed-length Unicode data with a maximum length of 4,000 characters.
ntext	16	Variable-length Unicode data with a maximum length of 2^30 - 1 (1,073,741,823) characters.
numeric	9	A synonym for decimal.
nvarchar	50	Variable-length Unicode data with a maximum length of 4,000 characters.
real	4	Floating-precision number data from -3.40E + 38 through 3.40E + 38.
smalldatetime	4	Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute.
smallint	2	Integer data from 2^15 (-32,768) through 2^15 - 1 (32,767).
smallmoney	4	Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit.
text	16	Variable-length non-Unicode data with a maximum length of 2^31 - 1 (2,147,483,647) characters.
timestamp	8	A database-wide unique number.
tinyint	1	Integer data from 0 through 255.
uniqueidentifier	16	A globally unique identifier (GUID).
varbinary	50	Variable-length binary data of n bytes. n must be a value from 1 through 8,000. Storage size is the actual length of the data entered + 4 bytes, not n bytes. The data entered can be 0 bytes in length. The SQL-92 synonym for varbinary is binary varying.
varchar	50	Variable-length non-Unicode data with a maximum of 8,000 characters.

For more information about data types, type database diagram designer considerations in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search, or see SQL Server Books Online and search for data types on the Index tab.

User-defined data types

A great feature of MSDE is the ability to define your own data types. User-defined data types are based on the system data types in SQL Server and MSDE, and they are used when you want to ensure that multiple columns will contain the same type of data. You define the new data type by using the system-supplied stored procedure sp_addtype. When creating the data type, you must specify the name, physical type, and nullability. Once you have created the data type, it will be displayed in the data type column property list in the table design grid. Note that if you create the user-defined data type in the MSDE model database, the new data type will be available for all new databases that you create. For more information about the model database, see SQL Server Books Online and search for model on the Index tab.

To show how a user-defined data type could be used, suppose that we want to change the format of the phone numbers stored in the State University database. These phone numbers are stored in three different columns and we want the format of those numbers to be (555) 555-5555. Instead of explicitly specifying the data type property for each of the three columns, we can make a user-defined data type that will be the same for each one. The following steps demonstrate how to create the user-defined phone data type.

Create the User-Defined phone Data Type

On the Insert menu, click Stored Procedure to open the Stored Procedure Designer.
In the CREATE PROCEDURE clause, change the name of the procedure to CreatePhoneDataType.
Remove the default comments and add the following line of code after the AS clause of the stored procedure:
```
EXECUTE sp_addtype 'phone', 'varchar(14)', 'NOT NULL'
```
Figure 2.6. The CreatePhoneDataType stored procedure
Close and save the stored procedure.
In the Database window, click Stored Procedures under Objects, select the CreatePhoneDataType procedure, and then click Run.

To test whether the new phone data type exists, create a new table, and then click in the Datatype column property cell. When you click the down arrow to display the list of available data types, you should see the phone data type at the end of the list.

For more information about using stored procedures, see "Chapter 3: Building Microsoft Access 2000 Client/Server Applications." For more information about the sp_addtype system stored procedure, see SQL Server Books Online and search for sp_addtype on the Index tab.

Allowing null values

For each column in your table, you can specify whether to allow or disallow null values. A null value, or NULL, is not the same as zero (0) or empty; NULL is a placeholder for a value that is nonexistent or unknown. Its presence usually implies that the value is either unknown or undefined. If null values are not allowed, the user entering data in the table must enter a value in the column or the new row cannot be accepted in the database.

Note You cannot change this property on columns defined as the primary key, and identity columns cannot be set to NULL.

For more information about null values, type database diagram designer considerations in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search, or see SQL Server Books Online and search for NULL values on the Index tab.

Default values

When users enter new rows in a table, often you'll want to specify a value that should be placed in a column if they user did not provide one. These are called default values and you can define them at the table level by using the table design grid. If the default value is a number, simply enter the number in the default column property cell. But if the value is a string, you should enclose the string in single quotation marks.

For more information about default values, type database diagram designer considerations in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search, or see SQL Server Books Online and search for default, definition on the Index tab.

The identity data type

When a column is defined as an identity column, it will contain system-generated sequential values that uniquely identify each row within a table, similar to the AutoNumber data type in Access .mdb databases. When you are inserting values in a table that has an identity column, MSDE automatically generates the next identifier based on the last-used identity value (the Identity Seed column property) and the increment value (the Identity Increment column property) specified during the creation of the column.

The identity property can be set only for a column with a data type of decimal, int, numeric, smallint, or tinyint; it must not allow null values; and you can have only one identity column defined in a table. In "Chapter 3: Building Microsoft Access 2000 Client/Server Applications," you will learn how to retrieve the identity column value of the last row inserted in a particular table that has an identity column defined.

For more information about identity values, type database diagram designer considerations in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search, or see SQL Server Books Online and search for identity columns on the Index tab.

Setting the Primary Key

When you are building a table in a relational database, you should define a primary key to uniquely identify rows within the table. If you define a primary key for a table in your database, you can relate that table to other tables, thus reducing the need for redundant data. A table can have only one primary key, but a primary key can consist of more than one column.

There are two ways that you can establish the primary key for a table when the table is opened in Design view:

Use the Primary Key button on the Table Design toolbar. To establish the primary key on a single column in a table, open the table in Design view, select the column that you want to define as the primary key by clicking on the arrow next to the column name, and then click the Primary Key button on the Table Design toolbar. Access will then create the primary key, naming it PK_tablename, and it will also automatically establish the column with a UNIQUE constraint.

If you want to create a multiple-column primary key, hold down the CTRL key as you select all of the fields you want to include in the key, and then click the Primary Key button on the Table Design toolbar. For more information about constraints, see "Creating Constraints on Tables" later in this chapter.
Use the table's Properties dialog box. You can get to the Properties dialog box in a number of ways, but the easiest is to open a table in Design view, and then click the Properties button on the table design grid. Once the Properties dialog box is open, click the Indexes/Keys tab, as shown in Figure 2.11.

To establish the primary key, click New on the Indexes/Keys tab to create the new index, and then select the column that you want to define as the primary key. Name your index something like PK_tablename, and create a UNIQUE constraint for it. For more information about constraints, see "Creating Constraints on Tables" later in this chapter.

As you can tell, it is much easier to simply use the Primary Key button on the Table Design toolbar to create the primary key. However, if you want to use your own naming conventions for the primary keys you create, you must use the Properties dialog box.

For more information about primary keys, type defining a primary key in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search, or see SQL Server Books Online and search for PRIMARY KEY constraints on the Index tab.

Using SQL to Create Tables

In addition to using the table designer grid to build your tables, you can also use the SQL CREATE TABLE statement, which is a data definition language (DDL) statement. You would do this in Access by creating a stored procedure, entering the CREATE TABLE statement, and then executing it. While a thorough discussion of the CREATE TABLE statement is beyond the scope of this article, you can find information about using it by opening SQL Server Books Online and searching for CREATE TABLE on the Index tab.

The Instructors Table Schema

Now that we have covered the basics of building a table in an Access project, let's create a table in the State University database. Below is the schema (or table definition) of the Instructors table.

Table 2.4. Instructors table schema

Column	Data type	Constraint	Description
InstructorID	Int (identity(1,1))	PRIMARY KEY	The ID for each instructor.
FirstName	varchar(25)	NOT NULL	The first name.
MiddleName	varchar(25)	NULLS ALLOWED	The middle name or initial.
LastName	varchar(25)	NOT NULL	The last name.
Address	varchar(255)	NOT NULL	The street address.
City	varchar(50)	NOT NULL	The city.
State	varchar(2)	NOT NULL	The state.
Zip	varchar(10)	NOT NULL	The ZIP Code.
HomePhone	phone (varchar)	NOT NULL	The home phone number.
Email	varchar(100)	NULLS ALLOWED	The e-mail address.

Note that there are no defaults defined for this table, but the HomePhone column's data type property is set to the phone user-defined data type that was discussed in a previous section. All of the State University tables can be found in "Appendix 1: The State University Application Architecture."

Create the Instructors Table

To create a new State University table called Instructors, do the following:

On the Insert menu, click Table. When prompted, name the table Instructors. You will then see the table design grid.
Using the table schema presented in Table 2.4, define all of the columns in the Instructors table.
Select the InstructorID column and then click the Primary Key button on the Table Design toolbar to establish the table's primary key.
Open the table's Properties dialog box and use the Indexes/Keys tab to confirm that there is a primary key called PK_Instructors.
Close and save the Instructors table.

Once completed, your table design should match Figure 2.7.

Figure 2.7. The Instructors table in Design view

Now that you know how to create tables in an Access project, go ahead and create all of the other State University tables using the database schema that is presented in Appendix 1. We will next learn how to establish the relationships between tables in an Access project.

Creating Relationships Between Tables

In a relational database system, it is often the goal to prevent redundant data, and also prevent the deletion of data in one table that is related to data in another table. These goals are enforced by using referential integrity, and part of establishing referential integrity is defining the relationships between the tables.

In Access projects, there are two primary ways of defining the relationships between tables: by using database diagrams, or by defining the relationships by using SQL. When you define the table relationships at the table level, it is often called declarativereferential integrity (or DRI).

For more information about defining table relationships, type table relationships in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search, or see SQL Server Books Online and search for normalization on the Index tab.

Using Database Diagrams

Database diagrams are similar to the Relationships window in Access .mdb databases, but they have a lot more functionality. In fact, it is possible to not only define relationships, but also create your tables right in the database diagram itself. The Database Diagram window presents a graphical display of the tables and relationships in the database. Some of the things that you can do in it are:

Add tables to the database diagram by dragging them from the Show Table list.
Remove tables from the diagram or delete them from the database.
Save selected tables, or the entire database diagram.
Create, edit, or delete relationships between tables.
Create, edit, or delete tables and table column definitions.
Open the property pages to edit objects shown in the diagram, or to create, edit, or delete database objects, such as indexes and constraints, that are not graphically represented in the diagram.
Create, edit, or delete text labels.
Show or hide relationship names.
Change the column properties that are displayed.

For more information about defining table relationships, type database diagrams in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search.

Creating database diagrams

To create a database diagram, click Diagram on the Insert menu, and then click Show Table on the View menu. The Show Table list will appear as shown in Figure 2.8; from there, you can select the tables that you want to include in your diagram and simply drag them to the Database Diagram window. However, you can only drag one table at a time.

Figure 2.8. The Show Table list

Once you have your tables in the Database Diagram window, you can define the relationships between them by clicking a column in one table and then dragging and dropping it on the related column in another table. Of course, the columns that you relate to each other must be of similar data type, and typically you will be defining either one-to-one or one-to-many table relationships by using a combination of primary and foreign keys.

After you drop the related column onto the other column, Access will display the Create Relationship dialog box, as shown in Figure 2.9.

Figure 2.9. The Create Relationship dialog box

Table 2.5 describes the various elements of the Create Relationship dialog box.

Table 2.5. Elements of the Create Relationship dialog box

Element	Description
Relationship name	Displays the system-assigned name of the relationship. To rename the relationship, type a new name in the box.
Primary key table	Shows the name of the primary key table in the relationship, followed by the columns that make up the primary key. You can select different columns to match the columns shown under Foreign key table.
Foreign key table	Shows the name of the foreign key table in the relationship, followed by the columns that make up the foreign key. You can select different columns to match the columns shown under Primary key table.
Check existing data on creation	Applies a foreign key constraint to existing data in the foreign key table when the relationship is created. An error message will notify you of any data that violates the constraint if this box is selected.
Enable relationship for INSERT and UPDATE	Applies a foreign key constraint when data is added to or updated in the foreign key table by using the INSERT and UPDATE statements.
Enable relationship for replication	Copies the foreign key constraint whenever the foreign key table is copied to a different database.

Once you click OK in the Create Relationship dialog box, the relationship will be graphically displayed in the Database Diagram window.

For more information about defining table relationships with database diagrams, type create relationships in database diagrams in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search.

Create the Students/Enrollment Relationship

By now you should have all of the State University tables created. In this exercise, we will be establishing the one-to-many relationship between the Students and Enrollment tables.

On the Insert menu, click Diagram to open the Database Diagram window.
Click the Show Table button on the Diagram Design toolbar. The Show Table list will be displayed.
Select and then drag the Students table to the Database Diagram window, and then do the same for the Enrollment table.
Click the StudentID column of the Students table and then drag it to the StudentID column of the Enrollment table. The Create Relationship dialog box will be displayed.
Click OK and the relationship will be graphically displayed, as shown in Figure 2.10.

Figure 2.10. The FK_Enrollment_Students relationship
Close the database diagram and save it as StateU_Diagram.

Now that you know how to create table relationships in an Access project, go ahead and create all of the other State University table relationships by using the database schema that is presented in Appendix 1. We will next learn how to establish indexes in an Access project.

Creating Indexes on Tables

When we built the State University tables, we also created primary keys for each of them. Establishing the primary keys automatically built a primary key index for each of the tables, based on the primary key columns. These indexes allow for faster access to the information contained in the tables, much the same as an index in a book allows for faster access to particular sections within that book. But there may be other columns that we will frequently be using to query for information, and it would helpful to build indexes on those columns as well.

In SQL Server and MSDE database servers, there are two basic kinds of indexes: clustered and nonclustered. In a clustered index, the physical order of the rows in the table is the same as the logical order of the key values, but a table can contain only one clustered index. If an index is nonclustered, the physical order of the rows in the table does not match the logical order of the key values. A clustered index usually provides faster access to data than does a nonclustered index.

There can be up to 249 nonclustered indexes per table, and you can include from 1 to 16 columns in a single index, but the index can be no more than 900 bytes wide.

You can also create a type of index called a unique index, in which no two rows are permitted to have the same index value. Primary key indexes are automatically created as unique indexes.

Note As a rule of thumb, you should create an index on a table only if the data in the indexed columns will be queried frequently. Indexes take up disk space and slow the addition, deletion, and updating of rows. In most situations, the speed advantages of indexes for data retrieval greatly outweigh these disadvantages. However, if your application updates data very frequently or if you have disk space constraints, you might want to limit the number of indexes.

To create indexes for Access project database tables, use the Indexes/Keys tab of a table's Properties dialog box, as shown in Figure 2.11. You can open a table's Properties dialog box from either table Design view, or in the Database Diagram window.

Figure 2.11. The Indexes/Keys tab of the Properties dialog box

Table 2.6 describes the various elements of the Indexes/Keys tab of the Properties dialog box.

Table 2.6. Elements of the Indexes/Keys tab

Element	Description
Selected index	Shows the name of the first index for the selected table. To choose a different index to show properties for, open the Selected index list.
Type	Shows the index/key object type for the selected table: index, primary key, or unique.
New	Click this button to create a new index for the selected database table.
Delete	Click this button to remove the selected index from the table.
Column name	Shows the list of columns in the index, primary key, or UNIQUE constraint. You can add, change, or remove column names in this list.
Index name	Shows the name of the selected index. You can rename the index by entering a new name in this box.
Index file group	Select the name of the file group in which you want to store the selected index. File groups are an administrative mechanism for grouping files within a database. You must have at least one user-defined file group for this setting to be enabled. In most cases, this should be left to the default PRIMARY file group. For more information about file groups, see SQL Server Books Online and search for filegroups on the Index tab.
Create UNIQUE	Select this option to create a UNIQUE constraint or index for the selected database table. Specify whether you are creating a constraint or index by clicking either the Constraint or Index button. If you create a unique index, you can set the Ignore duplicate key option to ensure that each value in an indexed column is unique.
Fill factor	Shows the fill factor, which specifies how full each index page can be. This option is used to fine-tune database performance, but in most cases it should be left at the default value of 0 (zero).
Pad index	If you specified a fill factor of more than 0 (zero) percent, and you selected the option to create a unique index, you can tell SQL Server or MSDE to use the same percentage you specified for the fill factor as the space to leave open on each interior node. By default, SQL Server or MSDE sets a two-row index size.
Create as CLUSTERED	Select this option to create a clustered index for the selected database table.
Don't automatically recompute statistics	Select this option to tell SQL Server or MSDE to use previously created statistics. This choice may not produce optimal results and is not recommended.

Create the IX_Students_HomePhone Index

In this exercise, we will be creating a nonclustered index on the HomePhone column of the Students table. Let's assume that our customer wants to be able to search for students by student ID and/or home phone number. Since the StudentID column is the primary key for the Students table, it already has a primary key index. Let's build the index for the HomePhone column.

In the Database window, click Tables under Objects, select the Students table, and then click Design.
On the View menu, click Properties to open the table Properties dialog box.
Click the Indexes/Keys tab.
Click New to create the new index.
In the Column name list, click the down arrow and select HomePhone as the column.
In the Index name box, type IX_Students_HomePhone as the name of the index. The Properties dialog box should now look similar to Figure 2.11, above.
Close the tables Properties dialog box, close the table design grid and click OK when prompted to save.

Now that you know how to create indexes in an Access project, go ahead and create all of the other State University table indexes by using the database schema that is presented in Appendix 1. We will next learn how to establish constraints in an Access project.

For more information about indexes, type indexes for a table in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search, or see SQL Server Books Online and search for indexes, described on the Index tab.

Creating Constraints on Tables

A constraint is a way of limiting (or constraining) the values that are entered in a particular column or set of columns. Constraints are very important when you are establishing referential integrity in a relational database because they can serve to enforce both data integrity and consistency.

There are five major kinds of constraints in SQL Server and MSDE databases, and we have already worked with four of them. Table 2.7 outlines the five types of constraints and their descriptions.

Table 2.7. Constraint types

Constraint	Description
DEFAULT	A DEFAULT constraint enables you to define the value that will be supplied for a column whenever a user fails to enter a value. Unlike Access databases (.mdb), the value defined by MSDE and SQL Server default constraints is supplied when a record is saved, not when a new record is first added.
UNIQUE	A UNIQUE constraint ensures no duplicate values are entered in specified columns that are not a table's primary key.
PRIMARY KEY	A PRIMARY KEY constraint ensures no duplicate values are entered in the primary key columns and that null values are not entered in those columns. You can use PRIMARY KEY constraints to enforce uniqueness as well as referential integrity.
FOREIGN KEY	A FOREIGN KEY constraint works in conjunction with a PRIMARY KEY or UNIQUE constraint to enforce referential integrity among specified tables.
CHECK	A CHECK constraint specifies the data values or formats that are acceptable in one or more columns in a table.

We have already worked with four types of these constraints in the following ways:

When we built the State University tables and defined a default value for a specific column, we used a DEFAULT constraint.
When we established primary keys for the State University tables, we defined both UNIQUE and PRIMARY KEY constraints along with the unique and primary indexes.
When we established the relationships between the tables in the State University database by using a database diagram, we also established the FOREIGN KEY constraints.

This means that the only constraint we haven't used is the CHECK constraint. A CHECK constraint is used to check a value before it is saved to a particular column in a table. Using this constraint is similar to establishing a data-validation rule in an Access .mdb database. CHECK constraints are defined at the table level by using the Tables tab of the table Properties dialog box, as shown in Figure 2.12.

Figure 2.12. The Tables tab of the Properties dialog box

Table 2.8 describes the various elements of the Tables tab of the Properties dialog box.

Table 2.8. Elements of the Tables tab

Element	Description
Selected table	Shows the name of the selected table.
Table name	Shows the name of the selected table. To rename the table, enter a new name in this box.
Table file group	Shows the name of the file group in which the table data is stored. The default file group is the PRIMARY file group.
Text file group	Shows the name of the file group in which the table's text and images are stored.
Selected constraint	Shows a list of the check constraints attached to the selected table.
New	Creates a new CHECK constraint for the selected table.
Delete	Deletes the selected CHECK constraint from the database.
Constraint expression	Shows the SQL syntax of the selected CHECK constraint. For new constraints, you must enter the SQL syntax before exiting this box.
Constraint name	Shows the name of the selected constraint. You can rename the constraint by entering a new name in this box.
Check existing data on creation	Ensures that all data that existed in the table before the constraint was created is verified against the constraint.
Enable constraint for INSERT and UPDATE	Applies the constraint when data is inserted or updated in the table.
Enable constraint for replication	Applies the constraint when the table is copied in a different database.

When writing CHECK constraints, you should consider the following:

CHECK constraints are expressions that evaluate to either true or false, and only when the expressions evaluate to true are the values accepted for the specified column or columns.
You can apply more than one CHECK constraint to a single column (column level), but the constraints can reference only the source column, and the constraints are checked in the order that they were created.
You can apply a single CHECK constraint to multiple columns (table level), but can reference only columns in the source table.
You can set the CHECK constraint to apply to both new data and data already in the table, or you can have it apply only to the new data. The default is to apply the constraint to both.
Multiple expressions can be combined with logical AND and OR operators.
Subqueries are not allowed in CHECK constraints.

The CHECK constraint is an SQL expression that evaluates to a single return value that is either true or false. If the expression returns false, the data is not accepted into the column and the INSERT or UPDATE operation fails (if the constraint is enabled for these operations). If the expression returns true, the data is accepted into the column and processing continues with the next CHECK constraint if there is one.

SQL expressions can use a mixture of identifiers, values, and operators. For example, to check that a column's values are always less than 100, use the following expression:

ColumnName < 100

To make sure that a column's values are within a list of values, use the IN operator:

ColumnName IN ('Value1','Value2','Value3')

To check that a column's values are numeric only, use the LIKE operator:

ColumnName LIKE '[0-9][0-9][0-9]'

For more information about CHECK constraints, type CHECKconstraints in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search, or see SQL Server Books Online and search for CHECKconstraints on the Index tab.

Create the CK_Instructors_Zip CHECK Constraint

In this exercise, we will be creating a CHECK constraint for the Instructors table in our State University sample database.

In the Database window, click Tables under Objects, and then select the Instructors table and click Design.
On the View menu,****click Properties to open the table Properties dialog box.
On the Tables tab, click New to create the new CHECK constraint.
Type the following expression in the Constraint expression box:
```
Zip LIKE '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'
```
In the Constraint name box, type CK_Instructors_Zip.
Make sure that the Check existing data on creation and Enable constraint for INSERT and UPDATE check boxes are selected.
Close the Properties dialog box.
Close the Instructors table design grid, and save your changes.

To test the constraint, open the Instructors table, and in one of the table's records, change the value of the Zip column to 555, and then try to move to a different row. You should see a message that states that there was a statement conflict due to the CHECK constraint. Note that the row is not saved.

Now that you know how to create CHECK constraints in an Access project, go ahead and create all of the other State University CHECK constraints by using the database schema that is presented in Appendix 1.

For more information about constraints, type constraints in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search, or see SQL Server Books Online and search for constraints, overview on the Index tab.

Creating Triggers

When you defined the table relationships in a previous section, you may have noticed that the cascading updates and deletes feature that is available when you are creating Access databases (.mdb) is not available when you are creating Access project files (.adp). Access provides a user interface that allows you to define this referential integrity feature when you are establishing table relationships in an Access database, whereas in Access projects, you must program this functionality yourself. Although it may seem that this aspect of Access projects is more difficult, you will soon see that, in fact, you have much more control over how your data is maintained at the server level.

To establish cascading updates and deletes on a SQL Server or MSDE database, you use a database object called a trigger. A trigger is a special form of stored procedure that is automatically fired when an UPDATE, INSERT, or DELETE operation is made against a table.

Note Stored procedures are discussed in detail in the "Creating Stored Procedures" section of "Chapter 3: Building Microsoft Access 2000 Client/Server Applications."

**Important **You cannot use both declarative referential integrity (DRI) and cascading update and delete triggers at the same time; they are mutually exclusive. If you want to establish cascading update and delete triggers for a particular table, you must delete the foreign key relationship that points to the table that will be cascaded to. For example, to use the delete trigger as outlined in the example below, you must clear the Enable relationship for INSERT and UPDATE check box in the relationship's Properties dialog box. If you want to have both DRI and the cascading updates and deletes functionality, you must use a stored procedure that deletes all related records before it deletes the row that contains the primary key. Then, whenever you want to delete a row from the primary table, you would do so by calling the stored procedure instead of deleting the row directly from the table.

In addition to maintaining referential integrity, triggers can also be used to:

Maintain complex data integrity rules.
Keep running totals and computed columns updated.
Enforce business rules.
Call other stored procedures.

Think of triggers the way you do "Before" events in Visual Basic® code; they are activated before the requested work item happens, which in the case of triggers, is some sort of change to the data in a database. Whenever a row is inserted, updated, or deleted from a database, you can define a trigger to perform some action before the change is committed to the table.

To create a trigger, click Tables under Objects in the Database window, and then right-click the table you want to define a trigger for. On the shortcut menu, click Triggers to open the Triggers for Table: TableName dialog box shown in Figure 2.13.

Figure 2.13. The Triggers for Table: TableName dialog box

In this dialog box, you can create, edit, or delete a trigger. If you create or edit a trigger, Access will open the Trigger design window where you can type in the Transact-SQL code that will be executed when the trigger is fired.

Note Transact-SQL is covered in the "Creating Stored Procedures" section of "Chapter 3: Building Microsoft Access 2000 Client/Server Applications."

To use triggers effectively, you must interact with two special tables that are used by triggers: the inserted and deleted tables. These temporary tables are used by a trigger to hold copies of the rows that will be affected in the trigger (or primary) table. When you are inserting a row, the rows being inserted are placed in both the inserted table and the trigger table. When you are deleting a row, the rows being deleted are removed from the trigger table and are placed in the deleted table. Finally, when you are updating a row, the rows being affected in the trigger table are copied to the deleted table, and the proposed changes are copied to the inserted table.

Triggers use these two special tables to create snapshots of the changes to the data before the changes are permanently applied to the database. These snapshots allow you to disregard the changes if needed by issuing a ROLLBACK TRANSACTION statement.

To create a trigger, use the following syntax:

CREATE TRIGGER trigger_name
ON table_name
FOR {INSERT or UPDATE or DELETE}
AS
SQL statements

**Note **When you create a trigger by using the Triggers for Table: TableName dialog box, much of the CREATE TRIGGER statement is created automatically.

Create the trgStudent_Delete_Enrollment Trigger

In this exercise, we will be creating a trigger for the Students table in our State University sample database.

In the Database window, click Tables under Objects, right-click the Students table, and then click Triggers on the shortcut menu.
In the Triggersfor Table: TableName dialog box,****click New to open the Triggers design window.

Type the following code in the Triggers design window:

CREATE TRIGGER trgStudent_Delete_Enrollment
ON Students
FOR DELETE
AS
    --Delete the related enrollment records if any exist.
    IF EXISTS (SELECT StudentID FROM Enrollment 
        WHERE StudentID = (SELECT StudentID FROM deleted))
        BEGIN
        DELETE FROM Enrollment
            WHERE StudentID = (SELECT StudentID FROM deleted)
        END
    RETURN

Close the Triggers design window and save the trigger as trgStudent_Delete_Enrollment.

To test the trigger, make sure that you have cleared the Enable relationship for INSERT and UPDATE check box in the Properties dialog box for the FK_Enrollment_Students relationship. After you have cleared the check box, open the Students table and delete a student row that has associated enrollment rows, and then check the Enrollments table to verify that the related rows were deleted.

Note If you try to delete more than one row at a time, the trigger will display an error message, and only the first row will be deleted. If you need to delete more than one row at a time, you should use a stored procedure instead.

Create the trgEnrollment_Insert_Student Trigger

You can also use triggers to enforce business rules. For example, one of our application's business rules is that no student may enroll for more than three courses per quarter. By using a trigger, we can write code that prevents the insertion of a new enrollment record if a student has already reached his or her three-course maximum for the quarter.

In the Database window, click Tables under Objects, right-click the Enrollment table, and then click Triggers on the shortcut menu.
In the Triggers for Table: TableName dialog box,****click New to open the Triggers design window.

Type the following code in the Triggers design window:

CREATE TRIGGER trgEnrollment_Insert_Student
ON Enrollment
FOR INSERT
AS

    --Check if student has reached the three-class maximum.
    DECLARE @StudentID int
    DECLARE @CourseID int
    DECLARE @CourseCount int
    DECLARE @Quarter varchar(10)
    DECLARE @Year varchar(4)

    SELECT @StudentID = StudentID, @CourseID = CourseID
        FROM inserted

    SELECT @Quarter = Quarter, @Year = Year
        FROM Courses
        WHERE CourseID = @CourseID

    SELECT @CourseCount = Count(*) 
        FROM Enrollment AS A INNER JOIN Courses AS B
        ON A.CourseID = B.CourseID
        WHERE A.StudentID = @StudentID
        AND  B.Quarter = @Quarter
        AND B.Year = @Year

    IF @CourseCount > 3
        BEGIN
        ROLLBACK TRANSACTION
        RAISERROR 50001 'This student has reached the enrollment 
                         maximum of three courses.'
        END

    RETURN

Close the Triggers design window and save the trigger as trgEnrollment_Insert_Student.

To test the trigger, open the Students form and try to enroll a student for four classes within the same quarter and year.

For more information about triggers, type triggers in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search, or see SQL Server Books Online and search for triggers, overview on the Index tab.

Importing Data

In many cases, the data that you use to populate your tables will come from external sources, such as Excel worksheets or text files, and of course existing Access .mdb databases. Access has its own importing tool, but if you have installed MSDE, you can also use a more powerful importing tool called the Data Transformations Services Wizard. The following sections describe how to use each of these tools to import data into the State University database.

Importing Data with the Access Import Wizards

When you import data from within the Access environment, you will work with the Import dialog box, as shown in Figure 2.14. The type of import wizard used by Access is determined by the type of file that you are trying to import, which you can specify in the Files of type box.

Figure 2.14. The Import dialog box

Note When you are importing data from an Access database (.mdb file), Access will create a new table based on the table being imported. It will not allow you append the data to an existing table. To append data from an .mdb file to an existing table, you must use the Data Transformations Services Wizard that comes with MSDE.

Import Instructors Data from an Excel Worksheet

In this exercise, we will use the Access importing features to import a list of instructors from an Excel worksheet. The data will be appended to the existing Instructors table of the State University sample database.

Open the State University Access project file, StateU.adp.
On the File menu, point to Get External Data, and then click Import. The Import dialog box will be displayed.
In the Files of type box, click Microsoft Excel (*.xls).
Find the folder where you installed the State University samples files, click the Instructors.xls file, and then click Import.
In the first page of the Import Spreadsheet Wizard, select the First Row Contains Column Headings check box, and then click Next.
In the second page of the Import Spreadsheet Wizard, click In an existing table, and then click the Instructors table in the drop-down list box. Click Next.
In the next page of the Import Spreadsheet Wizard, verify that Instructors is listed as the table to import to, and then click Finish.

To make sure the data was imported, open the Instructors table. You should see an additional 10 instructor records. After importing the Instructors data, follow the same steps above to import the data in the Courses.xls file to the Courses table.

For more information about importing data by using the tools in Access, type import data in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search.

Importing Data with the Data Transformations Services Wizard

A more powerful way to import data is the Data Transformation Services (DTS) Wizard that is installed with MSDE. By using the DTS Wizard, you can import and export data between multiple heterogeneous sources by using an OLE DB-based architecture. This wizard allows you to import, export, and transform data between MSDE and any OLE DB, Open Database Connectivity (ODBC), or text file format. You can specify where the data is coming from, where it is going, and if any transformations need to occur as the data is being imported.

A transformation is a set of operations applied to source data before it is stored at the destination. For example, the DTS Wizard allows you to calculate new values from one or more source columns, or break a single column into multiple values to be stored in separate destination columns. Transformations make it easy to validate, manipulate, and enhance data during import and export.

Import Students Data from an Access Database

In this exercise, we will use the DTS Wizard to import student data from an Access (.mdb) database. The data will be appended to the existing Students table of the State University sample database.

Click the Windows Start button, point to Programs, point to MSDE, and then click Import and Export Data. The Data Transformation Services Wizard will open.
Click Next to open the Choose a Data Source page.
In the Source box, click Microsoft Access.
Click the ellipsis button to the right of the File name box and browse to the folder where you installed the State University sample files. Click the Students.mdb file, and then click Next.
In the Choose a Destination page, select Microsoft OLE DB Provider for SQL Server in the Destination box, make sure the Server box is set to your server name, provide your log-on information, and select StateU in the Database box. Click Next.

Note You may have to click the Refresh button to see the full list of available databases.
In the next page, click Copy table(s) from the source database, and then click Next.
In the Select Source Tables page, make sure that Source is Students, and that Destination is [StateU].[dbo].[Students], and then click Next.
In the Save, Schedule, and Replicate Package page, make sure the Run Immediately check box is selected, and then click Next.
In the final DTS Wizard page, click Finish.

To make sure the data was imported, open the Students table. You should see an additional 30 student records.

For more information about the DTS Wizard, see SQL Server Books Online and search for Data Transformation Services, overview on the Index tab.

Summary

This chapter has covered implementing a database design with Access and SQL Server or MSDE. In "Chapter 3: Building Microsoft Access 2000 Client/Server Applications," we will cover building forms and reports, programming, and administering and deploying Access projects. For more discussion of designing and implementing databases with SQL Server or MSDE, see the "Creating and Maintaining Databases" book in SQL Server Books Online and the list of resources in "Appendix 2: Additional Resources for Working with Microsoft Access 2000 Projects."

--------------------------------------------

The names of companies, products, people, characters, and/or data mentioned herein are fictitious and are in no way intended to represent any real individual, company, product, or event, unless otherwise noted.