Access Cookbook - Ken Getz [327]
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.
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.
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.
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.
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