Create Web Reports by Wrapping Your Data With HTML

OK so you retrieved all that data from the database. Now how do you display it your user's browser? This sample shows how to create a web report from your data that can be displayed in any browser.

The download code contains a class module you can quickly and easily incorporate into your VB project. The class module accepts an ActiveX Data Objects (AD0) recordset and creates a .htm file from your data that is browser ready.

Download Source Code

This program opens an ADO connection to the WebReport.mdb file and retrieves data from the employee table into an ADO recordset. This part is mainly symbolic and is meant to be representative of your real life data retrieval choirs. Nothing fancy here. Once the ADO recordset is populated, the real meat of the program begins.

The core of this program is a class module that exposes a couple of methods. The CreateReport method accepts as input the ADO recordset and returns a string containing the recordset's data wrapped with the appropriate HTML tags to tabularize and display your data in a browser.

An HTML file consists of two main parts. The head and body sections. CreateReport begins by building the HTML tags necessary for the head section. The head section contains mostly meta tag information and the name of the report to be displayed in the browser's title bar.

The bulk of the HTML file lies in the body section. CreateReport builds a table to neatly display the data. The first row of the table contains the names of the fields pulled from the database. This row is built by looping through the recordset's fields collection and retrieving the name property.

The table's body is built similarly. CreateReport loops through the recordset and for each row, enumerates the fields collection. This time retrieving the value property. In the process of building the html string, CreateReport allows you to dictate the font, table border, cell padding, report title, subtitle, footer, company logo,... and many other items necessary to produce a professional looking report. These criteria are specified by setting properties of the class module.

Worth noting is the liberal use of carriage returns in CreateReport. Although they are not required by the HTML to display your data, they make viewing the resulting .htm file in a word processor much easier.

Once the recordset is traversed and the HTML string is built it must be written to a .htm file. This is accomplished via a call to the UpdateHTMLFile method which uses the Scripting Runtime Engine's FileSystemObject to store the string in the specified file.

This sample uses ADO 2.1 to connect to the Microsoft Access database. You need to have Microsoft ActiveX Data Objects 2.1 or later installed to run this code.

Download this project and check the path in the code to the WebReport.mdb file. The code uses app.path so it should work but you may need to verify the path.

Run the project and enter the desired values in the textboxes. The defaults should be fine. Click the Create Report button to create the report. Using Explorer, navigate to the folder containing the report. Double click on the .htm file to display the report in your default browser.