Scripting the Data Source Control in Data Access Pages

Data access pages are designed to reproduce many of the features of Microsoft Access forms and reports in Web pages running in Microsoft Internet Explorer 5.0 or later. Beyond simply reproducing forms and reports, data access pages also provide additional features and benefits that are tailored to performing data browsing, data entry, reporting, and analysis from a Web browser. While Access 2000 or later is required to create data access pages, users of data access pages require only Internet Explorer 5.0 or later, an installation of the Microsoft Office Web Components, and sufficient security permissions to work with the data sources the page is connected to (if working with live data).

Data access pages are built upon several technologies and components, the most important of which is the Microsoft Office Data Source control (MSODSC), part of the suite of components for displaying, editing, and analyzing data called the Microsoft Office Web Components. The Data Source control integrates functionality from the Dynamic HTML (DHTML) Object Model, which is implemented by Microsoft's HTML parsing and rendering engine (MSHTML), and from ActiveX Data Objects (ADO), the programmatic interface to the Microsoft Data Access Components (MDAC).

This article focuses on how the core features of the Data Source control's object model are related to the DHTML and ADO object models, and how to use that knowledge to extend and customize data access pages by writing script. Most of the information presented in this article can be applied to data access pages created in both Access 2000 and Access 2002. Some of the new features provided in Access 2002 data access pages are also described.

Data access page Design View in Access 2002 incorporates many improvements, such as multiple levels of undo, multiselect of page elements, improved property browsing, drop areas for grouping, and the Layout Wizard (which is displayed when you drag multiple fields or an entire table onto a page from the Field List) to make laying out multiple fields easier. For the best design experience, you should create data access pages in Access 2002 with Internet Explorer 5.5 or later installed (some of the improvements in Design View depend on components installed by Internet Explorer 5.5 or later).

Note Data access pages created in Access 2002 can't be opened in Design View in Access 2000. Similarly, once you open and save changes to a data access page created in Access 2000 by opening it in Access 2002 Design View, you can't re-open it in Access 2000. However, pages created in Access 2002 can be opened in Page View in Access 2000, if the user also has the Microsoft Office XP Web Components installed.

This article does not cover working with the Data Source control for the Microsoft Office Chart, Spreadsheet, and PivotTable components. For more information on these components, see Working with the Office Web Components in the Microsoft Office 2000/Visual Basic Programmer's Guide and Programming Microsoft Office 2000 Web Components books by Microsoft Press.

What is the Data Source Control?
The primary functions of the Data Source control are to connect to data sources, to build and execute commands against those data sources, and to retrieve and bind the results of those commands to elements on the page. Additionally, the Data Source control keeps track of the record the user is currently working with. For more complex "banded" pages, the Data Source creates hierarchical groupings. For instance, you can display an order form with a subform that displays the order details from a related table, or a sales report that groups data by multiple levels, such as nested groupings by month, region, and sales representative. (For more details on banded pages, see The Layout of a Data Access Page later in this article).

Unlike the other Office Web Components controls, the Data Source control is an ActiveX control that has no visible interface at run time. When you create a new data access page in Access, it inserts an OBJECT tag that defines the Data Source control in the HEAD element of the data access page. The ID attribute of the OBJECT tag created for a data access page created in Access is always set to MSODSC.

If you open a data access page in the Microsoft Script Editor from Access 2002 (by opening the data access page in Design View, and then clicking Microsoft Script Editor on the toolbar), you should see the OBJECT tag for the Data Source control near the top of the page. Following the CLASSID attribute for the control is an extensive XML definition of properties of the control, such as the ConnectionString property and the ElementExtensions collection. The ElementExtensions collection is used to add custom properties to the data-bound HTML elements in a data access page, and only exists after fields are bound to a page. The ElementExtensions collection is described in greater detail later in this article.

Important Do not modify the definition of the OBJECT tag for the Data Source control directly in the HTML of a data access page. You should only modify these settings from the page's Design View in Access, or through the methods and properties of the Data Source control object model.

