Working with Data Access Pages

  • Article

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Data access pages are HTML documents comprised of HTML code, HTML intrinsic controls, and Microsoft® ActiveX® controls. Data access pages rely on DHTML and are designed to work best with Microsoft® Internet Explorer version 5 or later. (You can also use other Web browsers to display data access pages, but users will not be able to work with the data directly.)

A data access page can be a simple HTML document or can include data-bound controls that let users use a Web browser to interact with data stored in a database. Microsoft® Access provides a WYSIWYG design environment for creating data access pages and a means for deploying those pages and any necessary supporting files to a Web server, network server, or local file system. In addition, you can view and use data access pages within Access itself.

You might be tempted to think of data access pages as HTML documents that combine the best features of forms and reports for display on the Web, but that would be a very narrow definition. Data access pages do support much of the functionality you are used to in Access forms and reports, but they also provide a completely new way to interact with data from within an Access database or on the Web. These objects make it possible for users to use a Web browser to work with data in an interactive manner and in a way that has never been possible before. Data access pages are similar to forms in that you can use them to view, edit, or delete existing records, and you can also use them to add new records to an underlying record source. They are similar to reports in that you can sort and filter records as well as group records according to criteria you specify. In addition, while a page is displayed, you can manipulate the records that are displayed and change how the records are displayed.

You can create data access pages from scratch in Access or you can base them on existing HTML pages created by using some other HTML authoring tool. Only those pages created or modified within the Access design environment will be visible in the Pages object list in the Database window. This means that if you edit an HTML document in Access, a link to that document is created, even if you later use another tool to make additional changes. In addition, because the data access pages that appear in the Database window are links to the files stored on disk, you can delete a page in the Database window without deleting the file from disk. Unlike other objects in an Access database, data access pages are stored on disk as .htm files that are separate from the Access database in which they are created.

Creating a data access page in Access is similar to creating a form or report. Data access pages have their own object list in the Database window, and when they are opened in Design view, they have a toolbox and property sheet. The toolbox contains tools for inserting the HTML intrinsic controls, such as the text box, label, list box, and command button controls. In addition, the toolbox contains tools for inserting controls that are useful only on data access pages, such as expand, bound HTML, and scrolling text controls. The toolbox also contains tools for inserting the Microsoft® Office Web Component controls on a data access page.

Note   Because Access does not use the shared Microsoft® Office components related to Script objects or HTMLProject objects, you can't use these objects to work with scripts or the HTML code in data access pages through VBA code. To work with scripts or the HTML code in an Access DataAccessPage object, you use the Microsoft® Script Editor or a DataAccessPage object's Document property, which returns the Web browser's document object for an HTML page.

Creating, Saving, and Closing Data Access Pages

You will typically create data access pages in the data access page design environment in Access. However, there might be circumstances where you want to use VBA code to display a data access page within Access or to programmatically output a page to a separate location, such as a Web server on your local intranet.

You create a data access page programmatically by using the Application object's CreateDataAccessPage method. You can use the CreateDataAccessPage method to work with an existing HTML page as a data access page or to create a new, blank page. For example, the following code illustrates how to use this method to create a new page called BlankDAP.htm:

Application.CreateDataAccessPage FileName:="c:\WebPages\BlankDAP.htm" _
   CreateNewFileName:=True

The CreateDataAccessPage method creates a new blank page by default and adds a link to that page in the Pages object list in the Database window. If the file specified in the FileName argument already exists when the method is called and the CreateNewFileName argument is set to True (the default), an error occurs. If you set the method's CreateNewFileName argument to False, the FileName argument must contain the path and name of an existing file. If the file does not exist, an error occurs. If the FileName argument contains the path and file name of a file for which there is already a link in the Pages object list in the Database window, a new, uniquely named link is created that points to the same file on disk. If you provide a name but do not specify the path to a new file, the page is created in the current directory.

You can determine the path and file name for pages that appear in the Pages object list in the Database window by using the read-only FullName property of the AccessObject object that represents a particular data access page. For example, the following code prints the Name and FullName properties for each page in the current database:

Dim objDAP As AccessObject

For Each objDAP In CurrentProject.AllDataAccessPages
Debug.Print "The '" & objDAP.Name & _
   "' is located at: " & objDAP.FullName
Next objDAP

When you call the CreateDataAccessPage method, Access creates a temporary file on disk. To permanently save the page and create a pointer to it from the Pages object list in the Database window, you must use the Save method or the Close method of the DoCmd object.

The following code fragment illustrates how you could create a new data access page, work with the HTML in the page, and then create a link to the page and permanently save it to disk. The procedure also illustrates one way to use an error trap to handle files that already exist.

