Linking Access to Other Office Applications | Programming Microsoft Access 2000 (Microsoft Programming Series)

This section introduces three techniques (using installable ISAM drivers, the OpenDataSource method of the MailMerge object, and automation) for making Access work with other Office applications. Subsequent sections will apply these techniques in practical contexts.

Installable ISAM Drivers

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-open database connectivity (ODBC) data sources such as Excel, dBASE, and Paradox. In this section, we'll use the Excel ISAM driver for linking to Excel workbooks from Access. 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. You can learn more by examining the Connect property summary in the online help.

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 work for dBASE and Paradox data in version 5 and earlier. If you need read/write access to other versions, you must independently acquire the Borland Database Engine through Inprise.

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 provider. Follow this with a specification that points at the file for the data source. In the case of Excel, this includes the drive, path, and filename. In certain other cases, you can designate just the drive and the path. You designate this 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 an Excel 2000 workbook using the string " Excel 8.0" followed by a semicolon.

The following simple sample uses an ISAM driver to link to an Excel 2000 workbook in an Access 2000 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 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 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:\Programming Access\Chap09\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.

The OpenDataSource Method

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. You can tap these resources with Word-based VBA procedures as well as through automation from within 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 with a special query within Access. The OpenDataSource method's Connect parameter merely references that query. You use the FirstRecord and LastRecord properties of the DataSource object to specify the first and last records to appear in a merged Word document. The DataSource object points to a target specified by the OpenDataSource method.

Automation

Using automation, you can enable one application to control another. The Microsoft 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, it must have a reference to the other application's object library and must create an instance of the application. (See Chapters 7 and 8 for information 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.

click to view at full size.

Figure 9-1. A References dialog box in Access showing references to Excel, Outlook, and Word.

CreateObject vs. GetObject

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, this 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 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 specification can represent any Office application (or even another COM object). The first procedure, xlThere, uses early binding. The xlApp variable can represent only an Excel Application object. 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 entire procedure—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 that xlThere does.

 Sub xlThere() Dim xlApp As Excel.Application     If isAppThere("Excel.Application") = False Then 'If no, 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 app 'and set reference to Nothing.     If MsgBox("Close XL ?", vbYesNo, _         "Programming Microsoft Access 2000") = 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 it opens it. If you want an application to show, you must normally set its Visible property to True. Different applications expose different objects for you to automate. Excel exposes objects such as Application, Workbook, and Worksheet. The latter two, of course, are not available with other Office applications.

Closing an automation reference

The xlThere procedure conditionally disposes of a reference to another Office application. First, you close or quit the application. (Excel supports a Quit method.) Then you set the reference to Nothing. Both steps are required to retrieve the resources consumed by the automation reference.