Making Sense of the Object Models Behind Data Access Pages
Unlike the Office Web Components Chart, Spreadsheet, and PivotTable controls, the functionality of a data access page is not contained within a single ActiveX control (although a data access page, like any Web page, can incorporate Chart, Spreadsheet, and PivotTable controls).

At its most basic level, a data access page consists of the interaction of the Data Source control with a set of DIV elements that act as containers for the labels, controls and data-bound elements that make up the page. Extending the functionality of a data access page can require you to work with the DHTML Document Object Model, as well as the Data Source control object model. There are two primary reasons why:

The primary elements that make up a typical data access page are intrinsic HTML controls and data-bound SPAN elements.

User interactions with the page are often events that occur within the context of an HTML document. Because the Data Source control is designed to integrate with HTML elements, its object model contains members such as the HTMLContainer property of the Section object that allow your script to access the collections, properties, and methods of the DHTML Document Object Model. This, in turn, enables you to work with the HTML elements that make up a data access page.

In addition to interacting with and integrating portions of the DHTML Document Object Model, the Data Source control encapsulates ADO functionality for working with the recordsets exposed through the page. Because these recordsets can be hierarchical and may contain calculated columns that are generated at run time (also known as shaped recordsets), the encapsulated ADO functionality operates in conjunction with the OLE DB MSDataShape Provider and the OLE DB Cursor Engine, in conjunction with the actual data provider for the data source itself.

While the Data Source control is designed to manage most of the details of working with these data access components for you, the Data Source control object model also includes members that allow you to access ADO objects; for example, the Connection property of the DataSourceControl object lets you access the ADO Connection object for the page's data source, and the Recordset property of the DataPage object lets you access the ADO Recordset object for a particular set of records displayed in a page.

Data Source Control Objects
While the Data Source control exposes a variety of collections and objects, the primary set of objects you need to understand and work with when customizing a data access page are:

The Section object

The GroupLevel object

The DataPage object

The ElementExtension object
While the GroupLevel, DataPage, and ElementExtension objects are accessible from the corresponding collections of the DataSourceControl object, there is no corresponding collection for Section objects. That's because Internet Explorer renders the actual section at run time. Instead of a corresponding collection for Section objects, the Data Source control object model provides a set of properties and methods for accessing a particular section on the page as described later in this article.

The following sections describe how these objects map to the components of a data access page, and how they relate to typical data access page customization tasks.

The Layout of a Data Access Page
The names of the basic parts of a data access page in Design View can be somewhat confusing, so it's important to understand what they refer to and how these parts map to the relevant collections and objects of the Data Source control object model.

The underlying architecture of a data access page varies depending on whether a page utilizes hierarchical grouping. There are two basic kinds of data access pages:

Simple pages - These pages display fields from a single record at a time with no repeated records or hierarchical grouping. While at run time a simple page has no grouping, in the terms of the underlying data model, it does have a single group level (GroupLevels.Count = 1) and a set of properties associated with that group level. The Employees page in the Northwind Traders sample database is an example of a simple page.

Banded pages - These pages display multiple records at a time in bands that are repeated down the page. A banded page can either have no hierarchical grouping (essentially a simple page with the DataPageSize property of its single group level set greater than 1), or it can display a hierarchy of related or grouped records. By default, banded pages with grouping display only the first level of hierarchy with lower levels of grouping collapsed beneath expand controls (+ icons). The Review Orders page in the Northwind Traders sample database is an example of a banded page with hierarchical grouping.

For additional information on distinguishing between simple and banded pages programmatically, see The GroupLevel Object later in this article.

The design surface of a data access page can consist of up to four kinds of sections:

Caption - The first section of a simple page, or the first section of a group level in a banded page, the caption section is most typically used to display column headings for fields displayed in the following header section. However, the caption section can contain any type of control except data-bound controls. When you create a data access page in Design View, the caption section isn't added by default. To add a caption section, right-click a header section, and then click Caption.

