Moving from Access to XML-Based Web Front-Ends

One of the primary objectives of Access is to render data in an organized, easily readable format. Datasheets, subdatasheets, and lookup fields are quite effective for displaying and editing the raw relational data stored in Jet and SQL Server tables. Access queries, forms, reports, PivotTables, and PivotCharts transform your raw data into usable information. The downside of using Access 2003 front-end applications to display information and edit data is that data consumers must run Access 2003 on their machines. With the exception of front-ends that use VBA code to implement disconnected Recordsets, each user must maintain at least one permanent connection to the back-end database while running the front-end application. Permanent connections limit the number of simultaneous users to 255 in the case of Jet; SQL Server (and MSDE) connections aren't limited, but each connection consumes server resources.

For more information on disconnected Recordsets, see "Taking Advantage of Disconnected Recordsets," p. 1312.

Microsoft Office is ubiquitous, but Access isn't; most Office licenses are the Standard Edition that doesn't include Access. Further, industry analysts report that when this book was written about 60% of the estimated 150 million Office installations were version 97 or earlier. Making your Access 2003 front end accessible to all Windows XP/2000 users requires using the Access 2003 Developer Extensions' Package Wizard to create a Windows installer (.msi) package of runtime Access 2003 and your application's .mdb/.mde or .adp/.ade front end.

Almost every PC user has a Web browser installed, and the majority perhaps as many as 90% of the PCs in the world run Internet Explorer (IE). Users of Windows 95+ who don't have the latest IE version can download it themselves at no charge. Thus viewing and editing Jet or SQL Server data in IE is an attractive alternative to distributing heavyweight runtime Access packages. Office 2003's requirement for Windows XP/2000 makes browser-based operations the only alternative for Windows Me, 98, and 95 users or those whose computers run Linux or Macintosh operating systems. XML plays a crucial role in Microsoft's approach to moving from conventional form-based multiuser Access applications to browser-based views of data in tables and Data access pages (DAP).

Note

This chapter assumes that you have a basic understanding of HTML, including code for generating formatted HTML tables. Familiarity with VBScript or ECMAScript is helpful for one section of this chapter, but isn't a necessity.

Making the Transition to Stateless Front-Ends

Conventional browser-based viewing and editing operations are said to be stateless, because successive operations don't depend on the outcome (state) of previous activities. Stateless applications don't depend on a continuous network connection for a session. Viewing or editing a page establishes a temporary connection to the Web server that lasts only as long as it takes to send a page request or data and receive a new or updated page from the server. Statelessness plays a very important role in determining how browsers interact with Jet and SQL Server data.

Note

The Web itself is stateless, but some applications such as Web-based credit-card purchases require maintaining some state information until the transaction completes or is abandoned. State information can be maintained on the client browser typically in the form of a cookie and on the Web server as a Session.State or similar object property.

The stateless nature of the Web is responsible for its scalability from tens of thousands of simultaneous users during its early stages to hundreds of millions today. The number of users that can connect simultaneously to a static Access Web page is limited only by your Web server's hardware and the available bandwidth of its Internet or intranet connection. The performance of the back-end data engine influences the scalability of live Access Web pages.

Separating Data from Presentation with XML

HTML is a language designed primarily to determine the presentation of text and graphic data for rendering by Web browsers. The Cascading Style Sheets (CSS) and Document Object Model (DOM) extensions to HTML enable browsers to modify (rerender) Web pages locally. One of the primary problems with HTML is that HTML combines the representation of data with the data itself. For example, conventional HTML tables contain both formatting and text data values for cells. To change the data values, you must regenerate the entire page and its table, which requires a round-trip to the sever. You can use DHTML to minimize round-tripping, but the data remains enmeshed in presentation code.

The objective of XML is to abstract (separate and condense) data and its structure from presentation of the data. In the general case, structured data can be any content a book, musical score, collection of images, or single news story. Structure is determined by the type of content for example, chapter, section, paragraph, and figure for books. The basic requirement is that all data within an XML document adhere to the predefined structure that's defined by its XML schema.

Tip

For a brief and somewhat whimsical introduction to XML, read Bert Bos's "XML in 10 points" at http://www.w3.org/XML/1999/XML-in-10-points.

In this book, data is restricted to the content of rows and columns of tables or query result sets, which can include text, numbers, images, sound, or video material stored in Jet OLE Object or SQL Server image fields. The structure of the data is determined by the table design and relationships between the tables, if more than one table is involved. XML stores tabular data as a structured text document and uses tags to specify the names of data elements. XML documents generated by query result sets are simple and easily readable in a browser with an XML parser, such as IE 5+. To represent data in related tables, XML uses a hierarchical structure of nested elements that use tags to identify the source tables. Today's ultimate destination of the content of most XML documents is a Web browser, which requires transformation of XML data to formatted HTML code. The examples of this chapter use sets of XML data documents and XML style sheets to deliver Web pages from Access tables and queries.

Note

Ultimately, the destination of most XML documents will be server-based applications, not Web browsers. XML Web services, which are the subject of Chapter 31, "Creating and Consuming XML Web Services," represent only one aspect of server-to-server transfer of XML documents.

