Create Access Reports for the Web

by Steve Harshbarger

Reprinted with permission from Visual Basic Programmer's Journal, 4/98, Volume 8, Issue 4, Copyright 1998, Fawcette Technical Publications, Palo Alto, CA, USA. To subscribe, call 1-800-848-5523, 650-833-7100, visit www.vbpj.com, or visit The Development Exchange

Build an Access report server for the Web using Visual Basic, Active Server Pages, and the Access Snapshot Viewer.

The Web has revolutionized your ability to access and divulge information, but at a cost. One significant cost: Web development typically requires writing a lot of code to display database information in a browser. Another: it's difficult, if not impossible, to give your Web-reported data the appearance of professional quality.

In the best of all possible worlds, you'd be able to view and distribute reports created by the tools you already use. For example, Access developers have long wished for a way to distribute and view Access reports over the Web with no loss of fidelity. This ability would mean a huge productivity boost for the developer, who could spend his time creating and maintaining critical information rather than coming up with ways to display information that a report writer could do far better. The end user would also benefit because reports would appear in their original formats, minus a time-consuming and inferior kludge.

Fortunately, Microsoft granted this wish with a recently released Access. The Access Snapshot Viewer simplifies creating and presenting reports over the Web. It lets you save any Access report as a small file that you can distribute independently of a database. You can view and print this file from either a standalone EXE or directly from the browser using an ActiveX control. The small, royalty-free Snapshot Viewer is analogous to the Excel, Word, and PowerPoint viewers that have been available for some time. Client machines don't need Access installed, nor do they need a direct connection to the database. In fact, all the client needs is a Web browser that can host ActiveX controls (read: Internet Explorer). In short, it's a great approach to report writing for developers who consider Access a formidable reporting tool.

You'll need to download the Snapshot Viewer before you can do any of this (see the sidebar, "How to Get the Access Snapshot Viewer"). Microsoft released the Snapshot Viewer with Office 97 Service Release 1, a collection of bug fixes and minor new features. You'll need to install both Service Release 1 and the SnapshotViewer kit to implement the sample application described in this article.

The sample app itself, ReportServer, requires that you make a number of pieces work together. For example, the bulk of the code exists in the form of a VB EXE and an Active Server Page (ASP). But you'll also need to throw in a dab of HTML, as well as an Access database with rudimentary tables, queries, and reports.

How to Get the Access Snapshot Viewer

Microsoft implemented the snapshot functionality with Microsoft Office 97 Service Release 1. You'll need to install two things to implement the sample application: Service Release 1 and the Snapshot Viewer kit. Service Release 1 gives Access the ability to save reports as snapshots. If you own Office 97, you can get this free patch at http://www.microsoft.com/Office/Office97/ServiceRelease. You also need to install the Snapshot Viewer kit, which includes the standalone viewer, the snapshot OCX, and developer help files. You can find this in one of two places: in \Valupack\Access\Snpvw80.exe of the Service Release 1 CD, or at http://www.microsoft.com/accessdev/prodinfo/snapshot.htm.

After you install the kit, be sure to check out its help file, SnapView.hlp, for detailed information about the product and how to program it. You can also find a white paper with additional information on programming the Snapshot Viewer on Microsoft's Web site at http://www.microsoft.com/accessdev/articles/snapshot.htm.

Take stock of the app's components

I'll give you a quick run-through of the process for creating the ReportServer app before I go into the code (see Figure 1). In the sample app, the user starts the process by requesting a report through an HTML page that he views in his browser. This form gathers three key pieces of information: the name of the report to run, the name of the Access database containing it, and an optional WHERE clause to filter the content of the report (see Figure 2). These parameters are passed to an ASP page that executes on the Web server. At this point, the ASP page writes the report request and parameters to a report queue. The queue takes the form of a simple Access MDB file and exists to facilitate report requests from multiple, simultaneous users. The ASP page then waits for the report to clear the queue before continuing.

Figure 1: Create Reports Over the Web. Building a Web-based, Access report server consists of combining several components in the appropriate manner. For example, you need to hook up a VB report server, Active Server Pages, HTML pages, and an Access database. This chart shows you an outline of the overall process. Note that the majority of the work here takes place on the server rather than the client.

Figure 2: Request a Report. This page lets you select a report to run and specify a filter. You implement it with HTML and a form that gathers three key pieces of information: the name of the report to run, the name of the Access database containing the report, and an optional WHERE clause to filter the content of the report.

