Microsoft Access as a report tool

In this example, I use a Visual Basic 5 front end to interrogate a user-selected Microsoft Access database for all the reports it contains. A user can then select a report by name and filter it according to the criteria selected on screen. (See Figure 3-8.) The result is viewable on screen in Print Preview mode or is printable to paper.

The starting point for this example is in the Company Stock.mdb database, where a query is designed to support the example’s report requirement. The qryUniverseOfCompanies query is created with all possible fields available for report use. No filtering is performed within the query because we will use the WHERE clause option of OpenReport. (This ensures that the user has total flexibility over what data will be returned in the report.) Two macros are also created: OpenReport, which supports the alternative method of opening Access 2 MDB files where no Access VBA object model is available for communication with Access; and CloseReport, which exits the Access application when the user closes the report.

Figure 3-8 Report selection utilizing the Microsoft Access report writer

The OpenReport and CloseReport macros contain the following functions and commands:

• OpenReport: Runcode macro command with the Function Name argument set to CustomOpenReport, a customized function that reads the command line to determine what parameters the calling program has passed to Access. The parameters for this command include all the parameters used in the OpenReport method in VBA.
  
  
• CloseReport: Quit macro command with default settings.

The rptCompanyUniverse report is then designed with two key properties set: Record Source set to qryUniverseOfCompanies, and On Close set to the macro CloseReport. These settings ensure that the right data is being used with the report and that the Access application will terminate when the user quits the report. This feature is important if you consider the problems that can occur if the user has free rein over the database window, which in this example is hidden behind the report.

Now we are left with the task of providing the user with the tools to gain access to this report. Two components are included in ReportsInAccess.vbp: the module modReportsInAccess, which loads the main form on start-up; and the main form frmReportsInAccess, which provides the user with an interface for reporting. Within frmReportsInAccess, a private variable is defined to hold the database filename in which the report resides:

Private fm_sDatabaseFileName As String

In the cmdOpenDatabase_Click procedure, we determine what reports are available in the Access database. Two points are critical:

1. The Reports collection provides details only on open reports.
   
   
2. The document container is unsupported in Access VBA for other applications.

Because of these two points, it is impossible to determine what reports are available to access the system MSysObjects table held within the Access database. This table provides details of all objects within an Access database, from tables to reports. Reports are identified by the Type data column having the value -32764. (To view systems tables with Access, you need to select Options from the Tools menu and enable the View System Objects option. Here is the code in the cmdOpenDatabase_Click routine to retrieve the report names into a list box:

' Define the variables required to access the database.
Dim ws As Workspace
Dim db As Database
Dim rs As Recordset

' Find the database the user wants.
    dlg.Filter = "Database (*.mdb)|*.mdb"
    dlg.InitDir = App.Path
    dlg.ShowOpen
    If dlg.filename & "" <> "" Then
        fm_sDatabaseFileName = dlg.filename
    End If
    txtDatabase = fm_sDatabaseFileName

    Me.MousePointer = vbHourglass

    ' Make sure that we have a filename to work with.
    If txtDatabase <> "" Then
        lstReports.Clear

        ' Open a Jet workspace, and get the database.
        Set ws = CreateWorkspace("", "Admin", "", dbUseJet)
        Set db = ws.OpenDatabase(fm_sDatabaseFileName)

        ' Read the system table to find all reports.
        Set rs = _
        db.OpenRecordset _
        ("Select Name From MSysObjects " & _
            "Where Type = -32764 Order By Name", _
            dbOpenSnapshot, dbReadOnly)

        ' Load any reports into the list box.
        If rs.RecordCount > 0 Then
            rs.MoveFirst
            Do Until rs.EOF
                lstReports.AddItem rs!Name
                rs.MoveNext
            Loop
            lstReports.ListIndex = 0
        End If

        ' Close all objects.
        rs.Close
        Set rs = Nothing
        db.Close
        Set db = Nothing
        ws.Close
        Set ws = Nothing
    End If

    Me.MousePointer = vbDefault

The code used to activate the report is split into two different methods: the Access 2 method, which fires a Shell command to load Access and execute the OpenReport macro created earlier to display the report; and the Access VBA method, which uses the DoCmd.OpenReport method to display the report. The code to place a report into Print Preview follows:

Private Sub cmdPreview_Click()

' Create an Access object.
Dim objAccess As Object

    Me.MousePointer = vbHourglass

    ' Make sure a report is selected.
    If lstReports.ListIndex <> -1 Then

        ' Which Access method has been selected?
        Select Case optAccessVersion(0).Value
            Case True 'Access 2.0
                ' Provide the parameters in string format
                ' delimited by |.
                ' /X executes an Access macro.
                ' /Cmd provides additional string data on the
                ' command line.
                Shell "Msaccess.exe " & fm_sDatabaseFileName _
                    & " /X OpenReport /Cmd " & lstReports & _
                    "|2|" & sBuildWhereClause, vbMaximizedFocus

            Case False ' Access 7/97

                ' Get the Access database application object.
                Set objAccess = _
                    GetObject(fm_sDatabaseFileName)

                ' With the application object, open the report
                ' and maximize it within Access's MDI parent 
                ' window.
                  With objAccess.Application
                     .DoCmd.OpenReport lstReports, acPreview, _
                         , sBuildWhereClause

                     .DoCmd.Maximize
                 End With
                 ' Close the object.
                 Set objAccess = Nothing
        End Select
    End If

    Me.MousePointer = vbDefault

This example provides a simple solution for accessing Access reports from within other component applications. Given the drawbacks mentioned earlier, though, I don’t recommend that you attempt to provide too much reporting to your users via the Access report writer.