Creating SQL Server Tables in a Project

3 4

To create a table in a project (such as the empty Tasks project we created in the previous section), double-click Create Table In Design View on the Tables tab in the Database window, as shown in Figure 19-14. Alternatively, you can click the New button in the Database window.

figure 19-14. you can double-click create table in design view in the database window to create a new table.

Figure 19-14. You can double-click Create Table In Design View in the Database window to create a new table.

In either case, a new, blank table will open in Design view, as shown in Figure 19-15.

figure 19-15. a newly created table opens in design view.

Figure 19-15. A newly created table opens in Design view.

You can add columns (fields) to the table much as you would for an Access table, except that the Length and AllowNulls properties are columns in the grid. You need to specify a data type for each column. Table 19-2 lists the SQL Server field data types. (See Table 19-1 for a comparison of Access and SQL Server field data types.)

Table 19-2. SQL Server field data types

SQL Server

data Type

Description

bigint

Stores whole numbers from -9,223,372,036,854,775,808 through 9,223,372,036,854,775,807.

bit

Stores either a 1 or 0 value; other integer values are interpreted as 1.

char

A fixed-length data type with a maximum of 8000 ANSI characters.

datetime

A date-and-time data type ranging from January 1, 1753, through December 31, 9999, to an accuracy of 3.33 milliseconds.

decimal

An exact data type that holds values from -1038-1 through 1038-1. Allows specification of the scale (maximum total number of digits) and precision (maximum number of digits to the right of the decimal point).

float

Stores positive values from approximately 2.23E-308 through 1.79E + 308 or negative values from approximately -2.23E-308 through -1.79E + 308.

image

A variable-length data type that holds a maximum of 2,147,483,647 bytes of binary data. Used to store Binary Large Objects (BLOBs) such as pictures, documents, sounds, and compiled code.

int

Stores whole numbers from -2,147,483,648 through 2,147,483,647.

money

Stores monetary values from -922,337.203,685,477.5808 through 922,337,203,685,477.5807.

nchar *

A fixed-length data type with a maximum of 4000 Unicode characters.

numeric

An exact data type that holds values from -1038-1 through 1038-1. Allows specification of the scale (maximum total number of digits) and precision (maximum number of digits to the right of the decimal point).

nvarchar(n)

A variable-length data type with a maximum of 4000 Unicode characters.

real

Stores positive values from approximately 1.18E - 38 through 3.40E + 38 and negative values from approximately -1.18E - 38 through -3.40E + 38. Zero can also be stored.

smalldatetime

A date-and-time data type ranging from January 1, 1900, through June 6, 2079, to an accuracy of one minute.

smallint

Stores whole numbers from -32,768 through 32,767.

smallmoney

Stores monetary values from -214,748.3648 through 214,748.3647. Rounded up to two decimal places when displayed.

sql_variant

Stores data of several data types, except for text, ntext, image, timestamp, and sql_variant types. Used in a column, parameter, variable, or return value of a user-defined function.

text

A variable-length data type with a maximum of 2,147,483,647 characters; the default length is 16.

timestamp

A data type that's automatically updated every time a row is inserted or updated. Values in timestamp columns are not datetime data, but binary (8) or varbinary(8), indicating the sequence of data modifications.

tinyint

Stores whole number from 0 through 255.

uniqueidentifier

(SQL Server 7 or later)

A 16-byte GUID.

user-defined

A definition of the type of data that a column can contain, defined by the user with existing system data types. Rules and defaults can be bound only to user-defined data types.

varbinary

A variable-length data type with a maximum of 8000 bytes of binary data.

varchar(n)

A variable-length data type with a maximum of 8000 ANSI characters.

* In an Access project or SQL Server database, the "n" prefix stands for "national." It means that the data type is unicode-enabled. In an Access database, all text columns are unicode-enabled.

