UPDATE: Viewing and Printing Access Reports with ASP and/or VisualBasic (Windows 2000 Fix)By Tom Travers
When using Richard Chisholm's technique for viewing Microsoft Access
reports via a Web browser, I noticed a problem
on my Windows 2000 server. Each time a report is generated and viewed, an
msaccess.exe is spawned as a separate process on the server.
The code in the ASP page that is supposed to clean up the objects was not
working. I discovered that the Microsoft Access Application must be set to
run with the Interactive user, rather than the Launching user. To set this
property in the Distributed COM Configurations property for the
application, perform the following steps:
Start by clicking Start, Run.
In the Open dialog, type:
dcomcnfg and click OK. A window will appear
similar to the one below.
Select the Microsoft Access Application and click the Properties button. The properties window will appear. Click the Identity tab in that window (see the screenshot below), and select the Interactive user, then click the Apply and OK buttons.
Click OK to close the dcomcnfg window as well.
msaccess.exe processes should now be disposed of properly.
I also have an enhancement to the technique that others might find useful. Richard's approach uses a unique filename for each snapshot, based on time of day. While this ensures that files won't be trampling on one another, they can also take up a lot of space in the long term. In my case, I have a set of reports that are viewed by clients of a real estate database.
I gave each client a username and password in a database table. Users first login, using a login.asp page that checks their username and password. If they are legit, a session variable is set with their username. When they try to view a report, that session variable is checked, and if it is empty or expired, they are redirected back to login.asp.
The username serves two functions in my case. First, it is used to create a modified where clause for the report, to let the client only see records they should see (the username table also has an identifier matching those in the real estate table, which I use in the query). Simple, but effective security.
Second, I use the FileSystemObject to see if a folder with the name of the
user exists under the
\snapfiles\ folder. If it does not exist (such as a
new client accessing the first time), I create it.
There are six or eight different reports in my database that are accessed by clients. Each one uses a different snapfile name in each client's folder. These files are uses over and over, being overwritten with each report request. This reduces the number of files to six or eight for each client. There does exist a slight possibility that more than one person using the client's username and password could try to access the same report at the same time, but the chances are slim.
The rest is not changed significantly from Richard's code. I do a preliminary query for a recordset first, matching that of the report just to see if it is empty. If it is empty I use alternate code instead of showing the report, because a report with no records has those ugly Error# tags in it.