Visual Designers
In Access 2000, the Objects bar in the Database window shows four types of server-side objects: tables, views, database diagrams, and stored procedures. The server-side objects that a Database window in Access 2002 shows are tables, queries, and database diagrams. (The new queries object class refers to stored procedures, views, and UDFs.)
The visual table designer in Access 2002 has a new look that experienced Access developers will find familiar. If you're unfamiliar with Access lookup columns, see the "Create a lookup column" topic in Access Help to follow step-by-step instructions for the process. To create a new table, open the designer by selecting Tables on the Objects bar and clicking New. Figure 3 shows the Lookup tab in the visual table designer. On this tab, you can specify relationships for foreign key values so that they show a meaningful string from the referenced table instead of the value for the foreign key. The OrdersWithLookups table uses a five-character field to represent customers. However, if you open the table or base a form on it, the full customer name appears instead of the five-character code. The Lookup tab shows the settings that enable this feature. As Figure 3 shows, the CustomerID column in the OrdersWithLookups table displays the second column from the Customers table. The second column in the Customers table contains customer names.
When you select Queries on the Objects bar and click New in the Database window, the New Query dialog box presents a list of design options. The options in the list vary depending on which release of SQL Server the Access project connects to. When Access 2002 connects to SQL Server 2000, users who have the appropriate permissions can create views, stored procedures, and UDFs. (SQL Server 7.0 doesn't support UDFs.) The New Query dialog box offers some pleasant surprises besides its ability to create UDFs. The new visual designer for stored procedures supports row-returning queries and queries for adding, updating, and deleting records. You can use the graphical query designer to design parameter queries for stored procedures. If you need more capabilities than the visual designer permits (e.g., conditional or looping statements), Access 2002—as earlier Access releases do—still offers a template that helps you write the T-SQL for stored procedures. Double-click Create Text Stored Procedure in the New Query dialog box to open the template.
The New Query dialog box also features the Design In-Line Function visual designer, which supports the in-line type of UDF in SQL Server 2000. This type of UDF returns a table based on one SELECT statement. Access 2002 offers text designers instead of graphical designers for the other two types of UDFs, scalar and multistatement table-valued functions. With these two types of UDFs, you can create functions that return scalar values and tables. You can make UDFs more flexible by using parameters as you do to make stored procedures dynamic and interactive.
Extended Properties
Access 2002 uses its extended property support for SQL Server 2000 to implement lookup columns for SQL Server tables. The extended property feature also supports setting validation rules (called constraints in SQL Server) from the visual table designer and designating subdatasheets. To create a validation rule, right-click a column in a table's Design view and select the Properties item from the context-sensitive menu. Select the Check Constraints tab in the Properties dialog box. You can set the constraint name and enter or edit the expression for the constraint. A Constraint expression text box lets you specify the message that appears when input to the table violates the validation rule. The Check Constraints tab also offers a drop-down list for selecting previously existing constraints. To remove a constraint, click Delete after selecting it.
Database developers can save time formerly spent on simple layout chores when a subdatasheet for a main datasheet replaces a main form or sub form. Subdatasheets work as sub forms do in main-form—sub-form combinations. Database developers, designers, or administrators can use subdatasheets to designate parent-child relationships between record sources. For example, a customer record can serve as the parent for one or more order records. Subdatasheets let you show the records related to a row in a main table. When you specify a subdatasheet, an expand control (represented by a plus sign) appears on the left edge of the record for the datasheet. You can click the plus sign in any record on the main datasheet to display the related records from the subdatasheet. This capability makes viewing and editing values in two related tables easier.
To add a subdatasheet, go to the table's Datasheet view and select Insert, Subdatasheet. Designate the name of the related record source—for example, by choosing a table. Then, from the drop-down list in the Insert Subdatasheet dialog box, specify the fields in the main and sub-record or child-record sources that you want match records on. To drop subdatasheets from a main datasheet, choose Format, Subdatasheet, Remove in a table's Datasheet view. You can also use these techniques to manage subdatasheets for other row-returning database objects (e.g., views).
MSDE 2000
Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) ships with editions of Office XP that also include Access 2002. MSDE 2000 is one of the two database engines that Access 2002 can use natively (the other one is Jet 4.0, which carried over unchanged from Access 2000). The predecessor to MSDE 2000 was the Microsoft Data Engine (MSDE) that shipped initially with Office 2000. MSDE uses the same database file format as SQL Server 7.0 does, whereas MSDE 2000 uses a new database file format associated with SQL Server 2000. The new SQL-DMO object model that ships with SQL Server 2000 installs automatically when you install MSDE 2000. MSDE 2000 supports multiple instancing, a feature introduced in SQL Server 2000.
Prev. page
1
[2]
3
4
next page 