At the same time, a VB EXE report server application continuously monitors the queue for incoming report requests. Whenever a report request appears in the queue, the VB app directs a server-based instance of Microsoft Access to run the report through Automation. The report is then saved as a snapshot (SNP) file into a directory on the server. The report is then marked as complete and the name of the generated snapshot file is noted in the queue. This approach funnels report requests from many users through a single queue and one instance of Access. This prevents a potentially bad situation where each user spawns his or her own copy of Access on the server. This would seriously impede performance if there were more than a handful of simultaneous users.

After the ASP page determines the report is complete—remember that it is checking continuously—it generates an HTML page that the client can view with an embedded Snapshot Viewer control. To view the file, part of the code generated by the ASP page is a parameter that indicates the name of the snapshot file. Finally, the snapshot control reads this file from the Web server and displays it within the browser. From here, the user can view, zoom, navigate through, and print the report as required (see Figure 3).

You'll need to place all the files for this application in the same directory. This prevents any path dependence and makes it easier for you to work with the sample files (see Table 1 for a complete list of the application files).

Request A Report

The user requests a report through an HTML page called ReportRequest.htm. You can find the code for this page on the free, Registered Level of The Development Exchange (see the Code Online box for more details). Create this kind of custom page for any report you want to make available to the application. Use a standard HTML form to gather the parameters for the report:

<FORM ACTION="ProcessReport.asp" 
METHOD="GET" NAME="Parameters">

The ACTION attribute causes the browser to call the page ProcessReport.asp when the form is submitted. The METHOD attribute determines whether the form sends the parameters through a GET or POST. I chose GET because it appends the parameters onto the URL of the page being called, which is convenient when testing because it allows you to see exactly what is sent to the server. POST, on the other hand, hides the parameters from view in the URL. This would work just as well in the ReportServer app, but you wouldn't get to see exactly what is sent to the server.

You need to create three fields for the form: a DatabaseName field, a ReportName field, and a WhereClause field. Create the DatabaseName field first, and make it a hidden field. This field indicates which Access database contains the report the page wants returned in its Value attribute. You hide this field because it contains nothing the user needs to see:

<INPUT TYPE="hidden" 
 NAME="DatabaseName" 
 VALUE="Reports.mdb">

The second field, ReportName, contains a pair of radio buttons that allow the user to choose between two reports. The VALUE of this field indicates the exact name of the report to run:

<INPUT TYPE="radio"
 CHECKED
 NAME="ReportName"
 VALUE="Catalog">
 Product Catalog</P>
<P>
<INPUT TYPE="radio"
 NAME="ReportName"
 VALUE="Products by Category">
 Product Listing by Category
</P>

Finally, create a WhereClause field, which displays a list box that allows the user to narrow down the report to a single category of data. The VALUE of each item in the list is a SQL WHERE clause, with the word WHERE omitted because Access doesn't require the word "WHERE" in its syntax for filtering reports:

<SELECT NAME="WhereClause"
SIZE="1">
   <OPTION selected VALUE="">
   (all)</OPTION>
   <OPTION VALUE="CategoryName = 
   'Beverages'">
    Beverages</OPTION>

You could also use ASP to enhance this page by filling the list of categories directly from the database, but I'll leave that as an extra-credit assignment. When the user clicks on the Submit button, ProcessReport.asp is invoked on the server and the three fields are passed as parameters in the URL.

Figure 3: View a Report. The Snapshot Viewer control displays and prints Access reports directly within the browser. The ASP page generates an HTML page that the client can view with an embedded Snapshot Viewer control. The snapshot control reads the file, then displays and prints Access reports directly within the browser.

queUE the Request

You now have a form for the user to submit report requests. Next you need to write an ASP page that adds the report request to the queue (see Listing 1). The queue itself is an Access database called ReportQueue.mdb. It contains one table, ReportQueue, and includes eight fields: Sequence (a unique ID for each report request); DatabaseName, ReportName, and WhereClause (the three parameters from RequestReport.htm); Complete (a Boolean flag to indicate when the report has been produced); SnapshotFile (the file name of the generated report), and ErrorMessage (a place to store error information from the server). You expose the queue through an ODBC data source called ReportQueue.

Opening and writing to the queue is straightforward. Simply use Active Data Objects (ADO) to open a recordset on the ReportQueue table and add a new record to it. The difficult part is determining the values of the three parameters passed in from RequestReport.htm. ASP provides a simple means to get parameters passed to a page through a form with its intrinsic Request object. This object represents information coming from the user. The object's Form property is a collection of fields from the HTML form indexed by their names. You get the value of a particular field with this syntax:

Request.Form("ReportName")

Note that you can omit ".Form" because Form is the default property of Request, as in this code that adds a report request to the queue:

rsQueue.AddNew
rsQueue("DatabaseName") = _
   Request("DatabaseName")
rsQueue("ReportName") = _
   Request("ReportName")
rsQueue("WhereClause") = _
   Request("WhereClause")
rsQueue.Update()
ReportID = rsQueue("Sequence")
rsQueue.Close()

The ASP page now must wait until the report is produced before continuing. You can write a loop that checks continuously until the request's Complete Flag becomes True. The sample app includes code that continuously requeries a recordset to accomplish this task:

rsQueue.Open _
   "Select * From ReportQueue " & _
   "Where Sequence=" _
   & ReportID & " and Complete=True"
Do Until rsQueue.RecordCount > 0
   rsQueue.Requery
Loop

A better implementation would wait for some brief period of time between requeries to keep from monopolizing the computer's resources. This isn't practical to do with ASP code, however, because of ASP's inability to call the Windows API. A great solution would be to write a small ActiveX DLL in VB that uses the Sleep API call to implement the pause between the requeries. Give the DLL an Automation interface to monitor the queue, and then invoke your ASP code as would for any Automation server. You can see an example of how to use the Sleep API in the VB application that monitors the queue for new report requests.

Table 1

FileDescription

RequestReport.htmHTML page to request a report

ProcessReport.aspActive Server Page to process report requests

ReportQueue.mdbAccess database implementing a report queue

ReportServer.exeVisual Basic executable that monitors the queue and runs reports

SnapView.ocxThe Snapshot Viewer ActiveX control

Reports.mdbAccess database containing the reports that can be run

Main.frmSource file for ReportServer.exe

Main.frxSource file for ReportServer.exe

ReportQueue.basSource file for ReportServer.exe

AccessReport.clsSource file for ReportServer.exe

ReportServer.vbpProject file for ReportServer.exe

Table 1: Examine the Application Files. This table shows you all the files associated with the ReportServer at a glance. Note that all these files must reside in the same directory on your Web server, or this solution will not work.

Build The REPORT SERVER

The HTML and ASP code for the app is out of the way, and you're ready for the fun stuff—writing the VB portion of the application. The purpose of ReportServer.exe is to run reports in the queue and then mark each as completed. It controls Microsoft Access through Automation to save the reports as snapshot files on the server. The ReportServer also writes the name of each file back to the queue so it can be displayed to the user. The app is designed to run unattended on a server, so the code here logs errors it encounters to the queue and keeps on running. You don't want this application to show a message box if it can't find a requested report: if it did, it would stop generating other reports. You might wonder why you should implement the ReportServer in VB at all, rather than simply using Access. This approach would work for the simple case described here, but it would not be extensible. A separate report server architecture lets you extend ReportServer to manage multiple instances of Access, which could potentially run on different machines and control through DCOM. Using VB allows you to scale the application to handle large volumes of report requests. It also opens up the possibility of adding support for reports produced in other applications such as Excel and Word. It might help to think of ReportServer as a potential broker that passes requests on to any number of applications that do the work of actually building the reports.

Listing 1 (VBS)

<%@ LANGUAGE="VBSCRIPT" %>
<HTML>
<HEAD>
<TITLE>Access Report Server Demo</TITLE>
</HEAD>
<BODY>
<A HREF="RequestReport.htm">
Request another report</A>
<HR>
<%   '/ Open the report queue
   Set Conn = Server.CreateObject("ADODB.Connection")
   Conn.Open "ReportQueue", "", ""
   Set rsQueue = Server.CreateObject("ADODB.RecordSet")
   rsQueue.Open "ReportQueue", conn, 1, 3

   '/ Write report request to the queue
   rsQueue.AddNew
   rsQueue("DatabaseName") = Request("DatabaseName")
   rsQueue("ReportName") = Request("ReportName")
   rsQueue("WhereClause") = Request("WhereClause")
   rsQueue.Update()
   ReportID = rsQueue("Sequence")
   rsQueue.Close()

   '/ Wait until report is ready
   rsQueue.Open "Select * From ReportQueue " _
      & "Where Sequence=" & ReportID & _
      " and Complete=True"
   Do Until rsQueue.RecordCount > 0
      rsQueue.Requery
   Loop

   '/ Get snapshot filename
   SnapshotFile = rsQueue("SnapshotFile")
   If SnapshotFile <> "" Then%>