Header - While this section is called the header, it is functionally similar to the detail section of a form or report. The header section is primarily used to display data in data-bound controls and calculated values. A simple page has a single header section that shows one record at a time, whereas a banded page shows multiple records at a time repeating the header section for each record in a grouping level. Banded pages with multiple grouping levels contain nested header sections for each grouping level.

Footer - The footer section is associated with a header section at the same grouping level. The footer section is typically used to display totals or subtotals for data displayed in the associated header section, although you can also add controls bound to fields and other controls. When you create a data access page in Design View, the footer section isn't added by default. To add a footer section, right-click a header section, and then click Footer. You can't add a footer section to a simple page, or to a banded page that has a single header section. On a page with grouping (pages with a hierarchy of two or more header sections), you can't add a footer section to the lowest (innermost) level in the grouping hierarchy.

Record Navigation - The record navigation section is the last section of a simple page or a group level in a banded page, and is associated with a header section at the same grouping level. It contains a record navigation control, which is used to move between records, or to add, delete, save, undo changes to, sort, or filter records in the associated header section. The record navigation section can't contain data-bound controls.

The Section Object At design time, a section can be thought of as a two-dimensional container for laying out the controls and elements of your data access page. When you start creating a data access page in Design View, Access adds a single unbound section that has no particular type. A section starts out as unbound, because at the start of the design process the Data Source control for the page doesn't have any Recordset objects defined in its Recordsets collection to bind to. There are two ways to bind data to a section:

Dragging fields or tables from the Field List into the section

Setting the RecordSource property in the section's property sheet Binding the unbound section by either method makes the section a header section and automatically adds a navigation section associated with the new header section. Both binding mechanisms automatically add Recordset objects to the Recordsets collection of the Data Source control associated with the page. The fields defined by the generated Recordset objects become available for binding controls within the section by setting their ControlSource property to the desired field's name.

If you drag fields or tables from the Field List, Access sets the ID attribute for the control to the same name as the bound field (or generates a unique name if there are duplicate names) and sets the ControlSource property for the control to bind it.

If you bind a section by setting the RecordSource property, and then add controls to the section from the Toolbox, you must specify the ControlSource property for the control manually.

Tip You can see the organization of the data bound to a page (which is called the data model) by displaying the Data Outline. To display the Data Outline, open a data access page in Design View; point to Toolbars on the View menu, and then click Data Outline. You can view and set the properties of the items that make up the data model, such as GroupingDef, PageField, PageRowSource, and Recordset objects, by right-clicking an item in the outline, and then clicking Properties.

Important When Access binds a section to data, it sets the ID attribute of the DIV tag for the section to reflect the tables that the section is bound to. Don't change the ID attribute for a section in the Microsoft Script Editor, because it can interfere with the data binding for a page. Also, if you add other fields to a section in Design View, Access may change the ID attribute of a section. For this reason, you should complete the basic design for your data access page before adding scripts to the page. Otherwise, you'll need to update any SCRIPT tags and code that refers to old ID attributes for the section or the controls in that section.

At run time, the Section object functions as an abstraction for the HTML DIV element and the HTML controls and elements that make up a particular section. The Section object provides methods and properties that allow you to work with a section at runtime.

Working with Data-Bound Controls in a Section
When you manipulate the value of a data-bound control in a data access page, you're modifying the underlying database field. Changes that you make take effect immediately in the data access page, and, just like when the user makes a change, the value is saved back to the database when the record is committed (by clicking the Save button on the record navigation control, by navigating off of the record, or by using the Save method of the DataPage object).

Accessing data-bound controls on a simple page
When you create data-bound controls in Design View by dragging a field or table from the Field List onto a data access page, Access sets the ID attribute of the controls to the same names as the bound fields. If you have a simple page, you can use a control's ID attribute to return or set the DHTML value property of the control. For example, in a simple (non-banded) page that contains a control bound to the CategoryName field, the following code will display the control's contents: Complete Article

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.