Programming Microsoft Office Access 2003 (Core Reference) [Electronic resources] نسخه متنی

Linking Access to Other Office Applications

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.

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-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.

Using ISAM Drivers with Excel

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.

SubConnect2XLPrintFromFirst() 
Dimcnn1AsNewADODB.Connection,rst1AsADODB.Recordset
'MakeconnectiontoExcelsource
cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &_
"DataSource=C:\Access2003Files\Customers.xls;" &_
"ExtendedProperties=Excel8.0;"
'Openread-onlyrecordsetbasedonExcelsource
Setrst1=NewADODB.Recordset
rst1.CursorType=adOpenForwardOnly
rst1.LockType=adLockReadOnly
rst1.Open "customers",cnn1,,,adCmdTable
'Printselectedfieldsfromfirstrecord
Debug.Printrst1.Fields(0).Value,rst1.Fields(1).Value
'Closeconnectiontosource
cnn1.Close
EndSub

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.

Using ISAM Drivers with Outlook

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.

SubConnect2OutlookPrintFromFirst() 
Dimrst1AsADODB.Recordset
#ConstAnyPath=False
'Instantiaterecordset
Setrst1=NewADODB.Recordset
'OpenrecordsetonContactsfolderwitheitherof
'twopathdesignations
#IfAnyPath=FalseThen
rst1.Open "SELECTFirst,Last,[EmailAddress] " &_
"FROMContactsIN'C:\Windows\Temp\;'" &_
"[Outlook9.0;MAPILEVEL=PersonalFolders|;];",_
CurrentProject.Connection
#Else
rst1.Open "SELECTFirst,Last,[EmailAddress] " &_
"FROMContactsIN'E:\;'" &_
"[Outlook9.0;MAPILEVEL=PersonalFolders|;];",_
CurrentProject.Connection
#EndIf
'PrintfirstrowofContactsfolder
Debug.Printrst1(0),rst1(1),rst1(2)
'Cleanupobjects
rst1.Close
Setrst1=Nothing
EndSub

The OpenDataSource Method

You can use the 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.

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.

Automation

Using Automation, 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.

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, 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.

SubXLThere()
DimxlAppAsExcel.Application
IfIsAppThere("Excel.Application")=FalseThen
'Ifnot,createanewinstance
SetxlApp=CreateObject("Excel.Application")
xlApp.Visible=True
Else
'Otherwise,referencetheexistinginstance
SetxlApp=GetObject(, "Excel.Application")
EndIf
'Ifuserwantsinstanceclosed,closeapplication
'andsetreferencetoNothing
IfMsgBox("CloseXL?",vbYesNo,_
"ProgrammingMicrosoftAccess2003")=vbYesThen
xlApp.Quit
SetxlApp=Nothing
EndIf
EndSub
FunctionIsAppThere(appName)AsBoolean
OnErrorResumeNext
DimobjAppAsObject
IsAppThere=True
SetobjApp=GetObject(,appName)
IfErr.Number<>0ThenIsAppThere=False
EndFunction

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