Figure 19-16 shows the table with four fields, one of them set as the key field. When you close the new table, you'll be asked to name it. As with naming Access tables, you may want to use the LNC tag tbl to identify tables, although this naming convention isn't as widely used for SQL Server tables as for Access tables. In this example, we'll name the table tblTasks.

figure 19-16. a new table has four fields of different data types.

Figure 19-16. A new table has four fields of different data types.

To aid the discussion of setting up relationships between project tables, let's follow the same procedure discussed earlier to create another table named tblPeople. Add to the table two columns: Person and Available. Set both columns' Data Type property to char and the Length property to 50. Set the Person column as the primary key column.

Column Properties

The column properties for the columns in project tables are set on the Columns tab in the Table Designer, as shown earlier in Figure 19-16. The more commonly used properties are listed here. For information about the more obscure properties, see the "Columns Property Page" Help topic.

  • Default Value. The default value for the column. You can enter this value as text or select it from a drop-down list of global default values.
  • Precision. The maximum number of digits for values in the column.
  • Scale. The maximum number of digits to the right of the decimal point for values in the column.
  • Identity. Specifies whether the column is used as an identity column, with a value of Yes, No, or Yes (Not For Replication).
  • Identity Seed. The seed value of an identity column whose Identity option is set to Yes or Yes (Not For Replication).
  • Identity Increment. The increment value of an identity column whose Identity option is set to Yes or Yes (Not For Replication).
  • Is RowGuid. Specifies whether the column is used by SQL Server as a ROWGUID column. Can be set to Yes only for an identity column.
  • Formula. The formula for a computed column.
  • Collation. The collating sequence that SQL Server applies by default to the column if the column values are used to sort rows of a query result.
  • Format. The display format for the column.
  • Decimal Places. The number of decimal places to use when displaying values in the column.
  • Input Mask. An input mask (or field template) that determines what characters can be entered into a field and optionally supplies literal display characters.
  • Caption. The text in the label attached to text boxes bound to the column.
  • Indexed. Specifies whether there's an index on the column, with a value of No, Yes (duplicates OK), or Yes (no duplicates).
  • Hyperlink. Specifies whether column values can be interpreted as hyperlinks.

The Lookup tab for project columns works the same way as the equivalent feature for Access database table fields.

See Chapter 4, "Creating a Database," for details on creating a lookup field.

Check Constraints

Constraints in an Access project table correspond to validation rules in an Access database table. You set them on the Check Constraints tab in the table properties sheet. Figure 19-17 shows the constraint for the emp_id key field of the Employee table in the Publications project we created earlier. The Constraint expression corresponds to the database Validation Rule property, and the Validation text corresponds to the database property of the same name.

figure 19-17. a check constraint for a project table field corresponds roughly to a validation rule for an access table field.

Figure 19-17. A check constraint for a project table field corresponds roughly to a validation rule for an Access table field.

Triggers

Triggers for project tables are a special type of stored procedure. Triggers somewhat resemble event procedures for Access forms, reports, and controls except that they fire when data is modified by an INSERT, an UPDATE, or a DELETE statement. Triggers, like stored procedures, are written in Transact-SQL code.

caution


Some of the Access database hot keys don't work in projects (for example, F4 to open a properties sheet and Ctrl+Z to undo an action), and Undo is implemented only for the last action, if it works at all.

Figure 19-18 shows the ALTER trigger for the Employee table in the Publications project. To open this trigger, click the Tables tab in the Database window, right-click the Employee table, and choose Triggers from the context menu. Click Edit in the Triggers For Table Employee dialog box. If you want to create or delete a trigger, click New or Delete in this dialog box.

figure 19-18. the employee table in the publications project has an alter trigger.

Figure 19-18. The Employee table in the Publications project has an ALTER trigger.

Primary Keys and Indexes

To set a column as a primary key in a project table, select the column, and click the Primary Key button on the Table Design toolbar, as shown in Figure 19-19.

figure 19-19. click the primary key button on the table design toolbar to set a field as the primary key for a project table.

