|
|
This section introduces three techniques for making Access work with other Office applications: using installable ISAM drivers, employing the OpenDataSource method of the MailMerge object, and implementing Automation. Subsequent sections will apply these techniques in practical contexts.
You use the familiar Microsoft ActiveX Data Objects (ADO) Connection object to link to other data sources through installable ISAM drivers. These data sources can include non-Jet, non-ODBC data sources such as Excel, dBASE, and Paradox. This section will demonstrate how Access developers can use the Excel ISAM driver for linking to Excel workbooks and the Outlook ISAM driver for linking to Outlook folders. Similar techniques apply to ISAM drivers for dBASE, Paradox, Lotus 1-2-3, text, and HTML files, but each driver has its unique features and restrictions.
| Note |
Installable ISAM support continues to change with user requirements and technology developments. ISAM support for Microsoft FoxPro databases was discontinued with Access 2000 in favor of the Microsoft ODBC FoxPro driver. The traditional ISAM drivers still provide import/export/read access for dBASE and Paradox data in version 5 and earlier. If you need read-write access to these versions of dBASE and Paradox files, you must independently acquire the Borland Database Engine through Inprise Inc. or verify the installation of Jet 4.0 SP5. See Microsoft Knowledge Base article 230125 for additional details. You can find any Knowledge Base article by going to http://support.microsoft.com and searching for its article number. |
When you use an ISAM driver, your connection string has three arguments, each of which must terminate with a semicolon. First you designate a provider. When you use an installable ISAM driver, start your connection string with a reference to the Jet 4.0 provider. Follow this reference with a specification that points at the file for the data source. In the case of Excel, this specification includes the drive, path, and filename. In certain other cases, you can designate just the drive and the path. You designate the final parameter by setting the extended properties parameter equal to the name of the ISAM driver. There are specific drivers for different versions of Excel and for the other types of data sources you can link to. You reference any recent version of an Excel workbook (from Excel 97 through Excel 2003) using the string "Excel 8.0" followed by a semicolon.
The following simple sample uses an ISAM driver to link to an Excel 2003 workbook in an Access 2003 application. The Dim statement declares and creates a new Connection object. The next statement opens the connection by pointing it at an Excel workbook through the Excel 8.0 ISAM driver. After creating the connection to the data source, your application must specify a range of cells in the workbook. This sample assigns the customers range within the file to a Recordset object named rst1. Access uses this link to work with the data in the workbook. The sample concludes by printing the first two columns of the first row from the range in the Excel workbook to the Immediate window in Access.
Sub Connect2XLPrintFromFirst() Dim cnn1 As New ADODB.Connection, rst1 As ADODB.Recordset 'Make connection to Excel source cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Access2003Files\Customers.xls;" & _ "Extended Properties=Excel 8.0;" 'Open read-only recordset based on Excel source Set rst1 = New ADODB.Recordset rst1.CursorType = adOpenForwardOnly rst1.LockType = adLockReadOnly rst1.Open "customers", cnn1, , , adCmdTable 'Print selected fields from first record Debug.Print rst1.Fields(0).Value, rst1.Fields(1).Value 'Close connection to source cnn1.Close End Sub
When you work with an ISAM driver, the Excel data source (or even Excel itself) need not be open. Your application also doesn't require a reference to the Excel object model. Despite the Excel ISAM driver's minimal requirements, you can use it to both read and update Excel data sources.
You can use ISAM drivers to examine the contents of Outlook folders on the current machine from within Access. With this approach, you can read but not update the contents of Outlook folders through an ADO object, such as a recordset. A SQL SELECT statement can serve as the source for the Recordset object, and the recordset can reference the CurrentProject object's connection. No matter which cursor settings you assign the recordset, it will not let you update Outlook folder items.
The SQL string that serves as the source for the recordset requires three elements. First, you must designate a collection of columns to extract from the folder record source. Precede the string specifying the columns you want with the SELECT keyword. You can use an asterisk (*), but performance will be faster if you designate a specific subset of columns. Second, you must designate a folder name and a path to Outlook. Preface the folder name with the FROM keyword. For example, if you want incoming messages, reference the Inbox folder. Third, you need to specify a path name and an Outlook ISAM driver name. Designate the name for the top-level Outlook folder. (Unless you change the default, this is the Personal Folders collection.) Identify the start of the third element with the IN keyword.
The following sample prints the first record in the Contacts folder of Outlook's Personal Folders collection. Use the Outlook 9.0 ISAM driver for Outlook 2003, Outlook 2002, or Outlook 2000. The sample extracts the First, Last, and E-mail Address fields from the Contacts folder. Although you cannot update Outlook through the Outlook ISAM driver, you can view changes (such as updated values) to the underlying recordset.
You will get the same results if you use either of the two paths that you can reference by setting your compiler constant to True or False. On the system that I am running, the E:\ folder points at a CD drive. Demonstrating this capability confirms that it doesn't matter what path you specify, so long as you designate a path.
Sub Connect2OutlookPrintFromFirst() Dim rst1 As ADODB.Recordset #Const AnyPath = False 'Instantiate recordset Set rst1 = New ADODB.Recordset 'Open recordset on Contacts folder with either of 'two path designations #If AnyPath = False Then rst1.Open "SELECT First, Last, [Email Address] " & _ "FROM Contacts IN 'C:\Windows\Temp\;'" & _ "[Outlook 9.0;MAPILEVEL=Personal Folders|;];", _ CurrentProject.Connection #Else rst1.Open "SELECT First, Last, [Email Address] " & _ "FROM Contacts IN 'E:\;'" & _ "[Outlook 9.0;MAPILEVEL=Personal Folders|;];", _ CurrentProject.Connection #End If 'Print first row of Contacts folder Debug.Print rst1(0), rst1(1), rst1(2) 'Clean up objects rst1.Close Set rst1 = Nothing End Sub
You can use the OpenDataSource method of the MailMerge object to link to an Access data source from within a Word application. You use Access—or more specifically, Jet—as a data store for mail merge applications that create mailing labels, form letters, product catalogs, and so on. While Access can do some of this through its Report object, Word is a more natural environment for composing content. It also has excellent text-formatting tools and WYSIWYG features that the Access Report object does not have (see Chapter 6 for more information on the Report object). You can tap these resources with Word-based VBA procedures, as well as through Automation from within Access.
| Note |
The most appropriate development environment for database reports and mail merge documents might be a matter of developer preference. Although Word is a more natural environment for developing text documents, using Word from Access requires Automation. This means that a developer has to program two object models and that a computer needs to manage two Office applications. You might be able to achieve greater efficiency for sets of standard messages sent regularly in large volumes by building solutions entirely within Access. Refer to Chapter 6 for sample reports that illustrate the options available from Access. |
When you reference an Access data source using the OpenDataSource method, you must first reference a Word document file and the Word MailMerge object. You specify two parameters for the method with Access: the Name parameter, which indicates the drive, path, and filename for the Access data source; and the Connection parameter, which designates either a Table or Query data source type, and the name of the Access database object. Your Word document must have either bookmarks or mail merge fields that point to the fields in the Jet database. You invoke the Execute method for the MailMerge object to launch a merge that pulls data from a designated data source, such as an Access table, into a Word document.
You can filter values that appear in a Word mail merge document in several ways. For example, you can use the OpenDataSource method's SQLStatement parameter to specify which records to extract from a data source. When you do this with a Jet data source, you reference Access through an ODBC driver and specify constr as the Connection setting. You use SQL statement syntax to filter records from an Access table or query.
A second approach to filtering is to use a special query within a Word macro that you activate from Access. The OpenDataSource method's Connect parameter merely references that query. You use the FirstRecord and LastRecord properties of the MailMergeDataSource object to specify the first and last records to appear in a merged Word document. The MailMergeDataSource object points to a target specified by the OpenDataSource method.
Using Automation, you can enable one application to control another. Component Object Model (COM) defines the protocol for this capability. The controlling application interacts with the controlled application by manipulating its exposed properties and methods and responding to its events. To do this, the controlling application must have a reference to the other application's object library and must create an instance of that application. (See Chapter 8 for instruction on how to create and manage references programmatically.) The controlling application invokes methods and assigns property values through that instance of the controlled application.
Figure 9-1 shows a References dialog box from an Access application with references to Excel, Outlook, and Word as well as the Office library with the shared object models. In a sense, Automation makes all the Office component object models shared. Access can expose its object model as an Automation server, and it can tap the object models of other applications by acting as an Automation client.
You use the CreateObject and GetObject functions to generate instances of other applications. You use GetObject to determine whether an instance of an application is already open. If it is, you can create a reference to it. If a user is not actively working with the instance, using an open instance might be acceptable. If the Automation server application is not already open or if you prefer not to use an open instance, you can use the CreateObject function to create a new instance of an application. You can also use GetObject to open an instance of an application with a particular file open in it.
The following two procedures create an instance of Excel from within an Access application. The second procedure, IsAppThere, uses late binding to test for an instance of any Office application. An objApp variable with a generic Object declaration can represent any Office application (or even another COM object). The first procedure, XLThere, uses early binding to designate a pointer for an Excel instance. If the application creates a new instance of Excel, the instance lasts only as long as the application. If the application uses an existing instance of Excel (for example, one based on an open version of the Customers.xls file), then the instance can have a scope beyond the application. The option pursued depends on how a user responds to a prompt from the application. One response to the prompt closes the Excel session pointed at by xlApp whether or not the application opened the session.
The xlApp variable can only represent an Excel Application object, because its declaration uses early binding. You cannot replace Excel.Application in either the CreateObject or GetObject functions with another Office Application object, such as Word.Application. However, you can create another procedure altogether—for example, one named WordThere—that includes a variable declared as a Word.Application object type. This new procedure can reference the generic IsAppThere procedure in the same way as XLThere.
Sub XLThere()
Dim xlApp As Excel.Application
If IsAppThere("Excel.Application") = False Then
'If not, create a new instance
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Else
'Otherwise, reference the existing instance
Set xlApp = GetObject(, "Excel.Application")
End If
'If user wants instance closed, close application
'and set reference to Nothing
If MsgBox("Close XL ?", vbYesNo, _
"Programming Microsoft Access 2003") = vbYes Then
xlApp.Quit
Set xlApp = Nothing
End If
End Sub
Function IsAppThere(appName) As Boolean
On Error Resume Next
Dim objApp As Object
IsAppThere = True
Set objApp = GetObject(, appName)
If Err.Number <> 0 Then IsAppThere = False
End Function
Automation does not normally make an Office application visible when opening it. If you want an application to display, you must normally set its Visible property to True. Different applications expose different objects for you to automate. Excel causes objects such as Application, Workbook, and Worksheet to display. The latte