<OBJECT ID="SnapshotViewer1"
WIDTH=750 HEIGHT=500 
CODEBASE="Snapview.ocx"
CLASSID="CLSID:F0E42D60-368C-11D0-AD81-00A0C90DC8D9">
   <PARAM NAME="_Version" VALUE="65536">
   <PARAM NAME="SnapshotPath"
   VALUE="<%=PagePath() & SnapshotFile%>">
   <PARAM name="Zoom" VALUE="0">
   <PARAM name="AllowContextMenu" 
   VALUE="-1">
   <PARAM name="ShowNavigationButtons"
   VALUE="-1">
</OBJECT>
<%   Else%>
<P><B>The following error occured while 
 attempting to produce your report:</B></P>
<%Response.Write(rsQueue("ErrorMessage"))
   End If
   rsQueue.Close
   Conn.Close%>
<HR>
</BODY>
</HTML>

<%
'/ Determines http path to snapshot file
Function PagePath()
   s=Request.ServerVariables("SCRIPT_NAME")
   s=Left(s,InstrRev(s,"/"))
   s=Request.ServerVariables("HTTP_HOST") _
      & s
   s="http://" & s
   PagePath=s
End Function
%>

Listing 1: Add Reports to the Queue. This Active Server Page receives requests from the browser and enters them into a report queue. When the report is complete, the ASP page generates HTML to view the Snapshot Viewer control.

ReportServer.exe includes a simple UI to let you start and stop processing (see Figure 4). It also includes a status message area to display what report it is currently working on. Write the code for the UI in frmMain (see Listing 2). The only detail worthy of note is the use of the public variable, pbPollQueue, which you declare in the module, mReportQueue. This Boolean variable controls a loop that polls the queue, and the loop continues to run for as long as this variable's value is True. You can set its value to false in the UI by clicking on the Stop button in the cmdStop_Click event or by closing the form in the Form_Unload event. Either of these actions shuts down the polling process.

Figure 4: Monitor Queue for Reports. The Report Server, written in VB, monitors the queue for report requests and produces report snapshots. The server includes a simple UI that lets you start and stop processing, as well as a message area to display the report that is currently being processed.

Listing 2 (VB5)

Const INITIAL_STATUS_MSG = _
   "Press Start to initialize the report server."

Private Sub cmdStart_Click()
   cmdStart.Enabled = False
   cmdStop.Enabled = True
   lblStatus = "Initializing..."
   DoEvents
   PollReportQueue
End Sub

Private Sub cmdStop_Click()
   pbPollQueue = False
   cmdStart.Enabled = True
   cmdStop.Enabled = False
   lblStatus = INITIAL_STATUS_MSG
End Sub

Private Sub Form_Load()
   cmdStart.Enabled = True
   cmdStop.Enabled = False
   lblStatus = INITIAL_STATUS_MSG
End Sub

Private Sub Form_Unload(Cancel As Integer)
   pbPollQueue = False
End Sub

Listing 2: Create the UI for the Report Server. This code includes buttons to stop and start processing, as well as a status message area that displays the current report being processed. The only detail worthy of note is the use of the public variable, pbPollQueue. This variable controls a loop that controls the queue. The loop continues to run for as long as this variable's value is True.

You conduct the polling itself in the PollReportQueue routine, which you place in the mReportQueue module. You can find this module on the free, Registered Level of The Development Exchange (see the Code Online box for details). This module contains the logic for the polling mechanism.

The module's logic starts executing when you open a recordset containing requests that aren't yet complete. This starts a continuous loop that you can only interrupt from the UI. Note that you must constantly requery the recordset for new records while within the loop. When records exist, loop through them to run the requested reports and mark each queue record as complete. This empties the recordset until new records appear again in the queue. One significant aspect of this code is that it uses DoEvents within both loops. DoEvents causes the processor to yield to other tasks so the user can indeed click on the Stop button and shut down the polling. The UI freezes without this, leaving you with an infinite loop.

You implement the code to run the report itself in a class, cAccessReport (see Listing 3). This class implements five properties: ReportName, DatabaseName, WhereClause, SnapshotFile, and ErrorMessage. The first four properties tell the object which report to run and where to store the output. The ErrorMessage property returns a description of any error that occurs when attempting to a run a report, while the Execute method lets you run a report described by the current values of the properties. It returns True or False to indicate its success or failure, respectively.

