Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources]

Controlling Access from Other Applications

Many times, you will want to control Access from another application. You might want to run an Access report from a Visual Basic or Excel application, for example. Just as you can tap into many of the rich features of other products (such as Excel) from within Access, you can use some of Access's features from within another program. Fortunately, it is extremely easy to control Access from within other applications.

You can find an overview of the Access object model in Access Help. Unless you are very familiar with the Access object model, you should look at this graphical representation of Access's object model before you attempt to use automation to control Access. Access launches with its Visible property set to False. You can change the Visible property of the application object to True to make Access visible.

The form shown in Figure 22.14 is a UserForm associated with an Excel spreadsheet. It is called frmReportSelect and is part of the Excel spreadsheet called RunAccessReports.xls, included on the sample code CD. The form enables you to select any Access database. It displays a list of all reports in the selected database; you can use this list to preview an Access report or print multiple Access reports.

Figure 22.14. The UserForm that enables you to print Access reports.

Listing 22.14 shows how this UserForm form accomplishes its work.

Listing 22.14 Creating a Visual Basic Form to Print Reports

Private Sub cmdSelectDatabase_Click() 'Set filter property of the Common Dialog control dlgCommon.Filter = "*.mdb" 'Display the open common dialog dlgCommon.ShowOpen 'Ensure that a file was selected If dlgCommon.FileName = " Then MsgBox "You Must Select a File to Continue" Else 'Set the text property of the text box to the 'file selected in the Open dialog Me.txtSelectedDB = _ dlgCommon.FileName 'Call the ListReports routine Call ListReports End If End Sub Private Sub ListReports() On Error GoTo ListReports_Err Dim vntReport As Variant 'If the Access object is not set, instantiate Access If mobjAccess Is Nothing Then Set mobjAccess = New Access.Application End If 'Open the database selected in the text box mobjAccess.OpenCurrentDatabase (Me.txtSelectedDB) 'Clear the list box lstReports.Clear 'Loop through each report in the AllReports collection 'of the selected database For Each vntReport In mobjAccess.CurrentProject.AllReports lstReports.AddItem vntReport.Name Next vntReport ListReports_Exit: Exit Sub ListReports_Err: MsgBox "Error #" & Err.Number & _ ": " & Err.Description Resume ListReports_Exit End Sub

The cmdSelectDatabase_Click event routine sets the Filter property of the Common Dialog control to Access database files. The ShowOpen method of the common dialog control is used to display the File Open dialog to the user. The ListReports routine executes after the user selects a file from the dialog.

The ListReports subprocedure begins by creating an instance of the Access application. It uses the OpenCurrentDatabase method of the Access object to open the Access database selected by the user in the File Open common dialog box. It then loops through the AllReports collection of the CurrentProject object that is associated with the selected database. It adds the name of each report to the list box.

The routine in Listing 22.15 prints the selected reports.

Listing 22.15 Creating a New Instance of the Access Application Object

Private Sub cmdPrint_Click() On Error GoTo cmdPreview_Err Dim intCounter As Integer Dim intPrintOption As Integer 'Evaluate whether Print or Preview was selected If optPreview.Value = True Then intPrintOption = acViewPreview ElseIf optPrint.Value = True Then intPrintOption = acViewNormal End If 'Make Access Visible mobjAccess.Visible = True 'Loop through the list box, printing the 'selected reports For intCounter = 0 To _ lstReports.ListCount - 1 If lstReports.Selected(intCounter) Then mobjAccess.DoCmd.OpenReport _ ReportName:=Me.lstReports.List(intCounter), _ View:=intPrintOption End If Next intCounter cmdPreview_Exit: Exit Sub cmdPreview_Err: MsgBox "Error #" & Err.Number & _ ": " & Err.Description If Not mobjAccess Is Nothing Then mobjAccess.Quit End If Set mobjAccess = Nothing Resume cmdPreview_Exit End Sub

The cmdPrint_Click event routine begins by evaluating whether the user selected the print or preview option button. It makes the Access application object visible. The code then loops through the lstReports list box, printing or previewing each selected report. The OpenReport method is used along with the constant acViewPreview or the constant acViewNormal to accomplish this task.