Access Cookbook 2nd Edition-Access Cookbook 2nd Edition

Recipe 17.6 Print an Access Report from .NET Windows Form Application

17.6.1 Problem

The Solution in Recipe 12.2 illustrates how to print an Access report from Excel. Is it also possible to print an Access report from a .NET Windows Form application?

17.6.2 Solution

Printing an Access report from another application requires you to automate the Access application. The Solution in Recipe 12.2 shows how to do this from Excel, which like Access is a COM-based program. The process for automating Access from a .NET application is very similar. The only difference is that a .NET application cannot directly call a COM program (or component). To call a COM-based program from .NET, you must obtain a runtime callable wrapper that calls the COM-based program on your behalf. (This process is the reverse of calling a .NET component from a COM-based program as discussed in the Solution in Recipe 17.1.) Runtime callable wrappers are also known as interop assemblies.

Using the Office 2003 setup program, you can install the interop assemblies for various Office applications, including Access. Depending on the path you take through the Office 2003 setup program, you may or may not have installed the interop assemblies. Fortunately, you can modify an existing Office 2003 installation to add one or more interop assemblies. The interop assemblies are listed under each product in the Office 2003 setup program under the heading ". NET Programmability Support."

If you have installed the interop assemblies, when you set a reference to Access 2003 or another Office application from Visual Studio .NET, your code will automatically use the installed interop assembly.

Follow these steps to create a Windows Form application named AccessReporter that automates Access 2003, opens the 17-06.MDB database, and runs the rptArtistAlbum report:

Start Visual Studio .NET.
Create a new VB .NET Windows Application project named AccessReporter.
Delete the initial Form1.vb file from the project.
Select Project Add Windows Form... to add a Windows Form file to the project named PrintArtistReport.vb.
Add the controls listed in Table 17-3 to the form. Size the controls to your liking.

Table 17-3. Controls for the Windows Form file for the project PrintArtistReport.vb

Control

Name

Text

Label

lblArtist

Artist:

ComboBox

cboArtist

n/a

Button

cmdRunReport

Run Report

Checkbox

chkPreview

Preview report before printing

Double-click the cmdRunReport button control to jump to the code editor window.
Select Project Add Reference... to display the Visual Studio .NET Add Reference dialog box.
Click the COM tab, select "Microsoft Access 11.0 Object Library" from the upper listbox, and click the Select button as shown in Figure 17-10. Click OK to dismiss the dialog box.

If you do not see Microsoft Access 11.0 Object Library listed in the upper listbox of the COM tab of the Visual Studio .NET Add Reference dialog box, then you have not installed the Access 2003 interop assembly. To install the interop assembly, start the Add or Remove Programs Control Panel applet. Choose to change the Microsoft Office 2003 installation. From the setup program, choose Add or Remove Features. On the next page of the setup wizard, ensure that the Access and "Choose advanced customization of applications" checkboxes are selected and click Next. Under the Microsoft Office Access node, make sure the ".NET Programmability Support" entry is enabled and click Update.

Figure 17-10. The Visual Studio .NET Add Reference dialog box

Add the following line of code at the top of the code window before the Class statement to import the Microsoft.Office.Interop namespace:
```
Imports Access = Microsoft.Office.Interop.Access
```
You also need to add the following Imports statement (below the other Imports statement) to import the System.Data.OleDb namespace:
```
Imports System.Data.OleDb
```
Add the following code to the PrintArtistReport class module, just beneath the Inherits statement to define two module-level constants:
```
    Private Const strDb As String = "D:\Books\AccessCookBook2003\ch17\17-06.mdb"
    Private Const strRpt As String = "rptArtistAlbums"
```
You will need to edit the path to the 17-06.MDB database to match the location of the database on your system.

