Viewing and Printing Access Reports with ASP and/or VisualBasicBy Richard Chisholm
|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)]
(While the main focus of this article will be viewing Access reports, there are a couple of pieces of information that anyone who wants to access Office applications through asp or VB-COM will find useful.)
On a company intranet, often times people not authorized to access the web server are responsible for adding content through databases such as Microsoft Access. However, the issue of security is always prevalent. An application like the Online Database Editor in my previous article allows you to add/edit/delete information from any database on a site, but does not allow for the use of a very important part of Access: Reports. Access Reports are highly customizable, allowing you to display exactly the data you want, and often provide a cleaner interface than an HTML page. They are also ideal for producing printable documents intended for distribution. But if you don't allow just anyone to access the intranet's databases, using reports can either be impractical if not impossible.
This solution is fairly straightforward and requires surprisingly little coding, as it is mostly a combination of server settings and VBA code adapted for a web environment. And although there is nothing overly complicated in and of itself, piecing together the scattered bits of information can prove very difficult. In fact, a lot of what I will talk about here was discovered in articles & books that are unrelated to ASP.
Here is what printing reports involves:
- Change 3 settings on the server
- Have MS Access on your server
- Download SnapShot Viewer from Microsoft (http://support.microsoft.com/support/kb/articles/Q175/2/74.ASP)
(SnapShot Viewer is an Application that allows you to view Access Reports without having Access installed on
your machine. We will be using it as an ActiveX plug-in for the browser)
- Permissions on the database will need to be set so that the
iusr_MachineNameaccount can access it.
Here are the configurations I tested the application with:
NT Workstation + PWS, Access97
NT Server 4 (SP5) + IIS 4, Access97
On the client side: IE4, Snapshot Viewer 9.0
If you test this on both NTS & NTWS you will notice a few differences. For example, with PWS you may not need
to worry about permissions to launch MS Access. Additionally, the included source code at the end of the
article has two folders, named
com (you'll have to provide your own
database & reports, though you can use Northwind). The
script folder contains a single ASP file,
which has all the code to create and display the report. The
com folder contains an ASP file, a
doc file, the VB Project files, and a compiled dll. I included both the dll and the source so that you can
look at the source if you have Visual Basic. The dll contains all the code needed to interact with Access, while
the ASP file loads the dll and outputs the content. The documentation is for those of you who don't have a
copy of VB but still want to use the component. In this article I will discuss using the
version, but the code is exactly the same in both so you won't have a problem switching between the two.
Before you can do anything however, you need to change the settings. First up, you need to configure IIS to launch applications. Otherwise you will get this error:
This error will occur whenever you try to access an
.exe file using
since by default IIS can only use dll's (NOTE: In IIS 5.0 launching
.exe's is allowed by default).
To fix this you need to make a change to the IIS metabase by running a script as given in the KB article
I have combined this script with one that will pull out your NT username and force authentication, and included
it below (
meta.asp). If you run the script from the server when logged on as the administrator or
with an account with admin privileges you should have no problem, and you will only need to run it once. If
this way doesn't appeal to you, you can also do it from the command prompt.
The second error would occur when you try and create an instance of MS Access, and manifests itself with an
"Access Denied" or similar message. This error may or may not occur depending on your platform. For NT
Server you will get this, but you may not with PWS. The reason? By default the
iusr* account does
not have access to applications, so you need to set it. To do this do Start-->Run-->"dcomcnfg", then refer to
the Knowledge Base article http://support.microsoft.com/support/kb/articles/Q198/4/32.ASP
The last hurdle you must clear is setting a default printer, because otherwise you will not be able to generate a report in Access, and get an error like this when you run this asp app:
You will get this error even if there is a default printer assigned to the computer, because IIS runs under the System account, which is not automatically assigned one. So you need to edit the registry in order to assign one. Check out the Knowledge Base article on this subject to find out how http://support.microsoft.com/support/kb/articles/Q184/2/91.asp. Also, make sure you have a default printer assigned to the computer before changing the registry.
Now that we've got that part out of the way, on to the good stuff! Part 2 looks at how, exactly, to view the Access reports through an ASP page!