• Choose a category
• All
• Classic-Fiction

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.

TIP

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