Creating Data Access Pages

3 4

Access 2002 gives you several different choices when creating data access pages. After you start the process by choosing Pages in the database window Objects bar and clicking New, you select the style that fits the data you want to publish to the Web. Unlike the other Access components, in which the New Form (or New Report) dialog box offers you all the available layout selections, data access page layout choices are divided between two dialog boxes: the New Data Access Page dialog box and the Layout Wizard dialog box, which appears when you drag fields to a new data access page.

Table 18-1 lists all the available options for creating data access pages.

note

---

One selection (Columnar) is available in both dialog boxes; another (Save As Data Access Page) is not available in either dialog box.

Table 18-1. The Data Access Page Creation Options

Creation Option	Available From
Columnar	AutoPage: Columnar selection in the New Data Access Page dialog box Columnar selection in the Layout Wizard
Tabular	Tabular selection in the Layout Wizard
Banded	Page Wizard selection in the New Data Access Page dialog box
Free-Form	Design View selection in the New Data Access Page dialog box
PivotTable	PivotTable selection in the Layout Wizard
PivotChart	PivotChart selection in the Layout Wizard
Office Spreadsheet	Office Spreadsheet selection in the Layout Wizard
From Existing Web Page	Existing Web page selection in the New Data Access Page dialog box
Saving a Form as a Data Access Page	Choose File, Save As from an open Access form

To create a new data access page, either click Pages in the database window Objects bar and then click the New button, or click the New Object selector and select Page from the drop-down list, as shown in Figure 18-4.

Figure 18-4. You can create a data access page based on a table using the Page item in the New Object selector drop-down list.

The New Data Access Page dialog box opens, as shown in Figure 18-5.

Figure 18-5. You can use the New Data Access Page dialog box to create a data access page.

The New Data Access Page dialog box offers you the following four choices:

• Design View. Opens the data access page Designer to a blank page; also gives you access to the Layout Wizard, which contains more data access page layout choices.
• Existing Web Page. Opens a saved data access page from its HTM file.
• Page Wizard. Starts the Page Wizard, which assists you in creating a banded data access page.
• AutoPage: Columnar. Automatically creates a columnar data access page.

As mentioned earlier, the Layout Wizard offers the other choices for creating data access pages. To use this wizard, start a data access page by using the Design View selection in the New Data Access Page dialog box, and then drag a table or query from the field list to the data access page grid. The Layout Wizard displays, as shown in Figure 18-6.

Figure 18-6. You can also create a data access page using the Layout Wizard.

The Layout Wizard offers you the following five choices:

• Columnar. The data access page's fields are arranged in a vertical column.
• Tabular. The data access page looks like a table, with labels used as column headings.
• PivotTable. The data access page has a PivotTable control, with the selected fields used as columns and rows.
• PivotChart. The data access page has a PivotChart control, with fields used as series and categories.
• Office Spreadsheet. The data access page has an Office Spreadsheet control filled with data from the selected fields.

PivotTables and PivotCharts on Data Access Pages

Although both Access forms and data access pages can host PivotTables and PivotCharts, you create these Office Web controls somewhat differently on forms and data access pages. When you create a new form, the New Form dialog box offersyou (among other choices) a choice of AutoForm: PivotTable, AutoForm: PivotChart, and PivotTable Wizard. These selections provide assistance (more in the case of PivotTables) with setting up a form with a PivotTable or PivotChart, as described in Chapter 12, "Using PivotTables and PivotCharts to Analyze Data." The form itself is bound to data, which can be displayed in either PivotTable or PivotChart view.

With data access pages, on the other hand, the New Data Access Page dialog box offers no choices for PivotTables and PivotCharts. You place PivotTables, PivotCharts, and Office Spreadsheets on a data access page by clicking the corresponding tool in the Toolbox, or by dragging a table or query to the data access page design grid and selecting PivotTable, PivotChart or Office Spreadsheet in the Layout Wizard.

Note that you can't create a PivotTable or PivotChart on an Access form and then copy it to a data access page; you must re-create these components on a data access page.

Creating Form-Type Data Access Pages

