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

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

Viewing and Printing Access Reports with ASP and/or VisualBasic, Part 2

By Richard Chisholm


  • Read Part 1

  • In Part 1 we looked at potential errors one could encounter when preparing to view Access reports through a Web page. Ugh. Now that we've got that part out of the way, on to the good stuff. The GetReports app will first find all the databases in the local directory using the FileSystemObject, and output them in a drop down box where you can select the appropriate one. When a database has been selected and the form submitted, a second function will open MS Access, and produce a list of reports for that database. Here's the code, which is essential VBA code altered for VBScript:

    - continued -

    <% 'after declaring the variables.... Line1: strDbName = Request.Form("db") 'Gets the database name Line2: Set objAccess = Server.CreateObject("Access.Application") Line3: objAccess.Visible = False Line4: objAccess.OpenCurrentDatabase Server.MapPath(strDbName) Line5: Set objDB = objAccess.CurrentDb() 'for simplicity's sake Line6: strReportCount = objDB.containers("Reports").Documents.Count %>

    Here's what's going on. First we need to grab the database name, then create the instance of access. Line 3 sets Access to invisible, (I don't know about you, but I have thus far avoided the temptation to find out what exactly would happen were I to set it to True, though I suspect the outcome is not all that exciting), and Line 4 opens our selected database. Note that we need the full path to the database, hence the Server.MapPath. Line 5 simply assigns objDB to the current database; this is not necessary, but it means we don't need to type objAccess.CurrentDb.etc.etc for all the commands, and we get a slight improvement in performance. Line 6 gets a count on the number of reports. If you are wondering what the 'containers' object is, it contains information on Access objects. You can use it with Forms, Tables, Modules, etc. Moreover, the container object allows you to access these objects in a similar fashion to ADO, something all of us are familiar with. In this case, we are accessing the container for reports, and getting a count of all the saved documents.

    <%
    If strReportCount > 0 Then
      '--Write out beginning of HTML form here--
      For Each objRprtDoc in strDB.Containers("Reports").Documents
        Response.Write("<option value='" & objRprtDoc.Name & _
                       "'>" & objRprtDoc.Name & "</option>" & vbCrLf)
      Next
      '--Write the end of the form here --
    Else 
      'Write Error Message
    End if
    %>
    

    After getting the count we run a FOR loop to output the results into another select box or, if no reports were found, write an error message to the browser. Then we need to clean up:

    <%
      set objDB = nothing
      objAccess.Quit acQuitSaveNone
      Set objAccess = Nothing
    %>
    

    Notice the command to close/quit Access. acQuitSaveNone allows us to quit without saving, which is important because it insures that Access will not try to prompt a dialog box asking if it should save. This is an intrinsic constant for Access VBA, similar to ADO commands like adCmdTable. But since we don't have an adovbs.inc-like file of all constants like we do for ADO, several such constants are declared at the top of the page.

    After the choice of reports is submitted, we first generate a unique file name for our Snapshot file, grab the name of the database and report from the querystring, then open the database through another instance of Access. Here is the code to open the report and save it to an external file:

    <%
    Line 1: With objAccess.DoCmd
    Line 2:  .OpenReport strRptName, acViewPreview, strFilter, strWhere
    Line 3:  .OutputTo acOutputReport, , "Snapshot Format", _
                       Server.MapPath(".") & "\snapfiles\" & strSnapFile
    Line 4:  .Close
    Line 5: End With
    %>
    

    Basically, the DoCmd allows you to script actions & tasks for Access that normally are done through interaction by the user. The first thing done here on Line 2 is open the appropriate report. We open it in Preview Mode (acViewPreview) since we can't make design changes, and Access needs to run the report so it knows what it should look like. Notice the values strFilter and strWhere. These allow you to set a filter on the report, or specify what you want returned-- similar to a SQL statement. So if you have a lot of data, you can customize your views. In Line 3, we save the report as a separate document. Since we want a report we use the report value (acOutputReport) where we need to specify the ObjectType, but you can also use the OutputTo command to save Forms, Modules, Queries & tables. We then specify the format, and on line 4 give the location we want the file saved (in this case a folder called snapfiles), and the file name.

    Finally, now that we have our Snapfile, we write out the HTML with the ActiveX control to the client's browser, and all we need to do is insert the file name into the appropriate parameter:

    <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="snapfiles/<%strSnapFile%>">
      <PARAM name="Zoom" VALUE="0">
      <PARAM name="AllowContextMenu" VALUE="-1">
      <PARAM name="ShowNavigationButtons" VALUE="-1">
    </OBJECT>
    

    In addition, by right-clicking in the report area, you can zoom into the document as well as print it, and for multi-page reports there are navigation buttons at the bottom. So there you go. A few changes to the server and a few lines of code later you're in business. Although the applications for this process are probably limited to Intranet use, it can nonetheless help in the distribution of documents. You could even customize the script so that a report is generated by one person with an unchanging file name, and have an html page that opens uses that file. That way the report is available to everyone, but there is no waste of resources and hard disk space by creating duplicate reports.

    Well, we've covered a lot of space in this article, but even if this particular application isn't useful to you, hopefully this article has broadened you vision of what you can do with ASP and VBA, and what some of what lies under the hood of IIS.

    Happy Programming!

    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)]

  • By Richard Chisholm
  • Read Part 1


    Attachments:

  • Download the files in ZIP format


    Richard Chisholm is the Webmaster of a large California law firm, as well as an independent developer. He runs a website dedicated to blues music in his spare time.


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