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
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
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.