Viewing and Printing Access Reports with ASP and/or VisualBasic, Part 2By Richard Chisholm
In Part 1 we looked at potential errors one could encounter when preparing
to view Access reports through a Web page. Ugh.
Now that we've got that part out of the way, on to the good stuff. The
GetReports app will first
find all the databases in the local directory using the FileSystemObject,
and output them in a drop down box
where you can select the appropriate one. When a database has been selected and the form submitted, a second
function will open MS Access, and produce a list of reports for that database. Here's the code, which is
essential VBA code altered for VBScript:
Here's what's going on. First we need to grab the database name, then create the instance of access. Line 3
sets Access to invisible, (I don't know about you, but I have thus far avoided the temptation to find out what
exactly would happen were I to set it to True, though I suspect the outcome is not all that exciting), and
Line 4 opens our selected database. Note that we need the full path to the database, hence the
Server.MapPath. Line 5 simply assigns objDB to the current database; this is not necessary, but
it means we don't need to type
objAccess.CurrentDb.etc.etc for all the commands, and we get a
slight improvement in performance. Line 6 gets a count on the number of reports. If you are wondering what the
'containers' object is, it contains information on Access objects. You can use it with Forms, Tables, Modules,
etc. Moreover, the container object allows you to access these objects in a similar fashion to ADO, something all
of us are familiar with. In this case, we are accessing the container for reports, and getting a count of
all the saved documents.
After getting the count we run a
FOR loop to output the results into another select box or, if no
reports were found, write an error message to the browser. Then we need to clean up:
Notice the command to close/quit Access.
acQuitSaveNone allows us to quit without saving, which
is important because it insures that Access will not try to prompt a dialog box asking if it should save.
This is an intrinsic constant for Access VBA, similar to ADO commands like
adCmdTable. But since
we don't have an
adovbs.inc-like file of all constants like we do for ADO, several such constants
are declared at the top of the page.
After the choice of reports is submitted, we first generate a unique file name for our Snapshot file, grab the name of the database and report from the querystring, then open the database through another instance of Access. Here is the code to open the report and save it to an external file:
DoCmd allows you to script actions & tasks for Access that normally are done through
interaction by the user. The first thing done here on Line 2 is open the appropriate report. We open it in
Preview Mode (
acViewPreview) since we can't make design changes, and Access needs to run the
report so it knows what it should look like. Notice the values
These allow you to set a filter on the report, or specify what you want returned-- similar to a SQL statement.
So if you have a lot of data, you can customize your views. In Line 3, we save the report as a separate
document. Since we want a report we use the report value (
acOutputReport) where we need to
ObjectType, but you can also use the
OutputTo command to save Forms,
Modules, Queries & tables. We then specify the format, and on line 4 give the
location we want the file saved (in this case a folder called
snapfiles), and the file name.
Finally, now that we have our Snapfile, we write out the HTML with the ActiveX control to the client's browser, and all we need to do is insert the file name into the appropriate parameter:
In addition, by right-clicking in the report area, you can zoom into the document as well as print it, and for multi-page reports there are navigation buttons at the bottom. So there you go. A few changes to the server and a few lines of code later you're in business. Although the applications for this process are probably limited to Intranet use, it can nonetheless help in the distribution of documents. You could even customize the script so that a report is generated by one person with an unchanging file name, and have an html page that opens uses that file. That way the report is available to everyone, but there is no waste of resources and hard disk space by creating duplicate reports.
Well, we've covered a lot of space in this article, but even if this particular application isn't useful to you, hopefully this article has broadened you vision of what you can do with ASP and VBA, and what some of what lies under the hood of IIS.
|An Important Update...|
Avid 4Guys reader Tom Travers has found a small error
with Richard's article when using Windows 2000. He discusses this error, its resolution, and
an interesting enhancement in his update article.|
[Read UPDATE: Viewing and Printing Access Reports with ASP and/or VisualBasic (Windows 2000 Fix)]