CSCI A114 / INFO I111 Lab Notes Six First semester 2000-2001

We are resuming Project 3 from the point where we specified referential integrity using the Relationship command by specifying the relationship between the Technician and Marina tables. Specifying the relationship between the two tables allows us to use subdatasheets.

Steps: To Use a Subdatasheet

1. With the Database window open and the Tables object selected right-click Technician.
2. Click Open on the shortcut menu.
3. Point to the plus symbol in front of the row for technician 36.
4. Click the plus symbol in front of the row for technician 36. The subdatasheet displays. It contains only those marinas that are assigned to technician 36.
5. Click the minus symbol to remove the subdatasheet and then close the datasheet for the Technician table by clicking its Close button on the title bar. The datasheet no longer displays.

1. Open the Marina table in Datasheet view, and then click the Name field on the first record (any other record would do as well). Point to the Sort Ascending button on the Table Datasheet toolbar.
2. Click the Sort Ascending button on the toolbar. The rows now are ordered by name.

1. Click the field selector at the top of the Marina Type field column to select the entire column.
2. Hold down the SHIFT key and then click the field selector for the Warranty field column. Release the SHIFT key.
3. Click the Sort Ascending button on the Table Datasheet toolbar. The rows are ordered by marina type. Within each group of marinas of the same type, the rows are ordered by the warranty amount.
4. Close the Marina: Table window by clicking its close button on the title bar. Click the No button to abandon changes. The next time the table is open. the records will display in their original order.

1. Right-click Marina. Click Design View on the shortcut menu, and then, if necessary, maximize the Marina: Table window. Click the row selector to select the Name field. Click the Indexed text box in the Field Properties pane. Click the Indexed text box down arrow.
2. The Indexed list displays. The items in the list are
   • No (no index)
   • Yes (Duplicates OK) (create an index and allow duplicates) and
   • Yes (No Duplicates) (create an index but reject (do not allow) duplicates).
3. Click the Yes (Duplicates OK) item in the list. The index on the Name field now will be created and is ready for use as soon as you save your work.

1. Point to the Indexes button on the Table Design toolbar. (Can you find it?)
2. Click the Indexes button on the toolbar. The Indexes: Marina dialog box displays. It shows the indexes that already have been created and allows you to create additional indexes.
3. Click the blank row (the row following Name) in the Index Name column in the Indexes: Marina window. Type TypeWarranty as the index name and then press the TAB key. Point to the down arrow in the Field Name column.
4. The index name has been entered as TypeWarranty. An insertion point displays in the Field Name column. The index on the Marina Number field is the primary index and was created automatically by Access. The index on the Name field is the one just created. Access created other indexes (for example, on the Zip Code field) automatically. In this dialog box, you can create additional indexes.
5. Click the down arrow in the Field Name column to produce a list of fields in the Marina table, scroll down the list, and then select the Marina Type field. Press the TAB key three times to move to the Field Name column on the following row. Select the Warranty field in the same manner as the Marina Type field.
6. Marina Type and Warranty are selected as the two fields for the TypeWarranty index. The absence of an index name on the row containing the Warranty field indicates that it is part of the previous index named, TypeWarranty.
7. Close the Indexes: Marina dialog box by clicking its Close button on the title bar, and then close the Marina: Table window by clicking its Close button on the title bar. When the Microsoft Access dialog box displays, click the Yes button to save your changes. The indexes are created and the Database window displays.

Project 3 Summary

In Project 3, you learned how to maintain a database. You saw how to use Form view to add records to a table. You learned how to locate and filter records. You saw how to change the contents of records in a table and how to delete records from a table. You restructured a table, both by changing field characteristics and by adding a new field. You saw how to make changes to groups of records and how to delete groups of records. You learned how to create a variety of validation rules to specify a required field, specify a range, specify a default value, specify legal values, and specify a format. You examined the issues involved in updating a table with validation rules. You also saw how to specify referential integrity. You learned how to view related data by using subdatasheets. You learned how to order records. Finally, you saw how to improve performance by creating single-field and multiple-field indexes.

Publishing to the Internet Using Data Access Pages

CASE PERSPECTIVE

Bavant Marine Services is pleased with all the work you have done for them thus far.

They appreciate the database you have created and the ease with which they ca query the database. They find the default values, the validation rules, the validation text, and the relationships you created to be useful in ensuring that the database contains only valid data.

They also find the report you created for them in Project 1 to be very useful. They are also very pleased with the form you created for them in Project 1.

They have used it to view and update marina data. They would like to use a web page that would be similar to this form in order to view and/or update marina data over the Internet.

They would like you to develop a sample of such a Web page for their review. If satisfactory, they will instruct the network administrator to make both the database and your Web page accessible on the Internet.

Steps: To Create a Data Access Page

1. Open your BMS database. With the Marina table selected, click the New Object: AutoForm button arrow on the Database window toolbar. The list of available objects displays.
2. Click Page. When the New Data Access Page dialog box displays, click Page Wizard and then point to the OK button. The New Data Access Page dialog box displays with Page Wizard selected.
3. Click the OK button. Point to the Add Field button. The Page Wizard dialog box displays. The fields in the Marina table display in the Available Fields box. The Marina Number field currently is selected.
4. Click the Add Field button to add the Marina Number field to the Selected Fields box. Click the Add Field six more times to add the Name, Address, City, State, Zip Code, and Marina Type fields. Point to the Next button.
5. The Marina Number, Name, Address, City, State, Zip Code, and Marina Type fields are added to the Selected Fields box.
6. Click the Next button. The next Page Wizard dialog box displays.
7. Click the Next button because no grouping levels are needed. Click the Next button a second time because no changes are needed to the sort order. Point to the Finish button.
8. Click the Finish button. The Page1: Data Access Page displays. The process of creating a data access page may take several seconds.
9. If necessary, click the up arrow on the vertical scroll bar to display the top of the screen. Click anywhere on the portion of the screen labeled "Click here and type title text" and then type Bavant Marine Services as the title text.
10. The data access page displays. The title is changed to Bavant Marine Services.
11. Click the Close button on the Page1: Data Access Page title bar to close the window. When the Save As Data Access Page dialog box appears, type Marina as the name of the page and click Save. If necessary, click 3¹/₂ Floppy (A:) in the Save in box to save your page to the same location as your database.
12. The data access page is created and saved as Marina.

1. With the Database window open, click the Pages object. The data access page objects display. Point to Marina.
2. Right-click Marina and then click Web Page Preview on the shortcut menu. The page displays in a maximized Internet Explorer window.
3. Click the Close button on the Internet Explorer window title bar to quit Internet Explorer. The page no longer displays. Click the Close button on the Microsoft Access window title bar to quit Access. The database is closed.

To use the data access page, start Internet Explorer, type the location of the data access page and then press the ENTER key. The page then will display. To ensure that the data displays in marina number order, click the Sort Ascending button on the record navigation toolbar.

You can use the navigation buttons just as you do when viewing a datasheet or a form in Access. You can get help on the way you use the page by clicking the Help button. (Can you find it?) Details concerning the use of the page then will display. Clicking the plus symbol in front of a category will change the plus symbol to a minus symbol and display all the topics withing the category. Clicking the question mark symbol in front of a topic will display details concerning the topic. Maximize the Help window to make it easier to read information.

Web Feature Summary.

In this Web Feature, you learned how to create a data access page to enable users to access the data in a database via the Internet. You worked with the Page Wizard to create such a page. You then previewed the data access page from within Access. Finally, you saw how to use the data access page. This page will enable Bavant to access their database using the Internet.