Programming Microsoft Access 2000 (Microsoft Programming Series)

Data access pages are data-bound HTML files. In many ways, they look and behave like a cross between a traditional Access report and a form because you can use it to browse through the records in a data source. The page's database connection enables users to add, update, and delete data. Typically, but not necessarily, the data is from a database object in the current database file. You can use the Sorting And Grouping tool to hierarchically display data—for example, in reports that have multiple, nested bands or group headings. The Pages icon in the database window represents the data access page objects associated with a database. But Access does not store pages in the database; they are separate DHTML files in the file system—for example, in a folder on your intranet server. The Pages collection in a database file is a collection of hyperlinks to the DHTML files. You create pages within Access, and users can then open them in Internet Explorer 5 or Access 2000.

NOTE

Creating a Data Access Page

Access 2000 contains wizards for basing a new data access page on either a tabular or columnar layout. You can also base a new page on an existing Web page. However, many will prefer to create a data access page by opening a blank page in Design view and populating it with controls from the Field List dialog box (shown in Figure 13-10).

To populate a page, you can open the Tables or Queries node to reveal the table names in the active connection. If you see a table from which you want to select values for the page, you can expand the table to view the individual fields. You can then select fields by dragging and dropping them on the page. Alternatively, you can populate a page with all fields in a table by dragging a table name to the page. You can then choose to arrange fields in a columnar or pivot table layout. You can perform the same kind of operations with queries.

NOTE

Figure 13-10. You can use the Field List dialog box to populate a blank data access page with controls bound to database fields.

To change the active connection, right-click on the database connector in the top left corner of the Field List dialog box and choose Connection from the shortcut menu. In the Data Link Properties dialog box that appears, select an OLE DB connection driver and a database consistent with that driver. When you close the dialog box, the Field List dialog box shows the tables and queries from the new database source.

Creating and Using a Simple Columnar Page

Figure 13-11 shows a simple data access page that serves as a form for the FamilyMembers table in a database file. Four fields appear on the form. A control below the fields supports navigation and other functions. Clicking the two navigator buttons on either side of the record indicator window moves the current record's position back and forth in the underlying recordset.

Figure 13-11. This data access page serves as a form. Users can browse, add, delete, edit, sort, and filter records using the navigation control below the fields.

The first six buttons to the right of the indicator for the record source and number are as follows, from left to right:

• Next Record button. Moves to the next record.
• Last Record button. Moves to the last record.
• New Record button. Adds a record.
• Delete Record button. Deletes the current record.
• Save Record button. Saves any changes to the current record.
• Undo Record button. Undoes any unsaved changes to the current record.

If you enter a record that you later want to remove, use the Delete button to delete it. You can also edit records by simply typing over them, adding new content, or removing existing content. If you want to preserve your revisions, click Save Record. Otherwise, click Undo Record.

The remaining buttons on the navigation control (except the Help button on the far right) are for sorting and filtering:

• Sort Ascending and Sort Descending buttons. Specify the sort direction. To sort on the values in a field, click in that field for any record, and then click one of these buttons.
• Filter By Selection button. Applies a filter based on the current value in a field. Click in the field to choose a value for filtering (for example, the Lname field with a value of Dobson), and then click Filter By Selection. The display changes to show just records matching the previously selected filter value. For example, if you choose Dobson for Lname in Figure 13-11, the record indicator changes from FamilyMembers 1 of 5 to FamilyMembers 1 of 2.
• Toggle Filter button. Applies or removes the filter.

All this functionality is available from within Access as well as from a browser on an intranet. Data access pages display best and have all their features function with Internet Explorer 5. Various degrees of degradation occur with Internet Explorer 4. Users of browsers other than Internet Explorer 5 must also obtain ActiveX controls for reading and working with a data access page.

Grouping Records

One powerful feature of data access pages is their ability to group records and conditionally expand a group to show the individual entries within. Figure 13-12 shows this capability. The page shown in the figure groups records from the FamilyMembers table by the Lname field. All records from the table with the same last name group together. When a user clicks the gray expand button (it changes from + to _), a second, nested navigation control appears for moving through the records with the same last name. Clicking the outer navigation control closes the expanded display and moves to the first record with the next last name.

Figure 13-12. You can group and conditionally expand records within groups on data access pages.

You can show more than a single record on a page. You use the Data Page Size setting in the Sorting And Grouping window to specify how many records to show at a time. (See the bottom window in Figure 13-13.) This window lets you set the page size independently for the grouping field as well as the fields within a group. You can use the Grouping And Sorting window for data access pages to control whether groups have header and footer sections, just like you use the corresponding window for reports. You can use the Group On setting to specify intervals, ranges of values, and prefixes for grouping fields.

Figure 13-13. In Design view, you can add Expand controls to let users interact with your pages and conditionally expand them to see the records within a group.

The top window in Figure 13-13 shows the Design view of the page in Figure 13-12. Notice that it contains two navigation controls. You can assign a grouping function to a field by selecting the field and clicking the Promote button (the left-pointing arrow) on the Page Design toolbar. When the record source for a page derives from a one-to-many relationship, you can use the Sorting And Grouping tool to group all the fields from the one side of the relationship over the fields on the many side. Access sorts and groups the records by the primary key for the one side of the relationship in this case.

Office 2000 Web Components on Data Access Pages

