Danny J. Lesandrini
In the May 2002 issue of Smart Access, Danny Lesandrini showed how easy it is to migrate existing Access reports to the Web using the ExportXML function of Access 2002. A little tinkering with this feature of Access has revealed its potential as a .NET report-writing tool. Follow along as he shows how to create complex reports for .NET applications using your favorite development tool, Microsoft Access.
Why talk about .NET in a Microsoft Access publication? Because some day, Microsoft Office is going to be "DotNetified" and we might as well get ready for it. While it's true that the next version of Office isn't a .NET product, competitive Access developers will use this reprieve to prepare for .NET. Now is the time to identify ways to leverage your current Access skills and find your own .NET niche.
Can you fit into this niche?
I've commented before that, in my opinion, reporting is one of the greatest features of Microsoft Access. The Access report designer is so powerful and intuitive that even novice Access users can create complex reports in minutes. For years now, it's been possible to export these reports as static Web pages and, with some difficulty, as report snapshot files that can be integrated into Web pages.
Starting with Access 2002, reports could also be exported as XML/XSL transformed HTML pages. The advantage of this over the Export as HTML function in previous versions of Access is twofold:
|•||All user-defined presentation formatting is preserved.|
|•||Since the XML data file is updatable, the formerly static report is now dynamic.|
The code required to export an existing Access 2002 report in XML/XSL format is relatively simple, as described in the article on XML in Access that I wrote in the January 2002 issue of Smart Access. The Application object's ExportXML function does all of the work. All you have to do is supply the necessary parameters, as shown in this example (sample code is included with the accompanying Download file):
Dim sRpt As String, sXML As String, sXSL As String
sRpt = "rpt001"
sXML = "C:\InetPub\wwwRoot\Reports\rpt001.xml"
sXSL = "C:\InetPub\wwwRoot\Reports\rpt001.xsl"
iFrmt = 4 'ASP format (use 0 for HTML output)
ExportXML acExportReport, sRpt, sXML, , _
sXSL, , , iFrmt
In the code, I've established the name of the report to export, the name of the XML file to create, and the name of the XSLT stylesheet that will transform the XML file, and I've told the method to create an ASP page that will run the XSLT stylesheet when the page is requested. The stylesheet (which is automatically generated for you by the ReportXML function) will convert the data in the XML file into an HTML version of your report that can be displayed in a Web browser.
Why is this important to you? If you already know how to create Access reports, those six lines of code turn you into a .NET report designer. There are a few gotchas you need to be aware of, and another couple of lines of code to integrate these files into .NET applications, but the hardest part is done.
Right now, you may be wondering what was so hard about what I just did. If so, you've never tried to create an XSL transform from scratch. XSLT is a tough language to work with, and creating even trivial reports with it is hard work. Even the best XML/XSL developers that I know leverage previously designed templates to ease the coding process. If you're new to XSLT, you won't have those resources. If you search the Internet, you'll find numerous tools claiming to be WYSIWYG editors for creating XSL transforms. I researched half a dozen of those tools in my quest for a simple way to deliver reports for an ASP.NET application that I was developing for the State of Colorado, but none of them were at all intuitive. In the end, I returned to my old friend, Microsoft Access.
As an example of the kind of difficulties you face in working with XSLT, one of the utilities I reviewed was so confusing, I couldn't even figure out how to get started. A help desk technician for the company suggested that I send him the XML/XSL pair of files generated by the code in this article so he could explain to me how to use their tool to reproduce the same output. After several rounds of e-mail back and forth, we determined that his utility could create the output, but I'd still have to know XSLT syntax to get the job done. That was for a simple report. When I showed him the report that I really wanted to write, he said he could do it, but that (a) it would take the better part of a day to create, and (b) I'd have to know advanced XSLT syntax. That's not what I call WYSIWYG.
Access, on the other hand, is the quintessential WYSIWYG report designer, and you don't need to know anything about XSLT to create truly great Web reports. Report writing is critical to creating applications. If you're good at creating reports in Access then you can, with just a little mentoring, deliver impressive reports in .NET applications. This could be your Access-to-.NET niche. There are, however, some issues to keep in mind when integrating reports into your .NET application.
Dynamic report content
Exporting an Access report in XML format, as I did in my sample code, creates three files: rpt001.xml, rpt001.xsl, and rpt001.asp. The XML file contains the data, the XSL file contains the presentation information (the XSLT program), and the ASP file performs the transform. The ASP code generated by Access looks something like the following, which I've edited and simplified somewhat:
Set objData = _
Set objStyle = _
Response.ContentType = "text/html"
At this point, you have all the files necessary to deliver your Access report to the Internetall you need to do is copy the files to your Web site. Once the files are in place, your Web application must only direct the users to the ASP page for them to view your report. To make the contents of the report dynamic, you'd need to follow these steps:
|1.||Collect some sort of criteria from the Web user.|
|2.||Start Access from your ASP code, using Automation.|
|3.||Create a new SQL statement to extract the data matching the user request.|
|4.||Re-execute the ExportXML function to update the XML file only.|
This is the solution I recommended in my previous article (I've included the code in the Download file for this article). This time, however, I want to show you how you can incorporate this report into an ASP.NET solution.
Though the code used in ASP.NET is somewhat different, the idea is the same as what you saw in the ASP version of the application. I still need XML and XSL files, but here the transform is performed by a special ASP.NET WebForms control, which simplifies the process. Adding the XML WebForms control to your ASP.NET can be done with drag-and-drop in Visual Studio .NET or by adding this line of code to an .aspx in any text editor:
<asp:Xml id="objXML" runat="server" />
This control takes two inputs: DocumentSource (the name of the XML file) and TransformSource (the name of the XSLT file). While it's possible to set the DocumentSource and TransformSource properties in HTML, I'll do it in the Visual Basic .NET code-behind file that's part of my aspx page. I'll also insert code to dynamically update the XML data file, based on the user's criteria. It takes only two lines of VB.NET code to instruct the XML WebForms control on how to perform the transformation:
objXML.DocumentSource = "rtp001.xml"
objXML.TransformSource = "rpt001.xsl"
The XSLT stylesheet will automatically be used to generate my report from the XML data file before the page is sent to the user's Web browser.
I created a function called UpdateFileXML to retrieve new data from my Access database, format the data as XML, and output it to the XML file. If this were part of a real Web application, the data filename would be renamed using the current session ID so as not to cause collisions with other simultaneous users. The code is relatively straightforward, though the syntax may be new to you if you haven't yet played with ADO.NET. A DataAdaptor object is used to retrieve the data and populate a DataSet object, which is in turn used to convert and export the data to XML:
Private Sub UpdateFileXML ()
' Declare and set string variables
Dim sXMLFile As String
Dim sMDBFile As String
Dim sConn As String
Dim sSQL As String
sFile = MapPath("rpt001.xml")
sMDB = MapPath("PubsLite.mdb")
sConn = "Provider=Microsoft.JET.OLEDB.4.0;"
sConn = sConn & "Data Source=" & strMDB
' Retrieve selected store value for query
strStore = Request.Form("selStore")
strSQL = "Select * From qryBookSales " & _
"Where stor_name='" & strStore & "'"
' Declare and set data access variables
Dim cnn As OleDbConnection
Dim cmd As OleDbCommand
Dim objDA As OleDbDataAdapter
Dim objDS As DataSet
cnn = New OleDbConnection(sConn)
cmd = New OleDbCommand(sSQL, cnn)
' Populate our DataSet object
objDA = New OleDbDataAdapter(cmd)
objDS = New DataSet("dataRoot")
' Rename table to match our XSLT file
objDS.Tables(0).TableName = "qryBookSales"
' Output data as XML to our file
Check out the results of this code in Access (see Figure 1) and IE (see Figure 2).
Getting Access to play with .NET
If you were to test your ASP.NET report, though, you'd be in for a disheartening surprise. The ASP.NET application displays the following error: "Missing mandatory attribute 'version.'"
At first, this error confused me, probably because the message is misleading. The "version" of my Access-generated XSL file is the same as that for the samples provided in the .NET Help article that I used to create this application, namely <?xml version='1.0'?>. A quick search in Google newsgroups revealed that this problem isn't with the XML version, but rather with the XSLT namespace. The Access-generated XSL file is using an older namespace for XSLT that isn't compatible with the namespace that's expected by the .NET WebForms control. The explanation that I found at the microsoft.public.dotnet.xml newsgroup says:
|•||It is required as part of the XSL spec [in .NET] that all qualified names starting with XSL must belong to the namespace "http://www.w3.org/1999/XSL/Transform" otherwise the XSL processor throws an exception.|
"Great," I thought, "I'll just change the namespace," but that created a new set of problems. Access uses a different XSL namespace on purpose, in order to leverage some cool scripting functions that make formatting the data easier. Changing the namespace breaks all the scripting functions. So, to make the XSL file compatible with the ASP.NET WebForms control, I'd have to completely rewrite the XSL file, which is exactly what I was trying to avoid.
However, all is not lost. I still have a compatible pair of XML/XSL files that can successfully be transformed with the VBScript code used above in our traditional ASP code. For now, this solution will serve as a workaround, though it would be nicer if I could use genuine .NET code for the entire solution. The complete process to deliver dynamic Access-generated reports in an ASP.NET application works like this:
|1.||Create the report with any degree of complexity that you want using the Access report designer.|
|2.||Export the report as XML with its XSL presentation information.|
|3.||Include these files in your ASP.NET solution folder (or subfolder).|
|4.||Write ASP.NET code to collect user-selected criteria and update the XML data file.|
|5.||Output transformed HTML using traditional VBScript code to execute the transformNode method.|
Office to the rescue?
With the next version of Microsoft Office, XML support in Access will continue to improve, especially with respect to the support for XSLT namespaces. Microsoft's white paper on the new features planned for the next version of Access says that the namespaces will be updated to the current versions. You can read the document yourself: Search MSDN for "Access 11" and select the first item returned. The paper is titled "Microsoft Access 11 Beta 1 Preview."
Ideally, this change will make a pure Access-to-.NET solution possible, allowing you to deliver complex reports to the Web using the best report writer in the business. It will also give Access report designers a great niche in the .NET world.
Your download file is called 305LESANDRINI.ZIP in the file SA2003-05down.zip
This is found on this page
For complex SQL Server upsizing projects and .Net Conversions, Garry Sells the MUST upsizing tool.
Find out more on upsizing and .Net Conversions from Access here http://www.vb123.com.au/up/