New Media Specialist Aquent US-CO-Denver Post A Job | Post A Resume

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:

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

Snippets:

'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 fso.CreateFolder(fldr) 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!

Jupitermedia Corporation has two divisions: Jupiterimages and JupiterOnlineMedia

Jupitermedia Corporate Info

Legal Notices, Licensing, Reprints, Permissions, Privacy Policy.
Advertise | Newsletters | Tech Jobs | Shopping | E-mail Offers