When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Stump the SQL Guru!
XML Info
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
Published: Wednesday, April 26, 2000

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 instance of 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:

- continued -

Start by clicking Start, Run. In the Open dialog, type: dcomcnfg and click OK. A window will appear similar to the one below.

DCOM Configuration Screen

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.

The Properties Screen for the Microsoft Access Application

Click OK to close the dcomcnfg window as well. The 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.


'set the name for the snapshot file
strSNPFileName = "RealEstateReport1.snp"

'set the username for use with the query in the database
'and the path for the snapshot file
strUserName = Session("username")

'instantiate a file system object
Set fso = CreateObject("Scripting.FileSystemObject")

'set the location for this user's files
fldr = Server.MapPath(".") & "\snapfiles\" & strUsername

'check if this location exists, and if not then create it
If Not fso.FolderExists(fldr) Then
end if

'Here we get the snapshot file name.    
strSnapFile = strUsername & "\" & strSNPFileName

'use the same path for URL of snapshot file but with opposite slashes
strSnapURL = Server.URLEncode(Replace(strSnapFile,"\","/"))

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.

Happy Programming!

  • By Tom Travers

    Related Articles:

  • Read Viewing and Printing Access Reports with ASP

  • ASP.NET [1.x] [2.0] | ASPFAQs.com | Advertise | Feedback | Author an Article