Function CreateDAP(strFileName As String) As Boolean
   ' This procedure illustrates how to create a data access
   ' page, work with the HTML in the page, and then save the page.
   ' The procedure also shows how to use an error trap to avoid
   ' the error that ocurrs if strFileName already exists.
   Dim dapNewPage As DataAccessPage
   
   Const DAP_EXISTS As Long = 2023
   
   On Error GoTo CreateDAP_Err
   ' Create the new page.
   Set dapNewPage = Application.CreateDataAccessPage(strFileName, True)

   ' Use the Document property to return the Internet Explorer 5
   ' document object, and then use the objects on the page to
   ' work with the HTML in the page.
   With dapNewPage.Document
      .All("HeadingText").innerText = "This page was created programmatically!"
      .All("HeadingText").Style.display = ""
      .All("BeforeBodyText").innerText = "When you work " _
         & "with the HTML in a data access page, you " _
         & "must use the document property of the page " _
         & "to get to the HTML. "
         .All("BeforeBodyText").Style.display = ""
   End With

   ' Close the page and save all changes.
   DoCmd.Close acDataAccessPage, dapNewPage.Name, acSaveYes

   CreateDAP = True

CreateDAP_End:
   Exit Function
CreateDAP_Err:
   If Err = DAP_EXISTS Then
      ' The file specified in strFileName already exists,
      ' so replace it with this new page.
      If MsgBox("'" & strFileName & "' already exists. Do you want to " _
         & "replace it with a new, blank page?", vbYesNo, _
         "Replace existing page?") = vbYes Then
         Set dapNewPage = Application.CreateDataAccessPage(strFileName, False)
         Resume Next
      Else
         CreateDAP = False
         Resume CreateDAP_End
      End If
   Else
      CreateDAP = False
      Resume CreateDAP_End
   End If
End Function

Note   When you create a new data access page, the display property of the style object for the HeadingText and BeforeBodyText elements is set to None by default. The preceding example also illustrates how to change this setting so the text you insert is visible when the page is viewed.

The CreateDAP procedure uses the data access page's Document property to return the Internet Explorer 5 document object and then sets properties of elements in the page. This procedure also uses the innerText property of an HTML element to specify the text that appears in the element.

Opening and Working with Data Access Pages

Although data access pages are designed to be viewed in a browser, you can display data access pages in Access to let users view and work with data as they do with forms and reports.

To open an existing data access page for which a link exists in the Pages object list in the Database window, you use the DoCmd object's OpenDataAccessPage method. You use the View argument of the OpenDataAccessPage method to specify whether to view the page in Design view or Page view. The following example illustrates how to open the Employees page in Page view:

DoCmd.OpenDataAccessPage "Employees", acDataAccessPageBrowse

To determine whether a page is currently open in Page view or Design view, you use a DataAccessPage object's CurrentView property.

The DataAccessPages collection contains all currently open data access pages. You can access an open page as a member of this collection and gain access to the properties and methods of the page itself as well as any controls on the page. The following sample code opens the Employees page in Design view, applies a theme, adds some text to the main heading, and then displays the page to the user:

