| Microsoft Office 2000/Visual Basic Programmer's Guide | |
Data access pages are new to Access 2000. Data access pages are HTML documents comprised of HTML code, HTML intrinsic controls, and ActiveX controls. Data access pages rely on DHTML and are designed to work best with 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. 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 may 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 allow 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 like 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 like 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 the 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. For more information about the Office Web Components, see Chapter 12, "Using Web Technologies."
To see examples of data access pages, see the Solutions9.mdb file in the ODETools\V9\Samples\OPG\Samples\CH05 subfolder on the Office 2000 Developer CD-ROM. The modDataAccessPageCode module in this database contains most of the VBA code that works with data access pages. You can use the DataAccessPages form to display the data access pages within the sample database.
Note Because Access does not use the shared 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. For more information about working with shared Office components, see Chapter 6, "Working with Shared Office Components." For more information about working with the document object, see Chapter 12, "Using Web Technologies."
You will typically create data access pages in the data access page design environment in Access. However, there may 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
The CreateDAP procedure is available in the modDataAccessPageCode module in Solutions9.mdb in the ODETools\V9\Samples\OPG\Samples\CH05 subfolder on the Office 2000 Developer CD-ROM.
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. For more information about working with the Internet Explorer 5 object model, see Chapter 12, "Using Web Technologies."
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 just like 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
Important 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 see an example of how you can make changes to the text in a data access page regardless of the current view, see the DAPInsertText procedure in the modDataAccessPageCode module in Solutions9.mdb in the ODETools\V9\Samples\OPG\Samples\CH05 subfolder on the Office 2000 Developer CD-ROM.
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. The DAPGetPageInfo procedure is available in the modDataAccessPageCode module in Solutions9.mdb in the ODETools\V9\Samples\OPG\Samples\CH05 subfolder on the Office 2000 Developer CD-ROM.
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 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. For more information about working with scripting event procedures in an HTML page, see Chapter 12, "Using Web Technologies."
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. For general information about using the Script Editor, see Chapter 12, "Using Web Technologies."
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 may 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 just like 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>
For information about how to use the Script Editor to debug script in a data access page, see Chapter 8, "Error Handling and Debugging."
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. For more information about securing data access pages, see Chapter 18, "Securing Access Databases."