Add the following code to the form's load event handler to populate the cboArtists combobox control with a list of Artists from the tblArtists table in the 17-06.MDB database:

    Private Sub PrintArtistReport_Load(ByVal sender As System.Object, _
     ByVal e As System.EventArgs) Handles MyBase.Load
        ' This code populates the cboArtists control
        ' with the list of artists from the 17-06.mdb database.

        Dim cnx As OleDbConnection = New OleDbConnection( _
         "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDb)

        Dim strSql As String = "SELECT ArtistId, ArtistName " & _
         "FROM tblArtists ORDER BY ArtistName"

        Dim daArtists As OleDbDataAdapter = New OleDbDataAdapter(strSql, cnx)

        Dim dsArtists As DataSet = New DataSet
        daArtists.Fill(dsArtists, "Artists")

        cboArtist.DataSource = dsArtists.Tables("Artists").DefaultView
        cboArtist.DisplayMember = "ArtistName"
        cboArtist.ValueMember = "ArtistId"
    End Sub

Add the following code to the cmdRunReport's Click event handler to open the report:

    Private Sub cmdRunReport_Click(ByVal sender As System.Object, _
     ByVal e As System.EventArgs) Handles cmdRunReport.Click
        Dim accApp As Access.Application
        Dim strWhere As String

        ' Construct where clause
        strWhere = "ArtistId = " & cboArtist.SelectedValue

        ' Instantiate the Access application
        accApp = New Access.Application

        'Open database
        accApp.OpenCurrentDatabase(strDb)

        If chkPreview.Checked Then
            ' Make Access visible and open report
            ' in print preview.

            ' Display report in Print Preview.
            accApp.DoCmd.OpenReport(strRpt, Access.AcView.acViewPreview, , _
             strWhere)

            ' Make Access visible so you can see the report.
            ' It will be up to the user to shut down Access.
            ' However, Access will not be released from memory until
            ' this application shuts down.
            accApp.Visible = True
        Else
            ' Go ahead and print directly. No need
            ' to make Access visible or to leave open.

            ' Print report to printer and quit Access.
            accApp.DoCmd.OpenReport(strRpt, Access.AcView.acViewNormal, , _
             strWhere)
            accApp.DoCmd.Quit( )

            ' Force Access to shutdown now.Unless you include this code, 
            ' Access won't be removed from memory until this app shuts down.
            System.Runtime.InteropServices.Marshal.ReleaseComObject(accApp)
        End If
    End Sub

Select Project AccessReporter Properties to display the Project Properties Pages dialog box. On the Common Properties, General page of the dialog box, select the PrintAccessReport form as the startup object and click OK to close the dialog box.
Select File Save All to save the open files.
Select Debug Start to run the application. Select an artist from the combobox control, ensure that the "Preview report before printing" checkbox is selected, and click on Run Report to open the rptArtistAlbums report in Print Preview view. The AccessReporter application is shown in Figure 17-11.

Figure 17-11. The AccessReporter Windows Form application is shown in front of the Access report it has previewed

Close Access and quit the AccessReporter application.

17.6.3 Discussion

Here's the basic process followed by the AccessReporter application to run the Access report:

When AccessReporter starts it calls the startup form, PrintArtistReport.
As the PrintArtistReport form is loaded it executes the form's Load event handler, which populates the cboArtists combobox with data from the 17-06.MDB database using ADO.NET.
When the cmdRunReport button is clicked by the user, the code attached to the cmdRunReport_Click event handler automates Access, and uses the OpenReport method to open the rptArtistAlbum report, passing it the selected Artist as a parameter.

17.6.3.1 Shutting down Access

The PrintArtistReport form includes a checkbox control to determine if the report is to be previewed or printed. If the report is to be previewed, then it is necessary to make Access visible to allow the user to view the report. In this case, it will be up to the user to close down Access:

accApp.DoCmd.OpenReport(strRpt, Access.AcView.acViewPreview, , _
 strWhere)
accApp.Visible = True

If the report is to be sent to a printer, the code takes a different path. There's no need to make Access visible. In fact, Access is shut down after the printing is complete:

accApp.DoCmd.OpenReport(strRpt, Access.AcView.acViewNormal, , _
 strWhere)
accApp.DoCmd.Quit( )

This code alone, however, will not remove Access from memory. That feat is accomplished with this additional line of code:

System.Runtime.InteropServices.Marshal.ReleaseComObject(accApp)

If you do not call the ReleaseComObject method, Access will not be removed from Memory until the AccessReporter application is closed.

17.6.3.2 Communicating parameters to Access

When automating Access 2003, you have no way to supply parameters to a parameter query, thus you must devise some other technique to pass parameters from your .NET application to Access. In many situations, you can construct a WHERE clause and pass it to the report using the fourth parameter of the call to the OpenReport method. This is the technique that was used in this solution.

There may be some situations where constructing a WHERE clause is too cumbersome. For example, if you used a listbox control that allowed for multiple rows to be selected, the WHERE clause could be inordinately long. In this case, another option would be to use a "parameters" table to which you would add the selected rows. You could then create a query that joined to this parameters table and base the report on this query. Before running the report your code would need to iterate through the rows in the listbox and, using ADO.NET, insert a row into the "parameters" table for each row of the selected listbox rows.

17.6.3.3 Interop assemblies

There are two types of interop assemblies: primary interop assemblies and alternate interop assemblies. Anyone can generate an alternate interop assembly (AIA) for any component by setting a reference to a COM component from Visual Studio .NET (which generates the AIA by calling the tlbimp utility that ships with the .NET Framework). A primary interop assembly (PIA) is the official interop assembly that has been produced and signed by the component's author. While the tlbimp utility usually does a good job in generating the AIA for a component, there may be situations where the types are not mapped properly. PIAs, on the other hand, are usually hand-optimized beyond the code automatically generated by tlbimp. Whenever it is available it's preferable to use the PIA rather than an AIA.

When setting a reference to a COM component or program from Visual Studio .NET, it will always use the PIA if one has been installed on the system. Otherwise, it will create an AIA and use that instead.

As mentioned in the solution, Office 2003 ships with PIAs for each of its applications. You can download the PIAs for Access 2002 and the other Office XP applications from the following URL:

http://msdn.microsoft.com/library/default.asp?url=/downloads/list/office.asp

Microsoft has no plans to supply PIAs for Office 2000 or Office 97, so you will have to generate and use AIAs for these applications.

17.6.4 See Also

See A Primer to the Office XP Primary Interop Assemblies (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnofftalk/html/office10032002.asp).

[ Team LiB ]