By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,435 Members | 1,967 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,435 IT Pros & Developers. It's quick & easy.

MS Access reports to XML to .NET

P: n/a
D. Lesandrini published an article some time ago about exporting Access
reports as XML, and then updating those XML reports dynamically in
..NET.
See->http://msdn.microsoft.com/library/de...tml/vs03g9.asp.

I'm not getting it to work. If I use the XML and XSL file as generated
by Access, it works OK, but if I generate the XML file myself and try
to transform that with the XSL file, I just end up with a big block of
completely unformatted text.

I've noticed that the header of the XML file is different when
generated from .NET. Specifically, the first two lines of the file
generated from Access look like:

<?xml version="1.0" encoding="UTF-8"?>
<dataroot xmlns:od="urn:schemas-microsoft-com:officedata"
generated="2006-06-27T10:06:57">

When generated by .NET, it looks like:

<?xml version="1.0" standalone="yes"?>
<dataRoot>

As an experiment, I've tried manually copying the Access-generated
lines over the .NET generated lines. In that case I get nothing at all.
Just an empty string.

The code I'm using is basically Lesandrini's, looking something like:

' connect to database
strConn = connection

' load the data and write it to our xml file.
Dim strSQL As String
strSQL = "SELECT EMPLOYEES.EmpID, EMPLOYEES.Title, " _
& "EMPLOYEES.FirstName, EMPLOYEES.LastName " _
& "FROM EMPLOYEES " _
& "ORDER BY EMPLOYEES.EmpID;"

Dim cnn As OleDbConnection = New OleDbConnection(strConn)
Dim cmd As OleDbCommand = New OleDbCommand(strSQL, cnn)
cnn.Open()

Dim objDA As OleDbDataAdapter = New OleDbDataAdapter(cmd)
Dim objDS As DataSet

objDS = New DataSet("dataRoot")
objDA.Fill(objDS)
objDS.Tables(0).TableName = "qryDelinquent"

objDS.WriteXml(strFile)

Dim objData As Object
Dim objStyle As Object

objData = Server.CreateObject("MSXML.DOMDocument")
objStyle = Server.CreateObject("MSXML.DOMDocument")

objData.load(Server.MapPath("RptDelinquent.xml"))
objStyle.load(Server.MapPath("RptDelinquent.xsl"))

Response.ContentType = "text/html"

Dim strOut As String
strOut = objData.transformNode(objStyle)

This is using Access 2003, BTW.

Another thing I've noticed is that the header of the XLS file included
in the sample .ZIP attached to the article, is different from what is
generated by Access:

Access:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:msxsl="urn:schemas-microsoft-com:xslt"
xmlns:fx="#fx-functions" exclude-
result-prefixes="msxsl fx">
<xsl:output method="html" version="4.0" indent="yes"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"/>
<xsl:template match="//dataroot"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

Sample:

<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/TR/WD-xsl"
language="vbscript">
<xsl:template match="/">

Has anybody been able to successfully use Access-generated XML reports
in their .NET apps?

ddk

Jun 27 '06 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.