Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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:

1. Start Visual Studio .NET.

2. Create a new VB .NET Windows Application project named
   AccessReporter.

3. Delete the initial Form1.vb file from the project.

4. Select Project Add Windows Form... to add a Windows Form
   file to the project named PrintArtistReport.vb.

5. Add the controls listed in Table 17-3 to the form.
   Size the controls to your liking.

6. Double-click the cmdRunReport button control to jump to the code
   editor window.

7. Select Project Add Reference... to display the Visual
   Studio .NET Add Reference dialog box.

8. 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

9. 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

10. 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

11. 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.

12. 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

13. 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

14. 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.

15. Select File Save All to save the open files.

16. 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

17. 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).