Figure 19-19. Click the Primary Key button on the Table Design toolbar to set a field as the primary key for a project table.

note


Afield must have its Allow Nulls property set to False before you can set it as the primary key field.

To index a column in a project table, select one of the available choices for its Indexed property: No, Yes (Duplicates OK), or Yes (No Duplicates). Figure 19-20 shows that you can select an index type for the Due column in the tblTasks table in the Tasks project we created earlier.

figure 19-20. you can select an index type for a column in a project table.

Figure 19-20. You can select an index type for a column in a project table.

Troubleshooting - I opened a project, but I don't see any tables in the Database window, and the project's name has disconnected after it

SQL Server might not be set to run when Windows is started. To connect your project to its back-end SQL Server database, select Service Manager from the Microsoft SQL Server program group (or click its icon in the Windows system tray), and click the Start/Continue button to start SQL Server, as shown in Figure 19-21. After you close and reopen the project, it will be connected to its SQL Server back-end database and you can work with the tables and other SQL Server objects.

figure 19-21. start sql server in the sql server service manager dialog box.

Figure 19-21. Start SQL Server in the SQL Server Service Manager dialog box.

Setting Up Relationships Between Tables

You can create a relationship between tables in a database diagram. The technique is slightly different from the technique used in the Relationships window in an Access database. For this example, we'll use the Tasks project created earlier.

To link two project tables in a database diagram, follow these steps:

  1. Click the New button on the Database Diagrams tab in the Database window to create a new, blank database diagram.
  2. The new diagram will open with the Add Table dialog box over it. Select tblPeople and tblTasks, and click Add to add them to the diagram, as shown in Figure 19-22. Then close the Add Table dialog box.

    figure 19-22.you use the add table dialog box to add a table to a database diagram.

    Figure 19-22. You use the Add Table dialog box to add a table to a database diagram.

  3. Click the row selector (the gray square to the left of the column name) of the Person column in tblPeople and drag it to the AssignedTo column in tblTasks.
  4. In the Create Relationship dialog box that appears, select one or more check boxes, if desired, to verify data or apply referential integrity, as shown in Figure 19-23, and then click OK.

    figure 19-23.you can select relationship options in the create relationship dialog box.

    Figure 19-23. You can select relationship options in the Create Relationship dialog box.

  5. The tables will appear linked in the database diagram, as shown in Figure 19-24.

    figure 19-24.linked tables are displayed in a project database diagram.

    Figure 19-24. Linked tables are displayed in a project database diagram.

note


Unlike links in the Access Relationships window, the join lines in a project database diagram aren't attached to the specific linking fields-they're attached only to the table as a whole.

You can also create relationships between project tables on the Relationships tab in the table's properties sheet. To create a relationship between the AssignedTo column of tblTasks and the Person column of tblPeople, follow these steps:

  1. Open tblTasks in Design view.

    note


    You must open the table on the foreign side of the relationship to create a relationship in the properties sheet.

  2. Press Alt+Enter to open its properties sheet.
  3. On the Relationships tab in the properties sheet, click New. A new relationship (FK_tblTasks_tblPeople) will appear in the Relationship name box and the Selected relationship drop-down list, with tblPeople selected as the primary key table and tblTasks selected as the foreign key table.
  4. Select Person as the column for the primary key table and AssignedTo as the column for the foreign key table, and select or clear the options at the bottom of the properties sheet, as shown in Figure 19-25.

    figure 19-25.you can create a relationship between project tables in the table's properties sheet.

    Figure 19-25. You can create a relationship between project tables in the table's properties sheet.

There's no OK button to create the relationship; saving the table saves the new relationship.



Microsoft Access Version 2002 Inside Out
Microsoft Access Version 2002 Inside Out (Inside Out (Microsoft))
ISBN: 0735612838
EAN: 2147483647
Year: 2005
Pages: 172
Authors: Helen Feddema

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