Listing 3 (VB5)

Option Explicit

'Module variables to hold property values
Private msReportName As String
Private msDatabaseName As String
Private msLastDatabaseName As String
Private msWhereClause As String
Private msSnapshotFile As String
Private msErrorMessage As String

' Variable for instance of Access
Private mobjAccess As Access.Application

Private Sub Class_Initialize()
' Creates and instance of Access
Set mobjAccess = New Access.Application
   mobjAccess.Visible = False
End Sub

Private Sub Class_Terminate()
' Destroys the instance of Access
On Error Resume Next
   mobjAccess.Quit acQuitSaveNone
   Set mobjAccess = Nothing
End Sub

Public Function Execute() As Boolean
' Runs the report described by the current values of the 
' ReportName, DatabaseName, and WhereClause properties. 
' Saves it to the SnapshotFile
On Error GoTo ErrorHandler
   Execute = False

   ' Get the right database open
   With mobjAccess
      If msLastDatabaseName = "" Then
         ' No database currently open
         .OpenCurrentDatabase App.Path & _
            "\" & msDatabaseName
         msLastDatabaseName = msDatabaseName
      ElseIf msLastDatabaseName <> _
         msDatabaseName Then
            ' Wrong database currently open
         .CloseCurrentDatabase
         .OpenCurrentDatabase App.Path & _
            "\" & msDatabaseName
         msLastDatabaseName = _
         msDatabaseName
      Else
         ' Right database already open, so do nothing
      End If
   End With

   ' Run the report
   With mobjAccess.DoCmd
      ' open report in preview mode so
      ' we can pass in a where clause
      .OpenReport msReportName, _
         acViewPreview, , msWhereClause
      ' save report to snapshot format
      .OutputTo acOutputReport, "", _
         "Snapshot Format", _
         App.Path & "\" & msSnapshotFile
      .Close
   End With
   msErrorMessage = ""
   Execute = True

RoutineExit:
   Exit Function

ErrorHandler:
   ' any error in running report will be
   ' passed back through a property
   msErrorMessage = Err.Description
   Resume RoutineExit

End Function

Public Property Get ErrorMessage() As String
   ErrorMessage = msErrorMessage
End Property

Public Property Get SnapshotFile() As String
   SnapshotFile = msSnapshotFile
End Property

Public Property Let SnapshotFile(ByVal vData As String)
   msSnapshotFile = vData
End Property

Public Property Let WhereClause(ByVal vData As String)
   msWhereClause = vData
End Property

Public Property Get WhereClause() As String
   WhereClause = msWhereClause
End Property

Public Property Let DatabaseName(ByVal vData As String)
   msDatabaseName = vData
End Property

Public Property Get DatabaseName() As String
   DatabaseName = msDatabaseName
End Property

Public Property Let ReportName(ByVal vData As String)
   msReportName = vData
End Property

Public Property Get ReportName() As String
   ReportName = msReportName
End Property

Listing 3: Implement an Access Report Class. The cAccessReport class contains that code that runs the report itself. The class includes several properties that tell the object which report to run and where to store the result, an ErrorMessage property that logs any errors returned, and an EXE method that runs a report described by the current values of the properties.

The cAccessReport class uses Automation to create an instance of Microsoft Access when it initializes. Note that the project must reference the Microsoft Access 8.0 Object Library for this to work. You use this single instance of Access to process all report requests that come through the queue:

Private mobjAccess As Access.Application

Private Sub Class_Initialize()
   Set mobjAccess = New Access.Application
   mobjAccess.Visible = False
End Sub

The Execute method performs two major tasks to run a report. First, it opens the database containing the report. You can make this more efficient by adding code to check whether the correct database is already open. If it is, leave it open. If not, close the current database and open the new one. Access provides two methods, OpenCurrentDatabase and CloseCurrentDatabase, to mimic opening and closing an MDB file. You can find the code for implementing this functionality of the free, Registered Level of The Development Exchange (see the Code Online box for details).

