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?
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.
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.
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:
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.
Close Access and quit the AccessReporter application.
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.
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:
If you do not call the ReleaseComObject method, Access will not be removed from Memory until the AccessReporter application is closed.
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.
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.
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:
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.
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).