Unlike basic HTML, which uses a standardized set of embedded tags (such as <p>...</p> for a paragraph and attributes (href="http://www.whatever.com/somepage.htm") to define visible objects on or pointers to a Web page, you can define your own tags and attributes to specify (delimit) data. The XML 1.0 specification (http://www.w3.org/TR/2000/REC-xml) defines only one tag with an attribute <? xml version="1.0" ?>. Exporting data from tables or query result sets generates tags and, in some cases, attribute names, from the table and field names.

Understanding the Role of Access's ReportML

Access 2003 installs two formidable style sheets: RPT2DAP.xsl and RPT2HTML4.xsl in your ...\Office11\AccessWeb folder. ReportML is a Microsoft specification for the representation of Access objects tables, queries, forms, and reports, as well as the controls on forms and reports in a well-formed XML data document. The RPT2DAP.xsl style sheet transforms ReportML to DAP; RPT2HTML4.xsl transforms ReportML to HTML 4.0 code that renders a Web page. Tables and queries export to HTML tables; forms export as continuous forms. Simple reports export a page that closely emulates a report snapshot.

Access 2003's ReportML generates .xsl, and .htm, files, which IE 6+ processes by creating two MSXML.DOMDocument ActiveX objects to render the Web page. Alternatively, ReportML can generate .asp files to deliver HTML from the server. MSXML is the type library for Microsoft's XML parser version 5.0 when this book was written. DOMDocument is the W3C DOM XML Class. DOM is the application programming interface (API) for valid HTML documents and well-formed XML documents. The W3C specification of November 2000 is for DOM version 2.0 (http://www.w3.org/TR/DOM-Level-2-Core/).

Figure 23.1 is a diagram of the processes involved and the files created when you export an Access 2003 table, query, form, or report object to XML. The diagram is for XSL presentation of the object by an .htm file processed by the client (the default). Creating an .xsd schema file is optional; ReportML doesn't use the schema information. Sections later in this chapter describe how elements of ReportML contribute to the .xsl file for the exported object.

Tip

If you want to inspect the temporary ObjectName_report.xml file generated by AccessWeb.dll, add the following Registry key:

 HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Access\ReportML 

To add the key automatically, double-click the ReportML.reg file in the \Seua11\Chaptr23 folder of your \Program Files folder or the accompanying CD-ROM.

The following two examples illustrate how the code in the ObjectName.htm and ObjectName.asp files use the transformNode method to apply the style sheet to the XML data document.

Figure 23.1. This diagram outlines the steps involved in generating in IE 6+ a static Web page from an Access table, query, form, or report object.

VBScript

Following is the VBScript for the vwUnion.htm file, which loads the vwUnion.xml and vwUnion.xsl files as data and style sheet streams into two MSXML.DOMDocument objects. The Document.Write(objData.TransformNode(objStyle)) instruction generates the HTML elements to display the table. IE 6+ running on the client handles the document processing.

[View full width]
 <HTML xmlns:signature="urn:schemas-microsoft-com:office:access"> <HEAD> <META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=UTF-8"/> </HEAD> <BODY ONLOAD="ApplyTransform()"> </BODY> <SCRIPT LANGUAGE="VBScript">     Option Explicit     Function ApplyTransform()        Dim objData, objStyle        Set objData = CreateDOM        LoadDOM objData, "vwUnion.xml"        Set objStyle = CreateDOM        LoadDOM objStyle, "vwUnion.xsl"        Document.Open "text/html","replace"        Document.Write objData.TransformNode(objStyle)     End Function     Function CreateDOM()        On Error Resume Next        Dim tmpDOM        Set tmpDOM = Nothing        Set tmpDOM = CreateObject("MSXML2.DOMDocument.5.0")        If tmpDOM Is Nothing Then           Set tmpDOM = CreateObject("MSXML2.DOMDocument.4.0")        End If        If tmpDOM Is Nothing Then           Set tmpDOM = CreateObject("MSXML.DOMDocument")        End If        Set CreateDOM = tmpDOM     End Function     Function LoadDOM(objDOM, strXMLFile)       objDOM.Async = False       objDOM.Load strXMLFile       If (objDOM.ParseError.ErrorCode <> 0) Then           MsgBox objDOM.ParseError.Reason       End If    End Function </SCRIPT> </HTML> 

With the exception of the .xml and .xsl file names, the code for all .htm files you generate by exporting Access objects is identical.

VBScript

The following VBScript in the vwUnion.asp file generates the MSXML2.DOMDocument.5.0 objects on the Web server and delivers browser-agnostic HTML code for the table to the client browser. The Session.CodePage = 65001 line shown in bold type makes extended ASCII/ANSI characters appear correctly in the table. 65001 is the UTF-8 code page.

 objData.async = false if (false) then    Set objDataXMLHTTP = Server.CreateObject("Microsoft.XMLHTTP")    objDataXMLHTTP.open "GET", "", false    objDataXMLHTTP.setRequestHeader "Content-Type", "text/xml"    objDataXMLHTTP.send    objData.load(objDataXMLHTTP.responseBody) else    objData.load(Server.MapPath("vwUnion.xml")) end if Set objStyle = CreateDOM objStyle.async = false objStyle.load(Server.MapPath("vwUnion.xsl")) Session.CodePage = 65001 Response.ContentType = "text/html" Response.Write objData.transformNode(objStyle) Function CreateDOM()   On Error Resume Next   Dim tmpDOM   Set tmpDOM = Nothing   Set tmpDOM = Server.CreateObject("MSXML2.DOMDocument.5.0")   If tmpDOM Is Nothing Then      Set tmpDOM = Server.CreateObject("MSXML2.DOMDocument.4.0")   End If   If tmpDOM Is Nothing Then      Set tmpDOM = Server.CreateObject("MSXML.DOMDocument")   End If   Set CreateDOM = tmpDOM End Function %> 

Processing the documents on the Web server is resource-intensive, so it's a good practice to specify server-side processing for static pages only if you need to support browsers that can't process .xml and .xsl files.