Three Office 2000 Web Components ship with Office 2000 (the Standard, Professional, Premium, and Developer editions): spreadsheet, chart, and PivotTable list components. You can use these to complement and extend the basic database functionality provided by data access pages. If a site has a license that permits intranet distribution, the site administrator can configure browsers under the license to automatically download and configure the Office Web Components the first time they load a page using a component. (For more information on configuring Office Web Components, see the Microsoft Office 2000 Resource Kit.)

An Office spreadsheet sample

Figure 13-14 shows one use of a spreadsheet component on a data access page. Controls on the page show the CategoryName, ProductName, and ProductSales fields for the Sales By Category query from the Northwind database. This query computes sales by product in 1997 for each product. The query lists the category ID and name of each product along with its sales.

Figure 13-14. The spreadsheet component on this data access page uses sales from the database for 1997 to project sales through 2001.

The sample in Figure 13-14 extends the basic query by projecting sales from 1998 through 2001. First, the component copies the current value of the ProductSales control from the page to the spreadsheet. Then it applies a progressive series of growth rates to sales starting with 1997. These rates increase sales from one year to the next. While the growth rates are the same for all products, the actual sales levels vary between products because the 1997 sales are different for each product. Finally, to protect the formulas from damage, you can lock selected spreadsheets to block users from inadvertently changing them.

Figure 13-15 shows the Design view of the data access page in Figure 13-14. Notice that a spreadsheet appears below the ProductsSales control. You can insert an Office 2000 Spreadsheet Component using the Insert-Office Spreadsheet command and adapt the component for use in your application by right-clicking it and choosing Property Toolbox. The spreadsheet in Figure 13-15 hides the toolbar, title bar, column headers, and row headers. In addition, the component's horizontal and vertical scrollbar settings are set to False.

Figure 13-15. The Design view of the data access page from Figure 13-14.

The top sales cell in the spreadsheet shows a formula that updates with each new record on the data access page: =document.productsales.value. The term document references a data access page. The term productsales references a specific control on the page. Finally, the value property references the current value of the control on the data access page. This value changes only if the user moves to a new record in the page's underlying record source.

The sales entries for 1998 through 2001 all evaluate to #VALUE! in Design view. This is not an error. It results from the fact that there is no value for 1997 in Design view. Recall from Figure 13-14 that there are legitimate values for these cells in Page view. This results from the formula in the top spreadsheet cell evaluating to the current value of productsales on the data access page.

An Office chart sample

The sample found in the section "Programmatic Issues for Data Access Pages" builds on the preceding one by adding an Office 2000 Chart Component that charts the values in the spreadsheet. The chart shows a graphical depiction of how sales grow over time for each product. Figure 13-16 shows a product with its spreadsheet projections and graphical depiction to the right side of the page. The chart dynamically updates each time the values in the spreadsheet change.

Figure 13-16. The Office 2000 Chart Component on a data access page accepts values from a Spreadsheet Component that changes its sales projections when a user moves off the current record.

To add a chart to a page, select the area of the page where you want the chart to appear, and then choose Insert-Office Chart. The command starts a wizard that walks you through the process of formatting a chart. You can select a chart type, choose a record source for the chart, and set values to display in the chart. After you finish using the wizard, you can right-click in the chart and choose Property Toolbox to edit your choices or specify other options that were not explicitly presented at setup time.

An Office PivotTable list sample

The PivotTable list control lets users sort, group, filter, outline, and manipulate data. It can also work with data from more providers than a normal data access page or other Office 2000 Web Components can. Its data sources can include a worksheet, a database, and a multidimensional data cube.

Figure 13-17 shows a PivotTable list control on a data access page that is based on the Orders table in the NorthwindCS database. It counts orders by ShipCountry, CustomerID, and EmployeeID. You can click the Expand control next to the EmployeeID and CustomerID fields to expand the table within that column or row. In addition, you can selectively show a subset of the table by opening the list of elements comprising all countries, customers, and employees and selecting just one or a few from each set. A toolbox along the top of the PivotTable list presents still more analysis options, such as filtering and sorting.

Figure 13-17. A PivotTable list on a data access page that displays data from the NorthwindCS database.

Programmatic Issues for Data Access Pages

You can program solutions with data access pages at several different levels. Data access pages can also serve as hosts for Office Web Components. Both data access pages and Office 2000 Web Components have object models that you can use to develop programmatic solutions. You can also code solutions in VBA or a Web scripting language, such as VBScript.

This section explores solutions based on VBA, the AllDataAccessPages collection, and the DataAccessPage object. The AllDataAccessPages collection works like the AllForms and AllReports collections. Its members are not database objects, but rather AccessObject objects. These objects are available whether or not a data access page is open. The AllDataAccessPages collection lets you track the full set of all data access pages associated with a database project. The following short VBA procedure lists all the data access pages in a project and notes whether they are open.

The FullName property of an AccessObject object in the AllDataAccessPages collection has a special meaning. Recall that pages are not stored as objects in the database file; they are separate DHTML files. The location of the pages can be anywhere on a LAN. The FullName property indicates the path and filename for a data access page. The next procedure lists all the pages in a project and itemizes them by their Name and FullName properties. The Name property value is the shortcut name for the data access page that appears in the database window.

The only method for a DataAccessPage object is ApplyTheme. You can use this method to automate the application of a Microsoft Office theme to the pages that members of the AllDataAccessPages collection point to. This method works properly only if the page is open in design mode. The following two procedures assign a theme to all the data access pages in a project whether or not they are open and whether or not they are open in design mode. The procedures also restore each data access page to its former open and current view status before applying the theme. If you do not like a theme selection, you can rerun the callSetTheme procedure with an argument of Blank.