Moving to a New Access Form Model

Data access pages (DAP) are a radical departure from traditional Access database applications. Unlike other Access objects stored in .mdb or .adp files, Access stores DAP in Dynamic HTML (DHTML) files with a standard .htm extension. DAP that appear when you click the Database window's Pages shortcut such as the sample pages installed by Office 2003's setup program represent links to the corresponding .htm files. The Review Products and Review Orders page items, for example, are links to the Review Products.htm and Review Orders.htm files in your ...\Office11\Samples folder.

Users view DAP in Internet Explorer (IE) 5+, so they don't need a copy of Access 2003, nor do you need to distribute a runtime version of your DAP. Users can run DAP from a local copy of the required files, which you can send as an email attachment, or from a file or Web server. However, Access 2003 DAP rely on members of the Office Web Components (OWC) 11 for data connection, navigation, and presentation. Users without an Office 2003 license can download the runtime version of OWC 11 from the Microsoft Office Update Web site. The OWC 11 runtime version lets users display data in PivotTable, PivotChart, and Spreadsheet controls, but it freezes their presentation, except PivotTable filters and expand/collapse operations. Clients must have an Office 2003 license to manipulate these three controls; users can't pivot a PivotTable or change the chart type of a PivotChart without an Office 2003 license.

Note

DAP include a script to detect the Web browser in use on the client PC opening the page. If the client doesn't have IE 5+ installed, the page displays a "Click here to install the latest version of Internet Explorer" message and a link to Microsoft's Internet Explorer site.

If the client PC running Windows 2000 SP3 or Windows XP doesn't have OWC 11 installed, a message opens with a link to download the Web components from the Microsoft Office Update site. (OWC11 won't install under Windows Me, 98, 98SE, or 95.) OWC 11 installs side-by-side with Office 2000's OWC 9 and Office XP's OWC 10, so Office 2000 and XP applications aren't affected by adding OWC 11.

Access 2002 delivered an upgraded page designer for DAP. Like the project designer for SQL Server objects, the page designer is a design-mode tool that automatically generates code. The project designer generates T-SQL statements to create or modify SQL Server objects. The page designer generates DHTML code when you add or modify control objects on the page. Access 2003's page designer differs greatly from Access's Form Design view. The page designer has its own page toolbar and a Toolbox of DHTML-compliant controls, some of which emulate native Access controls for forms and reports. The most notable omissions in the page Toolbox are the Subform/Subreport and Tab Control tools. DAP don't support subforms, subreports, or tab controls because HTML has no direct counterpart of these components.

For a list of new and improved DAP features introduced by Access 2002, see "Data Access Pages Revisited," p. 40.

Combining Report and Form Features in DAP

Many new Access users and even some seasoned Access experts have a difficult time getting a grip on the architecture and design methodology of DAP. DAP combine some of the features of forms and reports; for example, DAP make use of report-like grouping methods (called banding) to replace subforms. Grouping lets you easily add subtotals and grand totals to a hierarchy of nested sections that contain the bands. Like conventional data-bound Access forms and subforms, each hierarchy level has a default record navigation control. You expand and contract the levels' sections by clicking the boxed plus or minus signs at the left of the page. Figure 24.1 shows a modified version of the sample Review Orders.htm page in IE 6.0.

Figure 24.1. This slightly modified version of the Review Orders.htm page has a three-level section grouping hierarchy. The Order Details level shows all line items for the selected order and emulates a datasheet with record selector buttons. Each level has an HTML version of the default record navigation buttons of a form, as well as Save, Undo, Sort Ascending, Sort Descending, Filter by Selection, and Apply Filter buttons.

Note

Generating grand totals for each year's orders and calculating subtotals for each order delay opening Northwind.mdb's Review Orders.htm page. The page must download Recordsets for the entire Orders and Order Details tables before displaying the outer section.

The Review and Edit Orders page has a live connection to Northwind.mdb, so you can edit all but two field values shown in the text boxes of Figure 24.1. The OrderID field is read-only because it has the AutoNumber Jet field type, and ProductName is a read-only lookup field from the Products table. If you change a ProductID value and click the Save button of the Order Details Extended navigation control, the Product Name value changes accordingly. Other values, such as UnitPrice and ExtendedPrice, don't change, so editing real order data in this page would be dangerous.

Tip

To open the sample Review Order page in IE 5+, launch Explorer, navigate to your ...\Office11\Samples folder, and double-click the Review Orders.htm file. The Review and Edit Orders page shown in Figure 24.1 has only minor layout modifications to Northwind.mdb's sample page.