As mentioned, data access pages combine the features of both forms and reports in one object. However, they are neither a form nor a report. If you want to create a data access page that looks like a form and displays one record of information at a time, you can choose the AutoPage: Columnar option in the New Data Access Page dialog box. This option will help you create a form with fields displayed in a vertical column; the Columnar selection in the Layout Wizard offers you the same choice, but with your choice of fields.

If you want a data access page that looks as much as possible like a regular Access form (perhaps to provide Internet users with a familiar interface), the option to save a form as a data access page is the best way to go. This new option allows you to convert a form into a virtually identical data access page, so users of your applications can review, add, and analyze data in the form over an intranet or the Internet.

To create a columnar data access page, just select the AutoPage: Columnar selection from the New Data Access Page dialog box with a table selected. Figure 18-7 shows a data access page based on tblTasks (see the sidebar "Creating Table Relationships On the Fly in Data Access Pages") created using this selection.

Figure 18-7. This columnar data access page was automatically created from tblTasks.

From Figure 18-7 you can see that the value in the ID field is right-aligned and the field is a little bit too wide. You will probably want to make the TaskID field narrower and left-aligned. To make the text box narrower, switch to Design view, click the TaskID text box so that its sizing handles appear (they are small white squares at the corners and midpoints of the text box), grab the handle on the right side of the text box and drag it inwards until the text box has the right width. To make its text left-aligned, with the text box selected, click the Align Left button on the toolbar. (These properties can also be set in the text box's properties sheet, which can be opened by clicking the Properties button on the toolbar.).

Working with controls on data access pages is now quite similar to working with controls on forms or reports, so you can generally use the techniques described in Chapter 8, "Using Design Tools," when working with controls on data access pages.

To create a columnar data access page using the Layout Wizard, create a new data access page using the Design View selection in the Data Access Page dialog box, drag a table or query from the field listField list to the data access page's design grid, and then select the Columnar selection from the Layout Wizard. The only difference between the two methods is that the Layout Wizard Columnar selection allows you to select a subset of the fields in a data source, while the New Data Access Page AutoPage: Columnar selection automatically places all the fields in the selected table or query on the data access page.

If you want complete control over the placement of fields on a data access page, select the Design View choice in the New Data Access Page dialog box, and drag fields to the design grid from the Field List, placing them as desired on the data access page design grid.

Creating Table Relationships On the Fly in Data Access Pages

If you preselect a table or query before you create a new data access page, the selected table will be expanded in the Field List, ready for you to drag fields from it to the data access page design grid. However, unlike Access forms and reports, data access pages allow you to drag fields from any table in the current database to the data access page, as long as those fields can be related to the original table.

In the Modified Northwind database available on the companion CD, for example, tblTasks has a field (Assigned Person) that can be related to the Person field in tblPersons. After you drag fields from tblTasks to the design grid, dragging a field from tblPersons to a data access page opens the Layout Wizard first. Choosing a type of data access pageand then clicking OK opens the Relationship Wizard (shown in Figure 18-8), which allows you to create a link between the two tables on the fly.

Figure 18-8. You can set up a relationship in the Relationship Wizard after dragging a field from another table to the data access page grid.

Relationships set up in the Relationship Wizard opened from a data access page do not appear in the regular Relationships window or as links when you create queries based on these tables.

Generating Banded Data Access Pages

In Access 2002, you can create a data access page with a hierarchical structure by grouping records on it into specific categories. Grouping records on a data access page is similar to grouping records on a report. To create a banded (also called grouped) data access page, you can either use the Page Wizard selection in the New Data Access Page dialog box, or select the Create Data Access Page By Using Wizard shortcut in the Pages tab of the database window.

To create a banded data access page by using the Page Wizard, follow these steps:

1. Create a query containing all the data you want to display on the data access page. If all the data you need is available in one table, you can use the table as a data source.

   

   Figure 18-9 shows a sample query called qryNorthwindAll based on several Northwind tables. This query is located in the Modified Northwind.mdb database.

   

   Figure 18-9. This query is intended for use as a data access page data source.

2. Select the table or query to use as the data access page's data source in the database window, click the New Object selector, and select Page from the drop-down list.

   The New Data Access Page dialog box opens with qryNorthwindAll selected.

3. Select the Page Wizard option to start the Page Wizard.
4. Select the fields you want to use on the data access page, as shown in Figure 18-10, and click Next.

   

   Figure 18-10. The first page of the Page Wizard looks like this.

   Selecting Salesperson, ProductName, Customer, OrderDate and Price allows you to create a banded page using several categories.

5. Select grouping levels, as shown in Figure 18-11. Click Next.

   

   Figure 18-11. Select groups for a banded data access page on the second page of the Page Wizard.

6. Select sort fields for the data access page groups from the drop-down list of the remaining fields in the data source (those not already selected as grouping fields). Note that the sort order is Ascending by default. To sort in descending order, click the Ascending button to the right of a sort field; it will change to Descending.
7. Name your data access page. (See Figure 18-12.)

   

   Figure 18-12. The final page of the Page Wizard allows you to specify the page title and other options.

   You have several options before finishing the wizard: You can choose to open the page in Page view or Design view, you can select a theme for the page, and you can display Help for working on a data access page. When you've made your selections, click Finish.

note

---

The dap tag in the page title is used in the LNC naming convention (see "Naming Conventions" for details on using the LNC naming convention).

If you choose to apply a theme, the Theme dialog box will appear when you click Finish. Figure 18-13 shows the Canvas style. If you click Set Default, the Canvas style will remain the default, so that all the data access pages you create in this database will have a consistent appearance.

Figure 18-13. You can choose to apply a theme to your data access page.

Figure 18-14 shows the data access page open in Design view with a placeholder at the top where you can enter a title.

Figure 18-14. The data access page is now banded and includes a field list.

tip

---

Each group level (band) is indented under the band above it. The navigation bars for the bands are at the same indent level as their data, in reverse order.

After you type in the title, you can make changes in Design view. For example, you could add space to the labels and change the size of the controls. When you switch to Page view, the data access page appears with the first product name visible, in collapsed mode, as shown in Figure 18-15.

Figure 18-15. The new data access page is collapsed to show the top level only.

If you click the plus sign to the left of the ProductName label, you'll expand the next level, showing the Customer data; clicking the plus sign to the left of the Customer label expands the Detail group, showing the Salesperson, Order Date, and Price data, as shown in Figure 18-16.

Figure 18-16. In Page view, the new data access page looks like this with all bands of data expanded.

The data access page has three navigation bars that correspond to the Product Name group, the Customer group, and the Detail area. These navigation bars also have buttons for filtering and sorting the data in each group. You can see the number of records in each child group in the navigation bars. For example, the Alice Mutton product has 20 customers and the selected customer (Antonio Moreno Taqueria) has one detail record.

Creating Tabular Data Access Pages

If you want to create a table-type data access page, you can also use the tabular layout selection. As the name suggests, a tabular data access page looks like a table, with labels used as column headings. To create a data access page with a table displaying data fromselected fields, select Design View in the New Data Access Page dialog box, and then drag a table or query from the field list to the data access page grid. When Access displays the Layout Wizard, select Tabular. Figure 18-17 shows a tabular data access page created by dragging tblTasks to the data access page design grid.

Figure 18-17. This tabular data access page displays all fields from tblTasks.

Figure 18-18 shows the tabular data access page with a title added, in Page view.

Figure 18-18. The tabular data access page looks like this in Page view.

Adding PivotTables, PivotCharts, and Spreadsheets to Your Data Access Pages

If you want the viewers of your data access page to be able to dynamically rearrange summarized data on the data access page, use a PivotTable (or a PivotChart if you want to display the data graphically). These selections let users decide for themselves what information they want in rows, and what information in columns, and how they want to analyze or chart it. For example, one user might want to see salespersons' names as rows, and sales by month in columns, while another user might want to see summarized quarterly sales in rows, and salespersons in columns.

With a PivotTable, each user can decide for himself or herself how to arrange the data, without having to switch to Design view and modify the data access page (which in any case might not be possible, if the user only has read-only permissions for working with the data access page). Similarly, with PivotCharts users can select different chart and graph types, and swap the data displayed on the horizontal and vertical axes, without the need to redesign the data access page.

For a familiar worksheet look, you can use the Office Spreadsheet. You can create data access pages with any of these three Office Web Components by creating a new data access page in Design view and then placing a PivotTable, PivotChart, or Office Spreadsheet control on the design grid. However, the process of creating these controls and filling them with data is generally simpler if you choose the appropriate selection from the Layout Wizard, which appears when you drag a table or query from the data access page's field list to the design grid.

InsideOut

You can't copy a PivotTable or PivotChart from an Access form to a data access page, but if you take the time to create a select query that includes only the data you need in the PivotTable, and does some preprocessing of the data using date and string conversion functions, you can use that same query as the data source for a PivotTable on a data access page, thereby saving you a good deal of time when you place a PivotTable control on a data access page. The query qryNorthwindAll, shown in Figure 18-9, is such a query.

Creating a PivotTable

A PivotTable is much more flexible in displaying data than a banded data access page. Users can swap the Row and Column fields, add totals, and filter the data on the form to get just the view they want. You can use the same query that you used to create the banded data access page to create a PivotTable data access page.

To do so, follow these steps:

For more details on working with PivotTables, see Chapter 12, "Using PivotTables and PivotCharts to Analyze Data."

1. Click New in the Pages tab of the database window to open the New Data Access Page dialog box, select Design View, and click OK. A blank data access page opens.
2. Place a PivotTable control on the data access page by clicking the PivotTable tool in the Toolbox, and then clicking on the data access page to insert a PivotTable control on the grid, as shown in Figure 18-19. The PivotTable will have its drop zones, but won't have any fields selected.

   

   Figure 18-19. Insert a PivotTable control on a new data access page in Design view.

   Using Date Functions to Process Date Fields in a Query

   When creating a query for use in a PivotTable or PivotChart, you can use several date functions to separate out the data in a Date/Time field into the appropriate time periods, using meaningful words rather than numbers. For example, if you anticipate wanting to display data in months, quarters, or years, you could use the Year, Month, MonthName, and DatePart functions to produce properly formatted column headings, using the following query column expressions:

    OrderYear: Year([OrderDate]) OrderMonth: MonthName(Month([OrderDate])) OrderQuarter: "Q" & DatePart("q",[OrderDate]) & ", " & Year([OrderDate]) 

   These expressions produce the following values (for a sample row):

    Order Year: 1994 Order Month: September Order Quarter: Q3, 1994 

   These query columns can then be used to create PivotTable columns or rows, or PivotChart series and categories.

3. Alternatively, you can open the Queries folder in the field list, and then drag the qryNorthwindAll query from the field list to the data access page design grid, and then select the PivotTable selection from the Layout Wizard, as shown in Figure 18-20. If you use this method, all the query's fields are placed on the data access page as columns, and you will have to move them to other drop zones, or drag them off the PivotTable, to get the desired layout. The remaining steps assume you created the PivotTable from the Toolbox.

   

   Figure 18-20. Select the PivotTable choice when dragging a query to the data access page design grid.

   Troubleshooting - I can't see the drop zones in a newly inserted PivotTable.

   If the PivotTable's drop zones aren't visible, right-click the PivotTable control, and then choose Commands And Options from the shortcut menu to open the Commands And Options dialog box. Click the Behavior tab and select the Drop Areas check box, as shown in Figure 18-21. You can also turn on the PivotTable's title bar and toolbar on this page.

   

   Figure 18-21. Turn on a PivotTable's drop zones in the Commands And Options dialog box.

   note

   ---

   PivotTables on data access pages have the Commands And Options dialog box, while PivotTables on other database objects (such as forms) have a properties sheet where you can set options.

4. Use the sizing handles to stretch the PivotTable control to the desired size.
5. Click the Type Title Text area and type in Product Sales Totals by Salesperson for the title.
6. If necessary, open the Queries folder in the field list, and open the qryNorthwindAll query so you can see its fields.
7. Drag the ProductName field from the field list to the Row Fields drop zone of the PivotTable control, drag the Salesperson field to the Column Fields drop zone, and then drag the Price field to the Totals/Detail Fields drop zone. Figure 18-22 shows the PivotTable in Design view.

   

   Figure 18-22. The PivotTable data access page looks like this in Design view.

8. Select the Price column and click the AutoCalc button on the PivotTable toolbar (or right-click the selected column and select AutoCalc from its context menu) to add a Sum or Count summary row.
9. Save the data access page as dapProductsBySalesperson.

   Figure 18-23 shows the data access page in Page view.

   

   Figure 18-23. The new data access page looks like this in Page view.

Troubleshooting - I can't add a sum to a PivotTable or change the chart type on a PivotChart.

Although they look normal (they don't appear dimmed), the AutoSum button and other buttons on the PivotTable and PivotChart toolbars aren't available unless the PivotTable (or PivotChart) control is activated. In data access page Design view, click the control once, and then again. (Don't double-click the control. If you do, the Object Properties dialog box will open.) When you see the PivotTable (or PivotChart) surrounded by a wide, diagonally hatched border, you can use the tools on its toolbar.

You can also swap the Row and Column fields of the PivotTable and add totals. Figure 18-24 shows the same PivotTable with Salesperson as the Row field and ProductName as the column field.

Figure 18-24. The same PivotTable looks like this after you swap rows and columns and add totals.

tip

---

To turn on the PivotTable toolbar, double-click the PivotTable to open its properties sheet and set the DisplayToolbar property to True. (Alternatively, right-click the PivotTable control, and then choose Commands And Options from the shortcut menu. Click the Behavior tab and check the Toolbar checkbox.) The toolbar appears at the top of the PivotTable control; it has only a subset of the controls on the PivotTable toolbar on a form.

Creating a PivotChart

The data access page toolbox doesn't have a PivotChart control. Instead, it has an Office Chart control. In Access 2000, this control put a chart similar to a Microsoft Graph on a form or data access page. In Access 2002, this control places a Microsoft Office Web Component placeholder on a data access page, and you need to select a data source to turn it into a PivotChart. Placing a Chart control on a data access page is primarily useful for creating a PivotChart based on external data, as described in the "Creating a PivotChart Based on an External Data Source" section later in this chapter. If you want to create a PivotChart based on a local table or query, a much simpler method is available as described in the following procedure:

1. Create a new data access page by using the Design View selection in the New Data Access Page dialog box.
2. In the data access page field list, open the Tables (or Queries) folder, and drag the table or query you want to use for the PivotChart to the data access page's design grid. The Layout Wizard appears. Select the PivotChart option (as shown in Figure 18-25) and click OK to place the PivotChart on the data access page.

   

   Figure 18-25. Select the PivotChart option on the Layout Wizard when dragging a table or query to a data access page to create a PivotChart.

3. Use the sizing handles of the PivotChart control to resize it as needed.
4. Right-click the PivotChart control and select field list from its context menu to open the PivotChart's field list.
5. Drag one field from the Chart field list to the Category Fields drop zone, another to the Series Fields drop zone, and another to the Data drop zone. If you want to be able to filter the data, drag another field to the Filter Fields drop zone. Figure 18-26 shows the PivotChart with Order Year selected as the Category Field, Salesperson as the Series field, Price as the Data field, and CategoryName as the Filter field.

Figure 18-26. This PivotChart in Design view has its Series, Categories, and Filter fields filled.

Now that you have the PivotChart set up with the data you want to display on it, you can modify the chart in various ways:

tip - Turn on PivotChart Features

---

The PivotChart control has its own field list-don't confuse it with the data access page's field list. The PivotChart's field list says Chart Field List in its title bar. If you don't see it, right-click the PivotChart control and select Field List from its shortcut menu. The PivotChart's toolbar and drop zones can also be turned on or off from this shortcut menu.

• To display a legend, click the Show/Hide Legend button on the PivotChart toolbar. (By default, the PivotChart does not have a legend.)
• Select another chart type by clicking the Chart Type button on the PivotChart toolbar, and selecting a chart type, as shown in Figure 18-27.

Figure 18-27. You can choose from several chart types for the PivotChart.

Figure 18-28 shows the completed PivotChart in Page view.

Figure 18-28. The completed PivotChart looks like this in Page view.

You might want to change a few other properties in the PivotChart's properties sheet, although you should leave most of the properties at their default settings. Generally speaking, if the property is familiar from controls on Access forms, or if it starts with "Display," it is safe to change. For example, you can safely change the FontSize and FontStyle properties, but it's best to leave properties such as Behavior and ClassName alone.

Creating a PivotChart Based on an External Data Source In addition to creating a PivotChart based on a query or table in the current database, you can also create a PivotChart based on a query or table in an external data source, by setting up an OLEDB connection. To create a PivotChart on a data access page based on a query in another Access database, follow these steps:

1. Create a new data access page by using the Design View selection in the New Data Access Page dialog box.
2. Click the Office Chart tool in the Toolbox to place a Microsoft Office Web Component placeholder on the data access page.
3. Click the placeholder, then click it again, to open the Commands And Options dialog box to the Data Source tab. (Don't double-click the placeholder; doing so will open the regular properties sheet). Figure 18-29 shows the Data Source tab, with three options for specifying the PivotChart's data source.

   

   Figure 18-29. You have three choices for selecting a PivotChart's data source.

   Data Source Choices for PivotCharts on Data Access Pages

   When selecting a data source for a PivotChart control on a data access page, you have three options:

   Data Typed Into A Data Sheet This option lets you manually type the data for the PivotChart into a datasheet. When you select this option, the Data Sheetbutton is enabled. When you click this button, a little datasheet opens so you can fill it in with data.

   Data From A Database Table Or Query This option lets you select a table or query in another database as the data source. When you select this option, the Connection button appears on the form, which you can click to set up a connection to the database containing the table or query you want to use as the PivotChart's data source.

   Data From The Following Web Page Item This option lets you use the same data as a PivotTable control or Data Source control on the same data access page. When you select this option, the DataSource Details button appears on the form, which you use to select the PivotTable or DataSource control to use as the PivotChart's data source. This is handy if you want to have a PivotTable and a PivotChart displaying the same data on a data access page.

4. To use a query or table as the PivotChart's data source, select the second option on the Data Source tab. The Commands And Options dialog box now has two new tabs, Data Details and Type, and the Connection button appears on the bottom of the Data Source tab, as shown in Figure 18-30.

   

   Figure 18-30. Set a connection to a database on the Data Source tab of the Commands And Options dialog box.

5. Click the Connection button to select the database containing the query (or table) you want to use as the PivotChart's data source. For the example, we'll use the qryNorthwindAll query located in the Modified Northwind database, which was prepared in advance with appropriate fields for use in a PivotTable or PivotChart.
6. Click the Edit button on the Data Details tab to open the Select Data Source dialog box, and select the appropriate file type in the drop-down list at the bottom of the dialog box (for the example, I selected Access Databases (*.mdb; *.mde).
7. Navigate to the folder where the database you want to use is located, select the database, and click Open to set the connection.
8. The Select Table dialog box displays, where you can select the table or query from the selected database.
9. After selecting the query or table, you are back on the Data Details tab of the Commands And Options dialog box, with the connection information filled in, as shown in Figure 18-31.

   

   Figure 18-31. After selecting the database and query, the Data Details tab is filled with the necessary connection data.

10. The Microsoft Office Web Component placeholder is gone, replaced by a recognizable PivotChart control. Click the control to activate it (when activated, it has a wide cross-hatched border), and drag the desired fields from the PivotChart's field list to the PivotChart's drop zones.

From now on the PivotChart control can be modified just as for one based on a local table or query, as described earlier in this chapter.

Adding an Office Spreadsheet Control

The Office Spreadsheet control resembles a Microsoft Excel worksheet, and thus should be familiar to anyone who has worked with Excel. To create an Office Spreadsheet control based on an Access table or query, follow these steps:

1. Create a new, blank data access page using the Design View selection.
2. Drag a table or query to the data access page grid, and then select Office Spreadsheet from the Layout Wizard. The Office Spreadsheet control is preloaded with data from the selected table or query's fields.

   note

   ---

   Unlike the PivotTable and PivotChart controls, Office Spreadsheet controls lack drop zones for drag-and-drop field placement. Instead, they display all fields as data columns.

3. Adjust the size of the Spreadsheet by using its sizing handles. Adjust the column widths by using the Excel double-headed sizing arrows, as shown in Figure 18-32.

   

   Figure 18-32. You can adjust the column widths of a Spreadsheet control.

tip

---

To get more help on using the Office Spreadsheet control, click the Help button in the spreadsheet toolbar to open the Microsoft Office Spreadsheet Component Help book.

To add a sum to an Office Spreadsheet column, follow these steps:

1. Click a cell in the worksheet, and press Ctrl+End to go to the last cell of the worksheet.
2. Right-click the last row of the worksheet, and select Insert, Rows to insert a row above the last row (no option is available for you to insert a blank row below a selected row).
3. Move down a row and you will see the last row of the worksheet, under the blank row. Right-click the last row and select Cut; then highlight the blank row, right-click it and select Paste.
4. Select the blank cell under the column to be summed, and click the AutoSum button on the Office Spreadsheet control toolbar; a formula appears in the cell, and when you tab away from it, you can see the total amount, as shown in Figure 18-33.

   

   Figure 18-33. You can create a total for a column in an Office Spreadsheet control by clicking the AutoSum button with a blank cell selected.

InsideOut

Because it is more difficult to add a sum to an Office Spreadsheet control than to a PivotTable, and data in an Office Spreadsheet control can't be rearranged dynamically, you will have more flexibility if you use a PivotTable control rather than an Office Spreadsheet control.

Creating an Office Spreadsheet Control Bound to an Excel Worksheet If you need to display data from an Excel worksheet on a data access page, you can bind the Office Spreadsheet control to data in a worksheet by using the Data Source tab of the Commands And Options dialog box.

To create an Office Spreadsheet control bound to data in an Excel worksheet, follow these steps:

1. Create a new data access page by using the Design View selection in the New Data Access Page dialog box.
2. Click the Office Spreadsheet tool in the Toolbox to place an Office Spreadsheet control on the data access page.
3. Right-click the control to open the Commands And Options dialog box. Click the Data Source tab, where you can set up a connection to a data source (see Figure 18-34).

   

   Figure 18-34. The Data Source tab of the Commands And Options dialog box lets you select a worksheet as the data source of an Office Spreadsheet control.

4. Click the Edit button to open the Select Data Source dialog box, and select the Excel Files (*.xls) selection.
5. Navigate to the folder that contains the workbook you want to use, select the workbook, and click Open to bind the control to that workbook.
6. Select the worksheet to use in the Select Table dialog box, and click OK. The connection string appears in the Connection box. If necessary, type the command text or SQL string you want to use to retrieve data from the data source in the CommandText Or SQL box.

   The data from the selected worksheet now appears in the control.

Working with Miscellaneous Data Access Pages

In addition to the data access page types discussed in earlier sections, which are created by selections in the New Data Access Page dialog box or the Layout Wizard that appears when you drag a table or query to the data access page design grid, several other ways are available for you to create data access pages.

Access 2002 allows you to create a data access page by saving a form, a report, a query, or a table in an Access database as a data access page. You can also save tables, views, stored procedures, functions, and forms in an Access project as data access pages. To save an Access form as a data access page, open the form in Form or Design view, choose File, Save As, and then select Data Access Page in the As box.

This new feature works well for simple forms. Figure 18-35 shows a form based on the Northwind Order Details table and the data access page created from it. (Refer to Chapter 5, "Creating Forms for Entering, Editing and Viewing Data," for a detailed discussion of creating a form based on a table.)

Figure 18-35. The data access page on the bottom was created from the simple form on top.

However, if you try to save a more complex form, such as one with an embedded subform, or a record selector combo box, you may get an error message "Microsoft Access is unable to create the data access page," because Access is unable to save the form as a data access page. You can have the equivalent of a record selector combo box or a subform on a data access page, but you must create these features by using dataaccess page tools. For example, a data access page with a PivotTable can use the Filter field to select records for a single employee, and a banded report can display linked records, much like a subform.

tip

---

Because the design tools in data access pages are less refined than those used with Access forms, it is generally easier to design a form and then save it as a data access page, rather than building a data access page from scratch, at least for a form-type data access page.

Troubleshooting - I get the message "The HTML file associated with this link has been moved, renamed or deleted" when trying to open a data access page

If the saved HTM file for the data access page has been moved or renamed, you can click the Update Link button to search for the current location (or name) of the file; if it has been deleted, though, you will have to re-create it.

And finally, you can open a saved HTM file (for example, a file created in Internet Explorer or FrontPage) as an Access data access page by selecting Existing Web Page from the New Data Access Page dialog box, and then selecting the HTM file for the data access page you want to open from the Locate Web Page dialog box, as shown in Figure 18-36.

Figure 18-36. You can open a data access page from its HTM file.