With DoCmd
   .Echo False
   .OpenDataAccessPage "Employees", acDataAccessPageDesign
   With DataAccessPages("Employees")
      .ApplyTheme "Blends"
      .Document.All("HeadingText").innerText = "Today is " _
         & Format(Date, "mmmm d, yyyy"
   End With
   .OpenDataAccessPage "Employees", acDataAccessPageBrowse
   .Echo True
End With

Note   To save the changes you make to a data access page, you must make sure the page is in Design view, and then use the DoCmd object's Save method to save changes. If you programmatically make changes to a page while it is in Page view, those changes will be lost as soon as you call the Save method.

To get information about the pages in your database, including whether a page is currently open, you use the CurrentProject object's AllDataAccessPages collection. To specify or determine property settings for a page or controls on a page, you use the properties of a DataAccessPage object. The following sample uses both techniques to print information about data access pages to the Immediate window:

Sub DAPGetPageInfo()
   ' This procedure prints information about the data access pages
   ' in this database to the Immediate window.
   Dim objCurrentDAP       As AccessObject
   Dim strPageInfo         As String
   
   Const DAP_DESIGNVIEW    As Integer = 0
   Const DAP_PAGEVIEW      As Integer = 1
   
   Debug.Print "There are "; CurrentProject.AllDataAccessPages.Count _
      & " data access pages in this database."
   For Each objCurrentDAP In CurrentProject.AllDataAccessPages
      Debug.Print objCurrentDAP.Name & ":"
      Debug.Print vbTab & "File name: " & objCurrentDAP.FullName
      If objCurrentDAP.IsLoaded <> True Then
         Debug.Print vbTab & "The '" & objCurrentDAP.Name _
            & "' page is not currently open."
      Else
         Select Case DataAccessPages(objCurrentDAP.Name).CurrentView
            Case DAP_DESIGNVIEW
               Debug.Print vbTab & "The '" & objCurrentDAP.Name _
                  & "' page is open in Design view."
            Case DAP_PAGEVIEW
               Debug.Print vbTab & "The '" & objCurrentDAP.Name _
                  & "' page is open in Page view."
         End Select
      End If
   Next objCurrentDAP
End Sub

Note that the DataAccessPages collection contains DataAccessPage objects, whereas the AllDataAccessPages collection contains AccessObject objects.

When you work with data access pages inside Access, you can use VBA code in a form, for example, to specify or determine property settings of the page or controls on a page. In the next example, the SimplePageExample page is opened and the DataEntry property of the page's Data Source control is set to True so the page can be used only to enter new records:

Private Sub cmdSimpleDAPDataEntry_Click()
   With DoCmd
      .Echo False
      .OpenDataAccessPage "SimplePageExample", _
         acDataAccessPageBrowse
      DataAccessPages("SimplePageExample").Document _
         .All("MSODSC").DataEntry = True
      .Echo True
   End With
End Sub

Note   The ActiveX control that binds controls on a page to an underlying data source is the Microsoft® Office Data Source control (MSODSC). This control is included in every data access page you create but is not visible on the page itself. In the preceding example, the DataEntry property of the MSODSC is set to True. As you can see, the control is created by using an id property setting of

"MSODSC"

, and you use this id property to specify that the control is a member of the all collection in the data access page's Document object.

**Caution   **Although the HTML underlying the MSODSC is available, you should never modify the HTML directly either in Access or in any HTML authoring tool. To set properties of the MSODSC, you must use its property sheet in the Microsoft® Script Editor or use Microsoft® Visual Basic® Scripting Edition (VBScript) code in the data access page itself.

To open the SimplePageExample page so that it displays all records, you would use the following code:

DoCmd.OpenDataAccessPage "SimplePageExample", acDataAccessPageBrowse

You can also use the Microsoft® Script Editor to add to a page script that runs when the page is displayed or in response to events that occur on the page. The script you add to a page is part of the page itself and can run when the page is displayed in Access or in a Web browser.

Using the Microsoft Script Editor with Data Access Pages

The Microsoft® Script Editor is an editor and debugger that you can use to work with the HTML code and script in a data access page. This section describes how to use the Script Editor with data access pages.

When you view a data access page in the Script Editor, you see color-coded HTML code and script in the page. In addition, depending on the controls you have placed on the page, you might also see icons representing some controls. For example, the Data Source control is displayed as an icon. You can see the HTML and XML code underlying a control's icon by right-clicking the icon in the Script Editor and clicking Always View As Text on the shortcut menu.

When you create a new data access page, the page contains a two-dimensional section, represented by a <DIV> tag in the HTML code that uses the CLASS attribute MSOShowDesignGrid and a default ID attribute of SectionUnbound. When you add data-bound controls to this section of the page, Access automatically changes the ID attribute to reflect the controls you are using. For example, if you drag the Customers table to this section, Access changes the ID attribute to HeaderCustomers. You can place controls anywhere within the two-dimensional section as you can on a form or report. Outside of this section, controls cannot be positioned in this manner.

When you create event procedures, the Script Editor does not insert the event procedure arguments when they are required. You must insert these yourself. For example, every event associated with the Data Source control requires a single dscEventInfo argument. If you double-click the Current event for the Data Source control (MSODSC) in the Script Editor's Script Outline window, the following script block is inserted in your page:

<SCRIPT Language=vbscript FOR=MSODSC EVENT=Current>
<!--
--> 
</SCRIPT>

You must add the event's argument or arguments by adding parentheses and a name for the argument or arguments. It does not matter what name you use for each argument, and it does not matter if the argument is actually used in your script. You must supply all the arguments to the event, even if your code does not use them, or the code will not work. For example, here is the corrected event handler for the Data Source control's Current event:

<SCRIPT Language=vbscript FOR=MSODSC EVENT=Current(EventInfo)>
<!--
--> 
</SCRIPT>

Security Considerations for Data Access Pages

Because data access pages are designed to work both within and outside Access databases, security issues pertaining to data access pages require special attention. Understanding these issues requires an understanding of database security as well as Internet Explorer security.

See Also

Working with Reports, Forms, and Data Access Pages | Referring to Open Objects | The Data Behind Forms and Reports | Working with Controls on Forms and Reports | Using Web Technologies | Error Handling and Debugging | Working with Shared Office Components