Substituting Multiple Pages for MDI Forms

Access forms are multiple-document interface (MDI) child windows of the main Access parent window. You use command buttons such as those of the Main Switchboard sample form to execute DoCmd.OpenForm and DoCmd.Close methods in VBA ButtonName_Click event-handling code. Browsers use the single-document interface (SDI) model. HTML frames emulate some MDI features, but the page designer doesn't support frame set editing. You can't open a page with a frame set in Page Design view, but you can use FrontPage 2003 to design the frame set to display the pages. Access 2003 supports embedded frames (<IFRAME> elements) in pages.

DAP use hyperlinks or command buttons to navigate within applications that require multiple pages. The Employee: label of the Review Orders.htm page is formatted as a hyperlink when you open the page in IE 5+, but it doesn't have a <A href="http://www.wherever.com"></A> HTML anchor tag pair. Instead, a VBScript event handler for the onclick event of the EmployeeID1 label applies the window.showModalDialog method to open the Employees.htm page, as illustrated by Figure 24.2. The main page creates a cookie to pass the EmployeeID filter value (2) from the lookup list to the Employees.htm page, which displays the Employees record for Nancy Davolio. Opening the Employees.htm file with the VBScript event handler instead of a URL eliminates the need to deploy the pages to your Web server to enable navigation by hyperlinks.

Figure 24.2. Clicking the hyperlink-formatted Employee: label opens the Employees.htm page as a modal Web Page Dialog, which emulates an Access pop-up form. Code in the main page creates a cookie to pass the EmployeeID value as a filter on the dialog's Employees Recordset.

The navigation controls of the Employees Web Page Dialog illustrate substitution of labels for the page's default record navigation buttons. You also can substitute buttons or custom graphic images.

Note

Clicking the Employee label opens the Employees page in either Access Page view or IE. VBScript code in the Review Orders page detects whether the page is open in Page view or in IE 5+. The code applies the blue color and underline attributes to the label text only if you open the page in IE 5+.

Connecting to the Back-End Database

By default, DAP use DHTML Data Binding to ActiveX Data Object (ADO) Recordsets, which the page automatically generates from server-based Jet or SQL Server tables or queries. The page designer creates a connection string based on current Jet database for an .mdb file or the SQL Server database for the project's .adp file. Alternatively, you can establish the database connection with an Office Data Connector (.odc) file. The page either links to or embeds the contents of the .odc file in an <OBJECT>.../<OBJECT> declaration within the <HEAD> element to serve as the connection string for the Microsoft Office Data Source Control (MSODSC), a member of OWC 11. MSODSC makes the connection to the database that you specify in the .odc file when you open the page in IE 5+ or Access Page view. DAP also support Office 2000 Uniform Data Locator (.udl) files for backward compatibility.

To review how to create an .odc file, see "Linking a Jet 4.0 Database," p. 842.

Caution

Use SQL Server databases with integrated Windows security as the data source for production DAP on your intranet. Users of your pages can display the source code of your pages by choosing View, Source in IE. The default connection string for Jet database files and if you use SQL Server security SQL Server databases includes the login ID and password in plain text. You can prevent the password from appearing if you clear the Allow Saving Password check box in the Data Link Properties' Connection page. In this case, viewing the page requires entering the password for the login ID that you specify.

Linking an .odc file to provide the data source lets you specify a common connection for all pages that use the data .mdb file or SQL Server database. The location and name of the file, however, are visible in the page's source code. If users can gain access to the .odc file that contains a password, database security is compromised.

Binding to server-side data sources requires an active database connection to each client. DAP are stateful unless you write script to use a disconnected Recordset. The general rule is to avoid stateful intranet and Internet applications, which consume Web and database server resources. Minimizing the number of concurrent active connections is especially important with MSDE, which starts closing the throttle when more than five batch operations occur simultaneously.

For additional background on the statefulness issue, see "Making the Transition to Stateless Front-Ends," p. 949.

To learn more about disconnected Recordsets, see "Taking Advantage of Disconnected Recordsets," p. 1312.

To avoid persistent database connections, Access 2003 DAP support XML data documents as a read-only data source. Using an XML file as the data source eliminates issues with maintaining database security for Internet applications. To create and update the XML data, you make an administrative copy of the page and add a button to export an XML data document in the special DscPersistence format required by DAP. Changing two properties of the user page substitutes the XML data file for the database connection.