Custom Project Exporting an Access Report to an XML Data File with ASP

This custom project uses the copy of the Northwind sample database you created in Hands-On 29-8.

Part 1: Creating a VBA Procedure to Export Invoice Data

1. In the Database window, choose Tools | Macro | Visual Basic Editor.

2. In the Visual Basic Editor window, choose Insert | Module to add a standard module to the current VBA project.

3. In the module's Code window, enter the Export_InvoiceReport procedure as shown below.

Sub Export_InvoiceReport()

Application.ExportXML ObjectType:=acExportReport, _ DataSource:="Invoice", _ DataTarget:="C:\Learn_XML\Invoice.xml", _ PresentationTarget:="C:\Learn_XML\Invoice.xsl", _ ImageTarget:="C:\Learn_XML", _ WhereCondition:="OrderID=11075"

MsgBox "Export operation completed successfully." End Sub

Take a look at the last two arguments of the ExportXML method used in the procedure above. ImageTarget specifies that images displayed on the Invoice report are to be placed in the Learn_XML folder. The WhereCondition argument specifies that we want only the data for Order 11075.

Part 2: Executing the VBA Code to Export Data

1. Place the insertion point anywhere within the Export_InvoiceReport procedure code and choose Run | Run Sub/UserForm. Access executes the procedure code and displays a message.

2. Click OK to clear the informational message.

3. Switch to Windows Explorer and locate the Learn_XML folder.

4. Notice that Access has created a number of files: Invoice.xsl (stylesheet), Invoice.xml (XML document), Invoice.htm (HTML document), and two image files (PictureLogo.bmp and NameLogo.bmp).

Part 3: Viewing the Invoice Page in the Browser

1. Double-click the Invoice.htm file in the Learn_XML folder.

If you are using Internet Explorer, the file opens up as shown in Figure 29-17. When you compare this output with the original Access invoice report, you will notice that the invoice displayed in the browser is an exact image of the report displayed in the Access user interface.

2. Close the browser.

Part V

ül Invoice

- Microsoft Interne! Explorer

m

Ffe Edit

View Favorites Tools Help

*r

Qm

O S 1 G P

Search Favorites

a

•DB»

WAS-'.', g

C: V-earnJiMLiInvojce. htm

V flc» II

/ TRADERS

f

INVOICE

Ont Vzríjh P^'nä, WÍÍ3156 Phone:

Sate: 11-«01414

1 í-206-555-1417 Fat: 1-206-555-59SS

Mil lu TO:

Richter Supermarkt

Bill lU: Richter Supermarkt

Slarenweg 5

Grenzacherweg 237

Genève 1204

Genève 1203

Switzerland

Swllzerland

1 linier ID:

Customer ID: Salesperson:

Order Dale:

equired Bate:

■iltiji[inl Elate:

Ship Via: 1

II 11075

RICSU I Laura Callahan

5/6/1998

6/3Í1998 1

1 United Packaga 1

■ Prüfet I)

Product Name:

Quantity;

urm Price:

Disminuí:

SIMM Price: 1

i

Chang

10

«19,00

15%

M 61.50

te

Spegesjld

30

$12.00

13%

»-cc:c

76

Lakkalikööri

2

$18.00

15%

(30.60

Mtutill:

S49S1LI

Ffilgtll:

mi 9

mat

3504.29

V

tí_

J

Hit Computer

Figure 29-17: Viewing the exported invoice report in Internet Explorer.

Figure 29-17: Viewing the exported invoice report in Internet Explorer.

Part 4: Examining the Content of the lnvoice.htm File

1. In Windows Explorer, right-click the Invoice file and choose Open With | Notepad. The content of the Invoice file is shown below:

<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, "Invoice.xml"

Set objStyle = CreateDOM LoadDOM objStyle, "Invoice.xsl"

Document.Open "text/html","replace"

Taking Your VBA Programming Skills to the Web

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

Notice that when the htm page loads it executes the VBScript ApplyTransform function:

<BODY ONLOAD="ApplyTransform()">

The VBScript code uses a software component called the XML Document Object Model (DOM). The DOM offers methods and properties for working with XML programmatically, allowing you to output and transform the XML data. The XML DOM is automatically installed by Internet Explorer, as well as by Windows 2000/XP and Internet Information Services (IIS) 5.0.

The DOMDocument object is the top level of the XML DOM hierarchy and represents a tree structure composed of nodes. You can navigate through this tree structure and manipulate the data contained in the nodes by using various methods and properties. Because every XML object is created and accessed from the DOMDocument, you must first create the DOMDocument object in order to work with an XML document.

The ApplyTransform function above begins by setting an object variable (objData) to an instance of the DOMDocument that's returned by a custom CreateDOM function:

Set objData = CreateDOM

If you take a look at the CreateDOM function that appears at the bottom of the VBScript code, you will notice that a reference to the DOMDocument is set

Part V

via the CreateObject method of the Server object. Because different versions of the MSXML parser may be installed on a client machine (DOMDocument5, DOMDocument4, DOMDocument, etc.), the function attempts to instantiate the DOMDocument object using the most recent version. If such a version is not found, it looks for older versions of the MSXML parser that may exist. It is extremely important that only one version of the DOMDocument is used, since mixing DOMDocument objects from different versions of the MSXML parser can cause ugly errors.

Once the DOMDocument object has been instantiated, the LoadDOM function listed at the bottom of the page is called. This function expects two parameters: objectDOM, which is the objData variable referencing the DOMDocument, and strXMLFile, which is the name of the file to load into the DOMDocument object. To ensure that Internet Explorer waits until all the data is loaded before rendering the rest of the page, the Async property of the DOMDocument is set to False:

objDOM.Async = false objDOM.Load strXMLFile

The Load method is used to load the supplied file into the objData object variable. This method returns True if it successfully loaded the data and False otherwise. If there is a problem with loading, a description of the error is returned in a message box.

The Document object of XML DOM exposes a parseError object that allows you to check whether there was an error when loading the XML file or stylesheet. The parseError object has the following properties:

Table 29-2: parseError object properties

Property

Description

errorCode

Error number of the error that occurred.

filepos

Character position within the file where the error occurred.

line

Line number where the error occurred.

linepos

Character position within the line where the error occurred.

reason

Text description of the error.

srcText

The source (text) of the line where the error occurred.

url

URL or path of the file that was loaded.

After loading the Invoice.xml data file into the DOM software component, the ApplyTransform function repeats the same process for the Invoice.xsl file. After both files are successfully loaded, the transform is applied to the data using the TransformNode method:

Document.Write objData.TransformNode(objStyle)

The TransformNode method performs the transformation by applying the XSLT stylesheet to the XML data file. The result is the HTML document displayed in the browser as shown in Figure 29-17 earlier in this chapter.

2. Close the Invoice.htm file and exit Notepad.

Taking Your VBA Programming Skills to the Web

0 0

Post a comment