لطفا منتظر باشید ...
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.
