Share via

Working with Data Access Pages

  • Article

 

Paul Cornell
Microsoft Corporation

July 5, 2001

Introduced with Microsoft® Access 2000, data access pages allow you to create data-bound HTML pages, within the Access user interface, that can be viewed in Microsoft Internet Explorer 5 or later. You can also distribute data access pages containing static or live data by e-mail. Data access pages are typically intended for intranet use; however, with special considerations, they can be deployed over the Internet as well. Also, as with Access forms, data access pages can be used within the Access application.

Data access pages can bind to data from Access databases or Microsoft SQL Server 6.5 or later databases. These pages can also contain instances of the Microsoft Office XP Web Components, which in turn can connect to other data sources such as Microsoft Excel.

You can start experimenting with data access pages the moment you install Microsoft Access 2002. To do so:

  1. From the Help menu, point to Sample Databases and click Northwind Sample Database.
  2. On the Main Switchboard form, click Display Database Window.
  3. On the Objects tab, click Pages.
  4. Double-click any of the five available data access pages to open one of them in Page view. Click Design View from the View menu to change the page's design; then on the Tools menu, point to Macro, and click Microsoft Script Editor to manipulate the script code behind the page (by clicking Show Script Only on the View menu, you can filter the page's code to view only the script code).

In this column, I'll describe what's new and improved for data access pages in Access 2002, deployment and licensing considerations, and development approaches, including how to manipulate data access pages by using Visual Basic® for Applications code and Visual Basic Scripting Edition (VBScript). I also briefly mention the Microsoft Office Data Source control (MSODSC) and how you can leverage this feature in your data access page solutions.

There is so much to cover with data access pages that I will only be able to skim the high-level details in this column. And, for space considerations, I will be discussing data access pages from an Access 2002 perspective only. However, there are plenty of additional references for you to explore as you have time, which I've include for you later in this column.

If you've never worked with data access pages before, I recommend the following five topics in Access 2002 Help to get you up to speed quickly:

  • About data access pages
  • About sending a data access page in e-mail
  • Strategies for creating data access pages
  • Create a data access page
  • Publish Web pages

Some additional Access 2002 Help topics that introduce you to using the Office XP Web Components in data access pages include:

  • About Microsoft Office Web Components
  • Create a PivotTable list on a data access page
  • Add a spreadsheet to a data access page

To find these topics in Access 2002 Help, click the Data on the Web book on the Contents tab, and then click the Data Access Pages book.

What's New and Improved for Data Access Pages in Access 2002