You are now ready to run the report, but the code for this presents a couple problems. For example, I wanted to be sure I could pass a WHERE clause into the report to filter it to make the application more useful. Access provides the OpenReport method, which can take a WHERE clause as a parameter, but this method cannot save the report to snapshot format. The OutputTo method allows you save the report to a snapshot format, but it cannot take the WHERE clause. So, you must first call OpenReport and specify a parameter that displays the report in print preview mode. At this point, you can call the OutputTo method to act on the currently displayed preview by not specifying a report name and save it to a snapshot format. These methods are both provided by the DoCmd object, a catch-all object in Access that provides programmatic equivalents of common commands available in the user interface:

With mobjAccess.DoCmd
   ' open report in preview mode so
   ' we can pass in a where clause
   .OpenReport msReportName, _
      acViewPreview, , msWhereClause
   ' save report to snapshot format
   .OutputTo acOutputReport, "", _
      "Snapshot Format", _
      App.Path & "\" & msSnapshotFile
   .Close
End With

You use the cAccessReport object within the body of PollReportQueue in the mReportQueue module. PollReportQueue reads the report object's properties from out of the queue, then calls the object's Execute method. If this is successful, PollReportQueue marks the record as complete, then saves the snapshot file name in the queue. If this isn't successful, PollReportQueue marks the record as complete, but saves an error message instead. You can find the code for this as well on the free, Registered Level of The Development Exchange.

ProcessReport.ASP REVISItED

The ASP page has been waiting for the report server to produce the report, polling the queue continuously to see whether the requested report has completed. After it receives notification that a report is complete, it can continue. Its sole remaining task is to generate the HTML to display in the client's browser. You must embed the control with the HTML Object tag before you can display the a report with the Snapshot Viewer control:

<OBJECT ID="SnapshotViewer1"
WIDTH=750 HEIGHT=500 
CODEBASE="Snapview.ocx"
CLASSID="CLSID:F0E42D60-368C" & _
   "-11D0-AD81-00A0C90DC8D9">

The SnapshotPath is the most important parameter for the control because it tells SnapshotPath which file to open. This parameter takes the form of an HTTP URL. You construct the URL from the path and file name. The file name is easy—simply read it out of the queue:

SnapshotFile = rsQueue("SnapshotFile")

The path is a bit trickier. For example, you want to avoid hard-coding server or page names in code. Instead, you can use another collection of the Request object: ServerVariables. This collection contains many bits of information about the server and its environment. I've used SCRIPT_NAME to return the current page name, including its path and "HTTP_HOST" to return the server domain name. If you use a bit of string manipulation to strip the page name off the end, and you can determine the entire HTTP path to the snapshot file:

Function PagePath()
   s=Request.ServerVariables("SCRIPT_NAME")
   s=Left(s,InstrRev(s,"/"))
   s=Request.ServerVariables("HTTP_HOST") _
      & s
   s="http://" & s
   PagePath=s
End Function

The ASP page can use these two pieces of information to dynamically generate the value of SnapshotPath:

<PARAM NAME="SnapshotPath"
VALUE="<%=PagePath() & SnapshotFile%>">

When the page kicks down the generated HTML to the client's browser, the viewer control loads the snapshot file directly from the Web server. From there, the user can view it, scroll through it, and print it.

Your users can now request, view, and print Access reports using nothing more than Internet Explorer and the Access Snapshot Viewer. The user needs neither Access nor an open database connection, and this solution supports simultaneous report requests from multiple users. The solution is also extensible (see sidebar, "Extend the ReportServer Application"), but even in its present state it can form the basis of a significant and powerful reporting solution for your company.

Extend the ReportServer Application

There are several ways that you might extend the ideas presented here to create a robust report server. First, you might use ASP pages to make the report request pages data driven. This would enable you to fill pick lists of report parameters from tables in the database or even generate a list of available reports from a metadata table you create on the back end. Rights to view and run individual reports could be managed on a group or user level as well, and the pages could display only the reports a particular individual is allowed to see.

You might also choose to run multiple instances of Access on the back end, on the same or multiple servers to increase ReportServer's performance and scalability. All instances could be controlled by a single report server acting as a broker. This architecture is actually implemented in a major production Web site I've been involved with.

Finally, there's no reason to limit this concept to Microsoft Access. You could use similar architecture to generate Excel, Word, and event PowerPoint based reports from databases. There are viewers available for each of these applications too, so your users wouldn't need Microsoft Office to view the resulting files.

CODE ONLINE

You can find all the code published in this issue of VBPJ on The Development Exchange (DevX) at http://www.windx.com. For details, please see "Get Extra Code in DevX's Premier Club" in Letters to the Editor.

Download the code for this article here