Helper methods for running Access generated reports from ASP.NET

I am using Access reports exported as xml/xsl and  show them in ASP.NET as it is described in Access: Your New .NET Report Writer by Danny J. Lesandrini.


There are a few methods then can be useful for others who want to use the same technicque.


Note that the original article uses DataSetName = “dataRoot” which causes problems for generated xsl files, because XML is case-sensitive and MS access generate XML with  “dataroot“ element-all low case.


‘TODO create COM wrapper and use early binding 

   

‘ domDoc = Server.CreateObject(“MSXML.DOMDocument”)


    ‘sXslPath -absolute XSL file path


    Public Shared Function DOMDocumentTransform(ByVal domDoc As Object, ByVal sXslPath As String) As String


          Instead of using the XML Web Control we can use traditional VB Script solution.


        Dim objStyle As Object


        Dim Server As HttpServerUtility = HttpContext.Current.Server


        objStyle = Server.CreateObject(“MSXML.DOMDocument”)


        objStyle.load(sXslPath)


        Dim strOut As String


        strOut = domDoc.transformNode(objStyle)


        Return strOut


    End Function


    ‘TODO create COM wrapper and use early binding to return “MSXML.DOMDocument”)


    Public Shared Function DataSetToDOMDocument(ByVal ds As DataSet) As Object


        Dim domDoc As Object ‘TODO create COM wrapper and use early binding


        domDoc = HttpContext.Current.Server.CreateObject(“MSXML.DOMDocument”)


        domDoc.loadXML(ds.GetXml)


        Return domDoc


    End Function


    ‘TableName must be exactly the same as in Access Generated xsl file, case sensitive!


    Public Shared Function LoadDataSet(ByVal connString As String, ByVal sSQL As String, ByVal TableName As String) As DataSet


        ‘ load the data and write it to our xml file.


        Dim ds As DataSet


        ds = SqlHelper.ExecuteDataset(connString, CommandType.Text, sSQL)


        ds.DataSetName = “dataroot” ‘ESSENTIAL all low case


        ds.Tables(0).TableName = TableName ‘XSLT is case sencitive


        Debug.WriteLine(ds.GetXml)


        Return ds


    End Function