Saving Reports is a Snap

<< Click to Display Table of Contents >>

Navigation:  »No topics above this level«

Saving Reports is a Snap

200112_GR3 Garry Robinson        

In this article, Garry Robinson presents a routine that will allow you to save Access reports directly into Snapshot format.

This year in Australia, a new tax law was passed. Guess what! Invoice reports for most companies had to be changed significantly. This left software for two of our small business clients (plus our own billing system) in a situation where the invoices would have to be split into old and new versions. The solution that we came up with was to save all of the old invoices "as is" using Access's Snapshot format. That way, when a client rang up to inquire about an invoice, we could check it out, just using Windows Explorer.

As you might guess, the process of running the Access report and doing a manual Save As (see Figure 1) became tedious very soon—as did using Windows Explorer. Since removing repetition is the role of the programmer, I developed a generalized solution for saving Access reports directly into Snapshot format. You can add this routine to any Access report. This article describes the code that you need to add to your reports and forms to switch from on-screen display to hard-copy Snapshot reports.

Figure 1

What's a Snapshot?

A Snapshot is defined by Microsoft as "a portable Microsoft Access report." A report Snapshot file should have a file extension of *.snp. A Snapshot should always have the exact same layout and look as the report does when it's in preview mode. This exact-copy feature (see Figure 2) sets it apart from the other ways that you can save a report such as transfer to Rich Text Format (Word 95), HTML, and Excel where the report can look vastly different from its original format.

Figure 2

Naturally, once you save the report as a Snapshot, you need to be able view the file. To view the file, you'll use the Access Snapshot Viewer. This viewer is free and can be downloaded from the Microsoft Web site (see the sidebar, "Further Reading and Resources"). You can think of the viewer and additional saving functionality as being a highly specialized alternative to the Adobe Acrobat PDF format technology, available from Adobe ( The Adobe Reader is free, which is the reason the format is so popular. There's one important difference between Snapshot and Acrobat, though. If you already own Microsoft Access 97 Service Pack 1 or higher, the Snapshot software that you use to save the report is free and easy to install (especially if you own Access 2000). Adobe Acrobat, unfortunately, requires a new license for each PC that you wish to use as a platform to save Access reports to PDF format.

One of the best locations for reading about programming the Snapshots and installing the Snapshot Viewer is at This is also the address where your users can download the Snapshot Viewer. The documentation on this page describes how you can install the Snapshot Viewer from your Access 97 CD. With Access 2000, the Snapshot Viewer is automatically installed as soon as the user opens an .snp file.

As in Figure 2, the Snapshot Viewer has a very simple interface that works in the same way the Report Preview works in Microsoft Access. The only difference is that the Snapshot Viewer will keep a stack of the open Snapshots in memory that you can shuffle between just like ordinary windows. The viewer will also keep a list of previously opened Snapshot files and allow you to send the Snapshot .snp file as an e-mail attachment. What the viewer will definitely not allow you to do is edit the report in design view and/or change the data behind the report.

Relative referencing

Over the past year or two, I've seemed to be spending more and more time with external file formats like Excel, Word, and (now) Snapshot files. I've come to depend on relative referencing of directories and files. Using relative referencing means that I store all of my external files in locations relative to the location of the database. Generally speaking, I use a subdirectory under the main database. If you program everything using an absolute path of your "C:\MyDevelopmentDirPath\" directory and then move your solution to your client's "X:\LiveDirPath\" directory, everything will stop working. Using a relative reference means that your software will still work after you relocate your mdb because the subdirectories are in the same relative location.

You have to be careful about which Access database you use as the basis of your relative referencing. Many developers split their application into a front-end/software database and a back-end/data database. If you use the front-end developer as the basis for the relative path, it's possible that the database could be moved to a local drive rather than kept on a network drive. This would result in external files being stored on local hard drives—not a situation that you want to get into. I store the Snapshot and other files relative to the back-end database, which is more likely to be kept on the network server.

In my sample code, I've stored all of the Snapshots in the "\orders" subdirectory. To test this code, you'll need to create an orders subdirectory under the directory that holds the Northwind.mdb database.

To manage relative referencing, I use three layers of software. First, there's a local function and a constant that I use throughout the database. I use a function tailored for each database to set the relative path so that I have a single point of control should I want to change the location at some time. Here's the code:

Public Const gSnapFileType = ".snp"
Public Function SnapDirPath()
  SnapDirPath = GetLinkedPath_FX("Orders") & "orders"
End Function

The second layer of software finds the pathname of the back-end database. I pull that from the Connect property of one of the linked tables. Passed the name of a table, this function parses the Connect property to find the directory pathname:

Function GetLinkedPath_FX(tableName) As Variant
Dim conPath As Variant
Const linkedID = ";DATABASE="
On Error Resume Next
conPath = CurrentDb.TableDefs(tableName).Connect
If InStr(conPath, linkedID) > 0 Then
  GetLinkedPath_FX = _
        GetDBPath_FX(Mid(conPath, Len(linkedID) + 1))
  GetLinkedPath_FX = Null
End If
End Function

The GetLinkedPath that's the third layer of software is a modified version of some code from Edition 4 of the "Smart Access eXTRA" eNewsletter, provided by Mike Gunderloy. This code has been enhanced to accept an optional parameter for passing a database file path in a string. In this case, the file path is the Connect property of the table definition. A typical example is ;DATABASE=C:\MSOFFICE97\OFFICE\Samples\Northwind.mdb.

The code uses the Mid function to strip out the unnecessary ";DATABASE=" substring and returns the database path of the table in the back-end database:

Function GetDBPath_FX(Optional DbPathStr) As String
Dim strPath As String
Dim intLastSlash As Integer
If IsMissing(DbPathStr) Then
  strPath = CurrentDb.NAME
  strPath = DbPathStr
End If
For intLastSlash = Len(strPath) To 1 Step -1
  If Mid(strPath, intLastSlash, 1) = "\" Then
    Exit For
  End If
Next intLastSlash
GetDBPath_FX = left(strPath, intLastSlash)
End Function

From soft copy to hard copy

The amount of code that you'll need to add to your reports to make them save as Snapshots is really very small. Just put this code under the Activate event of the Report :

Private Sub Report_Activate()
Dim snapFile As String, reportOpen As Boolean
  snapFile = SnapDirPath & Me!OrderID & gSnapFileType
  reportOpen = makeSnapShot_FX(Me.NAME, snapFile)
End Sub

Naturally, there's more to this than is obvious at first glance. The code uses the SnapDirPath to retrieve the relative directory to store the file. Very importantly, you need a unique name for the file (just like you need a unique key to define a record). This unique reference should allow the report to be easily identified in Windows Explorer should you want to open the Snapshot report from a file list.

The actual generation of the Snapshot is handled through a general function called makeSnapShot_FX that can be imported into any Access software database. The first part of the module supports letting the user ask for a replacement report to be generated. The code uses the Visual Basic Dir function to test that the file actually exists. If it does, the user is given the option of replacing the report. You may wish to take more control of this option, either not letting users overwrite reports or automatically deleting any existing versions. Here's the first part of that code:

Public Function makeSnapShot_FX( _
  reportName As String, snapFilePath As String) _
  As Boolean
Dim openNow, buttonStyle
buttonStyle = vbYesNo + vbCritical + vbDefaultButton2
If Len(Dir(snapFilePath)) > 0 Then
  openNow = MsgBox("Are you sure that you want " & _
   "to replace the snapshot", buttonStyle, _
   "File already exists")
  openNow = vbYes
End If

The next part of the routine generates the all-important report Snapshot. As you can see, it's all handled with the OutputTo method of the DoCmd object. You'll be familiar with this method if you've ever generated output in RTF, Excel, or text formats. What's different is that you'll need to use the Snapshot format constant to export the file to a Snapshot. It's not the usual Integer constant that you might be used to, but it works. Also notice that I close the actual report before I open it in preview mode, immediately after the Snapshot is created:

If openNow = vbYes Then
  DoCmd.OutputTo acOutputReport, reportName, _
   "Snapshot Format", snapFilePath, True
  DoCmd.CLOSE acReport, reportName
  makeSnapShot_FX = True

The final section of the function supports letting the user send a report to a Snapshot that already exists. The text prompts that I've used cater to power users more than the naïve user. The user can choose to view the existing Snapshot, view the actual report, or just cancel. Before adopting this approach, consider whether your users need all of these options. Should they even be allowed to replace existing reports? Previewing the document is handled by not doing anything as the report is about to become visible anyway:

  makeSnapShot_FX = False
  buttonStyle = vbYesNoCancel + vbInformation
  openNow = MsgBox("Would you like to see the" & _
   " report as a snapshot document  {Yes} " & vbCrLf
   & vbCrLf & "OR   preview the Access Report  {No}",
   buttonStyle, "Open or Preview the Document")
  Select Case openNow
    Case vbYes
      Application.FollowHyperlink snapFilePath
      DoCmd.CLOSE acReport, reportName
    Case vbCancel
      DoCmd.CLOSE acReport, reportName
  End Select
End If
End Function

The really neat bit of code that I've used here is the "followHyperlink" method of the application object that opens the resulting Snapshot report. This is the method that I now use to open any external file because it opens the file using the program that's associated with that file type (just like double-clicking on the file in Windows Explorer). A great example of this is text files that now open in your favorite editor rather than in Notepad.

The user interface

The following code is the simplest that you're ever going to read in Smart Access. The code is behind the two buttons at the bottom of the form in Figure 3. The report is opened with this code (and no tricks except for the Order filter):

DoCmd.OpenReport "InvoiceSnapshot", acPreview, ,  _
  "[OrderID]=" & [OrderID]

Figure 3

The Preview Existing Order command button actually has no code at all under the button. Using the hyperlink property of the command button means that the hard-coded Invoice field for the current record will be opened. The following code under the Current event of the form does all of the work:

cmdViewSnapshot.HyperlinkAddress =  _
  SnapDirPath & Me!OrderID & gSnapFileType

The demonstration database

As my clients and my own billing databases were a bit too specific for general distribution, for the sample database in the accompanying Download file, I decided to add the Snapshot reporting software to the Northwind Order Entry Database that comes with the Access 97 Samples. You can use both Access 97 and 2000 formats in this month's Download file. Figure 3 shows the Orders form that I've amended to now use Snapshots rather than ordinary Access reports. When the form opens, it displays instructions so that you can produce your first Snapshot without having to learn too much about the application. These instructions are provided by using the Balloon class object that I covered in my article, "Balloons: More Than Just Hot Air," in the April issue of Smart Access. The sample databases will automatically link to the Northwind database.

There's a lot of other code under the form and in the database that was included in the initial Microsoft sample application. You won't need this to add a Snapshot report to your application. To find my code, open one of the code modules and search for "Garry Robinson."

I hope this example of Snapshot reporting helps you in the search for the paperless office that's often been promised and rarely delivered.

Your download file is called in the file

This can be purchased with all the other downloads on this page