Access 2002 has added several new features to data access pages, including:

  • The AutoSum button to calculate aggregates.
  • The ability to convert tables, queries, views, stored procedures, forms, and reports to data access pages. For an introduction to this feature, see the Access 2002 Help topic "About saving an object as a data access page". To find this topic in Access 2002 Help, click the Data on the Web book on the Contents tab, and then click the Data Access Pages book.
  • The ability to specify a connection file to use with the current page. You can also point multiple data access pages to a single data connection file, which eliminates the need to change the connection information for each page one-by-one. For an introduction to these features, see the following three Access 2002 Help topics: About using connection files, Create a connection file, and Set or change the connection information of a data access page. To find these topics in Access 2002 Help, click the Data on the Web book on the Contents tab, and then click the Data Access Pages book.
  • The ability to undo up to 20 of your most recent actions (although the undo list is cleared when you switch to Page view from the page's Design view). You can't undo actions on pages that are read-only.
  • The ability to select multiple controls on the page's design surface.
  • The ability to customize the navigation toolbar, such as showing or hiding navigation buttons, changing the pictures that are displayed on navigation buttons, and making other types of controls behave like navigation buttons.
  • With Microsoft Internet Explorer 5.5 and later, you can preview your data access pages as they would appear when printed. Additionally, ActiveX® controls now print properly. And finally, you can use print templates to add custom page headers and footers, print in multiple columns, and print 2-up and 4-up pages.

Connection, Deployment, and Licensing Considerations

Connecting to a Data Source

To change the data connection information of multiple data access pages in Access 2000, you must open each file individually and change the page connection. Access 2002 enables you to point multiple pages to a single connection file, which eliminates the need to change the connection information for each page one-by-one. There are two types of connection files: Office Database Connection (.odc) files (which contain connection information, keywords, and descriptions for data connections), and Microsoft Data Link (.udl) files (which is a standard file format created by Microsoft for specifying connection strings). The main properties used to manage data connection information are:

  • The ConnectionFile property (available through the Data tab on the data access page's Properties window, and as a property of the Microsoft Office Data Source control (MSODSC) in the Microsoft Script Editor (MSE)) accepts a String value that specifies the .odc or .udl file that is used to connect the page to a data source.
  • The UseDefaultConnectionFile property (available through the Use Default Connection File check box on the Pages tab Options dialog box, Tools menu) determines whether or not you will use an .odc or .udl file as a default connection file.
  • The DefaultConnectionFile property (available through the Default Connection File box on the Pages tab Options dialog box, Tools menu) is the path and file name of the .odc or .udl file that you will use as a default connection file.

Embedding or Linking to XML Data

From a data access page, embedding in, or linking to, Extensible Markup Language (XML) data is a great candidate for sharing data between disparate computer systems and among business partners.

The following properties for embedding XML data on a data access page are available on the Data tab on the page's Properties window, and as properties of the MSODSC in the MSE:

  • The UseXMLData property returns or sets a Boolean value that specifies whether the page binds to XML data (True to bind to XML data).
  • The XMLDataTarget property returns or sets a String value that represents the location of the XML data to load or save.
  • The XMLLocation property returns or sets a DscXMLLocationEnum constant that specifies whether the XML data is to be loaded from or saved to a standalone XML data file (dscXMLDataFile) or an XML data island (dscXMLEmbedded) embedded inside of the current page. The dscXMLDataFile option enhances performance by reading the XML directly into memory, while the dscXMLEmbedded option forces the HTML parser to parse the XML and then read the XML into memory.

For more information, see Including XML in Data Access Pages in Access 2002.

Hosting Data Access Pages on Your Intranet or on the Internet

Although you use Access to design data access pages, you save these pages to locations that exist separately from the Access database files or projects, either on a file share (for an intranet solution) or on a Web server (for either an intranet or an Internet solution). The icons for the pages in the Database Window are just links to the page (if you right-click a page icon in the Database Window and then click Properties, you will see in the Path box where the page is stored).

To allow end-users to open a data access page:

  1. Provide your end-users with the Universal Naming Convention (UNC) path (for example, \\MyServer7\Pages\MyPage.htm) or Uniform Resource Locator (URL) (for example, http:// http://example.microsoft.com/Data/Pages/Page05.htm) to the data access page.
  2. End-users can then open the data access page in Internet Explorer 5 or later by entering the UNC path or URL in the Address list. End-users can add the UNC path or URL to their Internet Explorer Favorites list for future reference.
  3. In Access, end-users can add a link to the data access page by clicking Page from the Insert menu, selecting Existing Web Page and then clicking OK, entering the UNC path or URL in the File name list, and clicking Open. The page will open automatically. End-users will see a new page icon in their Pages tab (in the Database Window) that links to the page.

For more information, see the shorter Knowledge Base article How to Deploy Data Access Pages over the Internet or the longer article Deploying Data Access Pages on the Internet or Your Intranet.

Licensing and Data Access Pages

Users don't need Access licenses to view data access pages, but users do need the Office XP Web Components support files. Data access pages displayed in Internet Explorer 5 or later that are just using the MSODSC to connect to data sources always have full functionality, regardless of whether or not users have valid Office XP licenses.

However, to interact with any Office XP Web Components (such as a spreadsheet, a PivotTable® list, or a chart) on a page, users must have the Office XP Web Components support files installed on their computers, as well as valid Office XP licenses. The Office XP Web Components are included with Office XP and the standalone version of Microsoft FrontPage® 2002. If you don't have Office XP installed on your computer, the page will allow you to download the Office XP Web Components support files (click here to go to the download). However, if you have the Office XP Web Components support files but you do not have Office XP or FrontPage 2002, you can only view the Office XP Web Components on the page; you cannot interact with these components or use their design capabilities.

For more information about data access page licensing and functionality considerations, see the section Details on licensing and functionality in the Upgrading to Access 2002 topic in the Microsoft Office XP Resource Kit.

Data Access Page Development Approaches

There are basically two data access page development approaches. One approach is to use Visual Basic for Applications (VBA) code outside of the data access page design environment to manipulate pages. The other approach is to use scripting languages such as Microsoft Visual Basic Scripting Edition (VBScript) or Microsoft Jscript® in the data access page design environment to control the page's behavior at run time. The VBA approach is somewhat limited, and is generally only used when you need to programmatically manipulate the properties of a set of data access pages in the development or debugging phase of a project. In most cases, you'll use the scripting approach. Because data access pages are HTML-based, you can embed script code inside of the HTML to provide run-time functionality for end-users. I will show you how to leverage these two approaches in the following two sections.

Working with Data Access Pages by Using VBA

The Microsoft Access 10.0 Object Library enables you to make changes to several data access pages at once, rather than wasting time opening each page individually and making the same change to each one.

You can programmatically access all of the pages in the open Access database or project file by using the AllDataAccessPages collection of the CurrentProject object. The following example lists the name of each page in the open Access database or project.

Public Sub AllPagesInCurrentProject()

    Dim intDAPCounter As Integer
    Dim strDAPList As String
    
    ' Get the names of all of the pages in the current Access database.
    ' It does not matter if the pages are open or not.
    With CurrentProject.AllDataAccessPages
        For intDAPCounter = 0 To .Count - 1
            strDAPList = strDAPList & .Item(intDAPCounter).Name & vbCrLf
        Next intDAPCounter
    End With
    
    MsgBox Prompt:="The names of all of the pages in the " & _
        "current project are:" & vbCrLf & strDAPList
    
End Sub

You can also programmatically access all of the open pages in a particular instance of Access by using the DataAccessPages collection of the Application object. The DataAccessPages collection contains one DataAccessPage object for each open page. The following example lists the name of each open page in the current instance of Access.

Public Sub AllOpenPagesInApplication()

    Dim intDAPCounter As Integer
    Dim strDAPList As String
  
    ' Get the name of each open page in this instance of Access.
    ' Each page must be currently open to count.
    With DataAccessPages
        For intDAPCounter = 0 To .Count - 1
            strDAPList = strDAPList & .Item(intDAPCounter).Name & vbCrLf
        Next intDAPCounter
    End With
    
    MsgBox Prompt:="The names of all of the open pages in this " & _
        "instance of Access are:" & vbCrLf & strDAPList

End Sub

Once you have programmatic access to a page, you can use the methods and properties of the particular DataAccessPage object to manipulate the page.

One of the more frequently used members of the DataAccessPage object is the Document property, which returns a Dynamic HTML (DHTML) document object representing the code behind the page. Help for the DHTML document object can be found in the DHTML, HTML, and CSS Reference (htmlref.chm), which is included when you install Office XP.

As an example of how to use the Document property, the following code returns the title of the first open page:

Public Sub DisplayPageTitle()

    Const FIRST_OPEN_PAGE As Integer = 0    
    Const NO_PAGES_OPEN As Integer = 2019
    
    On Error GoTo DisplayPageTitle_Err
    
    ' Display the title of the first open page in this instance of Access.
    MsgBox Prompt:="The title for the first open page is: " & _
        DataAccessPages.Item(FIRST_OPEN_PAGE).Document.Title
        
DisplayPageTitle_End:
    Exit Sub
    
DisplayPageTitle_Err:
    Select Case Err.Number
        Case NO_PAGES_OPEN
            MsgBox "You must have at least one data access page open first."
        Case Else    
    End Select
    Resume DisplayPageTitle_End
    
End Sub

Another frequently used member of the DataAccessPage object is the MSODSC property, which returns an Office XP Web Components DataSourceControl object, representing an instance of the MSODSC that binds the page to the source data. Help for the DataSourceControl object can be found in the Microsoft Office Web Components Visual Basic Reference (owcvba10.chm), which is included when you install Office XP.

As an example of how to use the MSODSC property, the following code exports the first open page's associated data to a separate file as XML:

Public Sub ExportXMLFromFirstOpenPage()

    ' You must first set a reference to the Microsoft Office XP
    ' Web Components Library (OWC10.DLL).
    
    Const FIRST_OPEN_PAGE As Integer = 0
    Const XML_TARGET_FILE As String = "C:\DataAccessPages0.xml"
    Const NO_PAGES_OPEN As Integer = 2019
    
    On Error GoTo ExportXMLFromFirstOpenPage_Err
    
    ' Export the first open pages's associated data to a separate file as XML.
    With DataAccessPages.Item(FIRST_OPEN_PAGE).MSODSC
        .XMLLocation = dscXMLDataFile
        .XMLDataTarget = XML_TARGET_FILE
        .ExportXML
    End With
        
ExportXMLFromFirstOpenPage_End:
    Exit Sub
    
ExportXMLFromFirstOpenPage_Err:
    Select Case Err.Number
        Case NO_PAGES_OPEN
            MsgBox "You must have at least one data access page open first."
        Case Else
    End Select
    Resume ExportXMLFromFirstOpenPage_End
    
End Sub

If you would like more examples, there are some great Knowledge Base articles on working with data access pages by using VBA:

Working with Data Access Pages by Using Script

Let's now turn our attention to programmatically controlling the run-time experiences of data access pages for your end-users. To do so, you will want to write script that is attached to the page. The script is not visible to your end-users through the page's user interface, but it provides functionality that the page's elements can leverage in response to the end-user or other run-time behaviors. This script can be written in VBScript or JScript.

Exploring VBScript and JScript

VBScript is designed to be a small and lightweight interpreted language. It is also designed to be safe, so it does not include direct access to the underlying operating system. VBScript syntax and usage is similar to VBA, except that the following features and keywords are omitted:

  • Language elements such as: #Const, #If...Then...Else, CVar, CVDate, Date, Declare (for declaring DLLs), Debug.Print, DoEvents, End, Erl, Error, GoSub...Return, GoTo, Like, LinkExecute, LinkPoke, LinkRequest, LinkSend, LSet, Mid, On Error GoTo, On...GoSub, On...GoTo, Optional, Option Base, Option Compare, Option Private Module, ParamArray, Resume, Resume Next, RSet, Static, Stop, Str, StrConv, Time, Type...End Type, TypeOf, and Val.
  • For Select Case statements, the use of the Is keyword, any comparison operators, or an expression containing a range a values using the To keyword.
  • Line numbers and line labels.
  • Declaration of arrays with a lower bound of anything other than zero (0).
  • The Add, Count, Item, and Remove keywords with collections. Accessing collections by using the ! operator. The Clipboard collection.
  • Fixed-length strings.
  • Financial functions.
  • All traditional Basic file input and output.
  • All intrinsic data types (except for the Variant type).
  • Object constants (you must use numbers or explicitly declare constants in script).

I won't be discussing the use of JScript in this article because VBScript lends itself so well to VBA and Office developers; however, if you're familiar with languages such as Java or JavaScript, you will have no problems learning and using JScript.

For more information on VBScript and JScript, see the Microsoft Scripting Technologies Web site, or the Microsoft Script Editor Help (available in the Microsoft Script Editor by pressing F1 or clicking Microsoft Script Editor Help on the Help menu).

Using the Microsoft Script Editor

To access the scripts behind a data access page, you use a scripting environment called the Microsoft Script Editor (MSE), which was mentioned earlier in this column. To access the MSE, with the page open in Design view, do one of the following:

  • Click Microsoft Script Editor on the Page Design toolbar.
  • On the Tools menu, point to Macro, and click Microsoft Script Editor.
  • Press ALT+SHIFT+F11.

In the MSE, the Toolbox (visible by clicking Toolbox on the View menu, or pressing CTRL+ALT+X) has some neat code reuse features. You can select and drag frequently used code to the HTML tab, and you can access the last 15 text-based Copy and Cut operations through the Clipboard Ring tab.

For more information on how to use the MSE, see Wes Kim's article titled Essentials of Creating Web Based Applications.

Scripting Examples

Now that you know a little bit about script and the MSE, let me show you a few examples of how script can be used on a data access page.

To refer to objects on pages through script, you can refer to their names. For example, on the Employees page in the Northwind sample database, you can refer to the Address, BirthDate, City, and Country control objects by using their names. You can also use the Object and Event lists in the MSE Code window to select an object and an event handler for that object. For example, by selecting the BirthDate control object in the Object list and the onmouseover event in the Event list, I can write code to execute when I move my mouse pointer over the BirthDate control:

<script language=vbscript for=BirthDate event=onmouseover>
<!--
    MsgBox "You moved the mouse pointer over the BirthDate control!"
-->

Likewise, the following code disables the City control:

<SCRIPT language=vbscript event=onmouseover for=City>
<!--
    Me.Disabled = True
-->

You also have access to a few built-in objects, the document and MSODSC objects, which I referred to in the Working with Data Access Pages by using VBA section above, and the window object, which refers to the open window when viewed in a Web browser.

To provide more of a real-world example, the following code, provided by a fellow Office developer, changes the color of items on a data access page that are almost out of stock to red.

<SCRIPT language=vbscript event=DataPageComplete(dscei) for=MSODSC>
<!--
' Highlights the items that are almost out of stock.
Dim Sect
Dim DSCConst
Dim bandHTML
   
' Check that the event fired for the DataPage object in the Products GroupLevel object.
If (dscei.DataPage.GroupLevel.RecordSource = "Products") Then
    Set DSCConst = MSODSC.Constants
    Set Sect = dscei.DataPage.FirstSection
    ' Go through the Section objects of the event's DataPage object.   
    Do
        ' Ignore the caption section.
        If (Sect.Type = DSCCconst.sectTypeHeader) then
            Set bandHTML = Sect.HTMLContainer      
      ' Conditional formatting on the units in stock. 
      ' Change the text to red if there are less than or equal to 20 units in stock.
      If (CInt(bandHTML.children("UnitsInStock").innerText) <= 20) Then
          bandHTML.children("UnitsInStock").style.color = "Red"
          bandHTML.children("UnitPrice").style.color = "Red"
          bandHTML.children("ProductName").style.color = "Red"
      End If
        End If
     Set Sect = Sect.NextSibling
    Loop Until (Sect Is nothing)
End If
-->
</SCRIPT>

Here's how the code works. The first line of code indicates that this is script for the MSODSC object's DataPageComplete event. When you write event handlers for the MSODSC object in the MSE, you must supply an argument to pass a required DSCEventInfo object into the event handler, because all MSODSC object events require a DSCEventInfo object. The MSE does not provide this argument for you automatically. So, this MSE-generated code:

<script language=vbscript event=DataPageComplete for=MSODSC>
<!--

-->
</script>

Should be changed to this code before you can fire the event:

<script language=vbscript event=DataPageComplete(dscei) for=MSODSC >
<!--

-->
</script>

Next, three objects are declared; the Sect object corresponds to a data access page's Section object, the DSCConst object represents the numerical constants that can be used with the DSCEventInfo object; and the bandHTML object corresponds to the HTML within the Sect object.

Moving on, when a particular expand button is clicked, if the section that expands contains a set of records that matches the Products record source, then the first section is retrieved, as shown in the following code:

If (dscei.DataPage.GroupLevel.RecordSource = "Products") Then
    Set DSCConst = MSODSC.Constants
    Set Sect = dscei.DataPage.FirstSection

After the first section is retrieved, the code moves into the first section's HTML and checks to see if the value of the UnitsInStock field is less than or equal to 20:

If (CInt(bandHTML.children("UnitsInStock").innerText) <= 20) Then
    bandHTML.children("UnitsInStock").style.color = "Red"
    bandHTML.children("UnitPrice").style.color = "Red"
    bandHTML.children("ProductName").style.color = "Red"
End If

If the UnitsInStock field is less than or equal to 20, the code turns the color of the text in the UnitsInStock, UnitPrice, and ProductName fields to red. The last several lines of code continue through all of the sections in the page, repeating the coloring process.

For more scripting examples, see the following seven articles by Meyyammai Subramanian:

The Microsoft Office Data Source Control

I've mentioned the Microsoft Office Data Source control (MSODSC) quite a few times in this column. The MSODSC is used in data access pages 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 MSODSC keeps track of the record the user is currently working with. For more complex "banded" pages, the MSODSC creates hierarchical groupings.

The MSODSC is an ActiveX control that has no visible user interface at run time. When you create a new data access page, Access inserts an OBJECT element in the page's underlying HTML that defines the MSODSC in the HEAD element of the page. The ID attribute of the OBJECT element is always set to "MSODSC."

The MSODSC integrates functionality from the Dynamic HTML (DHTML) object model and the ActiveX Data Objects (ADO) object model.

To see all of the available MSODSC methods, properties, and events, type MSODSC inside of any script block in the MSE, followed by a period.

To get Help with a particular MSODSC method, property, or event, in the MSE, press F1 or click Microsoft Script Editor Help on the Help menu. Similar to the Visual Basic Editor, you can also get context-sensitive Help by typing in an MSODSC method, property, or event into the MSE, positioning the cursor inside of the keyword and pressing F1.

The MSODSC gets its Help from two primary sources:

  • The DHTML, HTML, and CSS Reference (htmlref.chm).
  • The Microsoft Office Web Components Visual Basic Reference (owcvba10.chm).

The Office Web Components Reference will look familiar to you as an Office developer, but the HTML Reference will take some getting used to on your part.

For more information on working with the MSODSC, see Mark Roberts' article titled Scripting the Data Source Control in Data Access Pages.

Where to Go for More Info

For your convenience, I've gathered in one place a list of all of the references that I provided in this column:

 

Paul Cornell is an editor for the MSDN Online Office Developer Center and the Office developer documentation team. He spends his free time hiking, playing dominoes with his wife, and reading to his fifteen-month-old daughter.