Charles Bestor
Microsoft Corporation
March 1996
OLE is an important technology to understand if you're developing solutions using Microsoft® Access. This brief introduction will answer the question: "What is OLE and why is it important to me as a developer of Microsoft Access-based solutions?"
OLE is a set of software services for integrating information from various Microsoft Windows®-based applications. OLE-enabled services are a part of virtually every Windows-based application available today, and, in fact, they are a key element of the Windows operating system and strategy.
OLE services for desktop applications include:
OLE helps developers and users alike, breaking down the walls between applications and making available functionality that would have previously required time-consuming coding and maintenance.
The following features of OLE are supported in Microsoft® Access:
Let's examine each of these features to understand how they work and what benefits they offer a developer working with Microsoft Access.
To provide users with the ability to store any kind of data for Windows, a developer can add a field to a table of type OLE Object. An object (such as a Microsoft Excel spreadsheet, a Microsoft Word document, graphics, sounds, or other binary data) can then be linked to or embedded in the table. Linked objects are those that reside outside of the Microsoft Access database, can be updated independently of the database, and are managed by the file system, not the database. Embedded objects, however, reside completely within the database, are updated by first launching the database, and travel within the database, as opposed to within the file system. In general, linked objects are better suited for data that changes frequently; embedded objects are better when portability is an issue.
A user of Microsoft® Visual Basic® for Applications program may paste documents created in other applications into an OLE Object field. After they've been pasted in, double-clicking on the objects allows them to be edited. By default, embedded objects are edited in-place, that is, the surrounding Microsoft Access form remains visible, but the menu and toolbar switch to the application that created the object. Linked objects are edited in a separate window. Depending on what suits your needs, you can display either an icon or a copy of the image. Microsoft Access provides a variety of frame-based properties so that the developer can control the behavior of OLE objects on a form.
To explore OLE storage for yourself, create a new database and then create a new table with the following two fields: a Text field and an OLE Object field. Add several records, placing the following labels in the text field: embedded object, linked object, linked object as icon, and so forth. Now, viewing the table in Datasheet view, place the cursor in the OLE Object field and use the Object command on the Insert menu to experiment with inserting various objects in different ways. Be sure to place a description of how the object was inserted into your text field so that you can learn how behavior differs for various storage methods.
Next, create a form based on this table, and add your two fields to the form. On the All tab of the properties sheet for the OLE Object, experiment with the first 11 properties, most of which relate to the behavior of the OLE object. Press SHIFT+F1 and point to any of the properties for an explanation from the Help file for that property. This will give you a good introduction to the behavior of OLE objects in a Microsoft Access database and properties of object frames on forms that can contain OLE objects.
One important note on OLE objects and forms for Microsoft Access for Windows 95: Many developers previously used OLE objects as pictures on forms. There is a new data type in Microsoft Access for Windows 95 called an image. An image is useful for displaying graphics that don't need to be modified on forms. OLE objects are by definition modifiable, so Microsoft Access tracks editing information for each OLE object. This adds some overhead to your form. With the image data type, this tracking is unnecessary, so forms open and update faster than they would with an OLE object.
"Drag and drop" turns out to be an extremely intuitive metaphor for users working in a graphical environment. "Cut and paste" might be familiar for people who set type for their high school newspapers, but everyone knows that to move something, you pick it up and drop it where you want it to go!
Drag-and-drop editing is a great feature within the applications in Office. Perhaps as important, it is available via OLE between applications. Many applications, including those in Office, support various drag-and-drop operations with other applications. An application can be a drag-and-drop source and/or target, and it can provide a variety of behavior, from simple unformatted copy and paste to sophisticated formatting, shortcutting, and more.
Drag-and-drop editing is typically not implemented in custom database solutions, but developers should be aware of some of the capabilities that users have with Microsoft Access. Here are some examples of what Microsoft Access can do with drag and drop:
One of the most exciting areas of OLE in Microsoft Access is OLE controls, new for Microsoft Access for Windows 95. OLE controls are 32-bit reusable components, available from Microsoft and third parties, which can enhance your applications in exciting ways. The main benefit of using OLE controls is that your applications get unique and powerful functionality without your incurring the cost of maintaining the code for these objects. Additional benefits include:
There is a wide variety of OLE controls available. Microsoft provides a calendar control in Microsoft Access that allows users to view and set dates graphically. The Microsoft Access Developer's Toolkit provides an additional 12 controls, including a data outline control, toolbar, status bar, slider control, tab control, and more. All of these controls may be distributed as part of a custom solution provided that the developer has purchased the Microsoft Access Developer's Toolkit.
Third-party controls are plentiful—from controls that let you interface with add-in cards such as voice-processing systems, to external devices such as factory equipment, to multimedia controls, gauges, grids, applets, and more. The OLE Broker, a World Wide Web site that allows you to locate reusable OLE components and receive industry information on OLE, is located at http://www.olebroker.com/. A list of vendors who provide OLE components specifically for users of Microsoft Access can be found on the Web site for Microsoft Access developers at http://www.microsoft.com/accessdev.
To try out an OLE control, do the following: Create a new form based on a table containing a date field. In Design view, drag the date field from the field list to the form to create a text field on the form based on the date field in the table. Now add the Calendar Control to your toolbox by right-clicking on your toolbox, clicking Customize, scrolling to the Custom Controls category, and then dragging the Calendar Control from the Customize Toolbars dialog box to your toolbox.
.
Next, click, but don't drag, the Calendar Control icon in the toolbox. Then click and drag the date field from the field list to your form. A Calendar Control, bound to the date field, will be created for you automatically. Now, switch to Form view and see how you can update the text field by typing the date or by clicking on the Calendar Control. By setting the date in either control, the underlying data field is updated. To take this one step further, check out the properties and events for the Calendar Control by right-clicking on the Calendar Control in Design view. Or use the Object Browser, available from a Code window (right-click on the Calendar Control, click Build Event, select Code Builder from this list, and then select Object Browser from the View menu), to view the properties and methods of the Calendar Control.
Using OLE controls can help you develop better solutions—faster. Additional samples may be found in the OLE Controls Sample Application page on the Web site for Microsoft Access developers at http://www.microsoft.com/accessdev/.
The ability of Microsoft Access to "remote control" another application for Windows via OLE Automation is not new to Microsoft Access for Windows 95. Microsoft Access has been an OLE Automation controller since version 2.0. The two new features in Microsoft Access for Windows 95 are the Object Browser, described above and pictured below, and the use of Visual Basic for Applications as the language used for remote control.
Consider the following simple code example, which opens a Microsoft Word document template, takes data from a Microsoft Access table, and places it into the document at preset points called bookmarks:
Dim oWord as Object
Public Sub Product_Sheet(frm As Form)
'The underscore (_) is a line-continuation character in Microsoft Access 95!
Dim product As String, quote As String, Country As String, _
Family As String
'Instantiate the object, oWord, as a WordBasic object
Set oWord = CreateObject("Word.Basic")
'Now that oWord exists, we can manipulate it via VBA
With oWord
.appmaximize
.filenew "c:\program files\access 95 developer demo\ps.dot"
.viewpage
End With
'With/End With is a new construction for Microsoft Access 95!
'Pull some data from an open form (could substitute table-
' or query-based data here)
Product = frm![Bean Name]
Quote = frm!Description
Country = frm!Country
Family = frm!Family
'Pump the data in the Word doc, and preview the document
'Note: Goto is going to a predefined Bookmark in the Word doc
With oWord
.EditGoto "Product_Title"
.Insert product
.EditGoto "Product"
.Insert product
.EditGoto "Product2"
.Insert product
.EditGoto "Quote"
.Insert quote
.EditGoto "Country"
.Insert Country
.EditGoto "Family"
.Insert Family
.FilePrintPreview
End With
End Sub
This technique can be used to drive any application that acts as an OLE Automation server (see the following section titled "OLE Automation Server" for a complete explanation). In the example above, data from the Microsoft Access database is formatted in a Microsoft Word document. This technique can also be used to access the analysis and charting capabilities of Microsoft Excel, project management in Microsoft Project, presentations in Microsoft PowerPoint®, and a variety of other applications.
Additional information on OLE Automation, including important techniques for optimizing performance in these scenarios, can be found in the articles "A Primer on Early Binding" and "Your Unofficial Guide to Using OLE Automation with Microsoft Office and Microsoft BackOffice."
Completely new for Microsoft Access for Windows 95 is the ability to act as an OLE Automation server, in other words, to be the object of the remote control activity performed by an OLE Automation controller. Microsoft Access for Windows 95 thus exposes all of its powerful functionality to OLE Automation controllers. A typical scenario would have a Visual Basic or Microsoft Excel application using OLE Automation to use the report writer in Microsoft Access.
The following example shows this being done from Microsoft Excel. The first subroutine gets a list of the reports stored in the currently open Microsoft Access database, puts the list into an array, and then into a list box in Microsoft Excel. The user selects a report from the list box and then runs the second subroutine to print the selected report:
Dim objAccess As Object
Sub GetAccessReportList()
'Note: to use early binding (as we do here) you must have a reference
' in your project to DAO 3.0, chooose Tools/References to add this
'The first parm for GetObject is omitted, thus retrieving the active
' Microsoft Access object, i.e. Microsoft Access is assumed to be open!
Set objAccess = GetObject(, "Access.Application.7")
Dim rpt As Document
Dim rptCollection As Documents
Dim rgReports() As String
Dim iReports As Integer
Set rptCollection = objAccess.dbengine(0)(0).containers("Reports").Documents
'Set the array size based on the number of reports in the collection
ReDim rgReports(rptCollection.Count)
iReports = 0
'For Each/Next loops also work in Microsoft Access 95!
For Each rpt In rptCollection
iReports = iReports + 1
'Put the report name in the array
rgReports(iReports) = rpt.Name
Next
'Clear the list first so we don't append the same list of reports
Sheets("EIS Report Selector").ListBoxes.RemoveAllItems
'Add the items to the list box from the array
For iReports = 1 To rptCollection.Count
Sheets("EIS Report Selector").ListBoxes(1).AddItem rgReports(iReports)
Next
End Sub
Sub PrintReport()
'This sub actually prints the report
objAccess.DoCmd.OpenReport objAccess.DBEngine(0)(0).Containers("Reports")._
Documents(Sheets(1).ListBoxes(1).ListIndex - 1).Name, 0
AppActivate "Microsoft Access"
End Sub
If you explore the Object Browser for Microsoft Access from an OLE Automation controller (for example, from Microsoft Excel), you may find the following interesting: Microsoft Access actually provides three libraries for you to browse through: Microsoft Access, Visual Basic for Applications, and Data Access Objects (DAO). That is, you may program against the Microsoft Access application object, the language object, or directly against the Microsoft Jet database engine. This allows you to attach your OLE Automation code to the most appropriate objects for your particular application; and if you're just manipulating data from a Jet database, you can program directly against Microsoft Jet through DAO, avoiding the overhead of loading a complete application.
Microsoft Access derives much of its strength as a solutions platform from the advanced use of OLE technologies, including OLE storage, drag-and-drop operations, OLE controls, and OLE Automation. Using OLE effectively allows the solution developer to gain maximum leverage in the Microsoft Windows and Microsoft Office environments, and to build complete, elegant, and relatively low-cost solutions.