While you use Automation to work with the objects in another component’s object model, you use OLE to include objects from other components in a Microsoft Access form or report. These objects are typically documents created by another component that supports OLE and are called OLE objects. A component that provides its documents to be linked or embedded in other components is called an OLE server. A component in which documents can be linked or embedded is called an OLE container. For example, you can embed a Microsoft Word document in a Microsoft Access form and the user can then edit this document in Microsoft Word. In this case, Microsoft Word is the OLE server, and Microsoft Access is the OLE container.
When you embed an OLE object, you store a copy of the document with your Microsoft Access application. You can also link an OLE object to your application. When you link an OLE object, your application stores only a link to the document that is the data source, not the document itself. The same OLE object can be linkedto more than one application at a time. When you make changes to the linked document, the updated data is displayed in all of the applications that are linked to it.
There are several different ways to create an OLE object in your Microsoft Access application. To include OLE objects on a form or report, put them in either bound object frame controls or unbound object frame controls. Which type of control you choose depends on what you want to do with the OLE object. A bound object frame displays an OLE object that is stored as data in a table. An unbound object frame displays an OLE object that is not associated with data in a table.
OLE objects can be items of data in a database. For example, if your database includes an Employees table, you can save a photograph of each employee with their record. The photograph would be an OLE object, perhaps a bitmap file. Because the photograph is an item of data that is saved in the underlying table, it can be displayed in a bound object frame. You can find an example of this technique in the Employees form of the Northwind sample database.
OLE objects can also be unrelated to the data in a database. For example, your application may include a form that contains a Microsoft Word document with instructions for entering data in the database. If the Word document is a linked object, you can edit the file to which it is linked by double-clicking the OLE object to activate Microsoft Word, and then making changes to the document. The changes you make are reflected in the document displayed on the form. An OLE object like the one described here would be displayed in an unbound object frame.
When you place an unbound object frame control on a form or report, Microsoft Access displays the Insert Object dialog box, where you can select what type of OLE object you want to include, and specify whether to create a new OLE object or use an existing OLE object. If you use an existing OLE object, you can specify whether it is to be linked or embedded. The Insert Object dialog box also appears when you click Object on the Insert menu. When you insert an OLE object by using this dialog box and you haven’t already created the unbound object frame yourself, Microsoft Access creates an unbound object frame that contains the OLE object you have selected.
See Also For more information on object frames, search the Help index for “object frames.”
The following illustration shows a Microsoft Access form that contains different OLE objects.
Each control provides a view of data from a different Windows-based component—drawing, word processor, spreadsheet, and chart—and each represents a separate OLE object.
You can create OLE objects in a Microsoft Access form or report without writing any Visual Basic code. However, if you want greater control over the OLE objects in your application, you can also manipulate them using Visual Basic. The following sections discuss how to work with OLE objects in code.
To work with OLE objects in Visual Basic, you can set properties for the bound or unbound object frame controls that contain the objects. The following table lists some properties that you can use when working with OLE objects in Visual Basic.
| Property | Description |
| Action | Indicates the operation to be performed on an OLE object, such as whether the object is to be embedded or linked. This property is available only at run time. For a list of constants you can use with this property, search the Help index for “Action property.” |
| Class | Identifies the component that supplied the OLE object and identifies the object type (for example, Word.Document or Excel.Sheet). |
| OLETypeAllowed | Indicates whether a control can contain linked OLE objects, embedded OLE objects, or either type. |
| SourceDoc | Indicates the file to link to when creating a linked OLE object (for example, Sheet1.xls). |
| SourceItem | Determines which portion of data within the file is to be linked. |
The Class, OLETypeAllowed, SourceDoc, and SourceItem properties specify information Microsoft Access uses to create the OLE object. The Action property creates the OLE object by using this information, and thus should always be set after the other properties.
See Also For more information on the Action, Class, OLETypeAllowed, SourceDoc, and SourceItem properties, search the Help index for the name of the property.
You can create an empty embedded OLE object in which the user can enter data. For example, you can embed an empty Microsoft Excel worksheet in a Microsoft Access form.
The following examples create a new embedded Microsoft Excel spreadsheet in an unbound object frame. To try these examples, first enter the following code in a standard module and save the module. This function creates a new form with an empty unbound object frame which you can use to try the examples in this section and the next section of this chapter.
Function CreateFormWithFrame(strControlName As String)
Dim frm As Form, ctlFrame As Control, ctlCommand As Control
Dim intLeft As Integer, intRight As Integer
Set frm = CreateForm
Set ctlFrame = CreateControl(frm.Name, acObjectFrame)
intLeft = ctlFrame.Left + ctlFrame.Width + 200
intRight = 200
Set ctlCommand = CreateControl(frm.Name, acCommandButton, , , , _
intLeft, intRight)
With ctlCommand
.Name = strControlName
.Caption = strControlName
.OnClick = "[Event Procedure]"
End With
DoCmd.Restore
End Function
Once you’ve saved this code, run it in the Debug window by entering the following line of code and pressing ENTER.
CreateFormWithFrame("EmbedEmpty")
Microsoft Access creates a new form with an unbound object frame and a command button named EmbedEmpty. Open the form module and enter the code in the following example. Then switch to Form view. When you click the EmbedEmpty button, Microsoft Access embeds a new, empty Microsoft Excel spreadsheet in the unbound object frame. You can double-click the spreadsheet to edit it.
Private Sub EmbedEmpty_Click()
Dim ctl As Control
Set ctl = Me!OLEUnbound0
With ctl
' Enable control.
.Enabled = True
' Set Locked property to False.
.Locked = False
' Allow embedded objects only.
.OLETypeAllowed = acOLEEmbedded
' Specify the OLE server and the type of object.
.Class = "Excel.Sheet"
' Create an embedded object.
.Action = acOLECreateEmbed
End With
End Sub
In the preceding example, the embedded object you create is a brand new OLE object that has not been saved to disk.
You can create an OLE object from a file in Visual Basic. For example, you can embed an existing Microsoft Excel document in a Microsoft Access form.
To try this, create a Microsoft Excel worksheet called Revenue.xls, add some data to it, and save it in the My Documents folder. Next, run the following code in the Debug window to create a form with an unbound object frame and a command button named EmbedExisting. This code calls the CreateFormWithFrame function shown in the previous section.
CreateFormWithFrame("EmbedExisting")
Open the new form module and enter the code in the following example. Then switch to Form view. When you click the EmbedExisting button, Microsoft Access embeds the existing Microsoft Excel document Revenue.xls in the unbound object frame.
Private Sub EmbedExisting_Click()
Dim ctl As Control
Set ctl = Me!OLEUnbound0
With ctl
' Enable control.
.Enabled = True
' Set Locked property to False.
.Locked = False
' Allow embedded objects only.
.OLETypeAllowed = acOLEEmbedded
' Specify the OLE server and the type of object.
.Class = "Excel.Sheet"
' Specify the file to be embedded.
.SourceDoc = "C:\My Documents\Revenue.xls"
' Create the embedded object.
.Action = acOLECreateEmbed
End With
End Sub
To link the document rather than embed it, set the OLETypeAllowed property to acOLELinked and the Action property to acOLECreateLink. You can also change an embedded OLE object to a linked OLE object, or a linked OLE object to an embedded OLE object, by changing the settings of these two properties appropriately.
In the preceding example, setting the Class property for the OLE object is optional. If you don’t set this property, Microsoft Access determines the appropriate setting based on the file name specified in the SourceDoc property.
When you open a linked or embedded document for editing, you activate the object. For example, you’ve probably already seen that when you double-click an embedded OLE object, the component in which that object was created opens so that you can edit the object. An advantage to working with OLE objects in Visual Basic is that you can control when and how an OLE object is activated, rather than leaving it up to the user.
You activate an OLE object in Visual Basic by setting its Action property to acOLEActivate. For example, you could set an OLE object’s Action property within a command button’s Click event procedure. When the user clicks the command button, the object is activated.
When you activate an OLE object, the object takes the operation specified by its verb. A verb specifies how an OLE object behaves when it’s activated. For example, the verb determines whether the OLE object is opened for editing once it is activated. An OLE object can have a number of verbs. You specify which verb to use by setting the OLE object’s Verb property.
The following example activates an embedded OLE object for editing. To try this example, create an unbound object frame named OLEUnbound0 on a form and embed a document, such as a Microsoft Word document. Add a command button named Activate to the form and add the following code to the command button’s OnClick event procedure. Then switch to Form view. When you click the Activate button, the document is activated for editing.
Private Sub Activate_Click()
Dim ctl As Control
Set ctl = Me!OLEUnbound0
With ctl
' Enable control.
.Enabled = True
' Set Locked property to False.
.Locked = False
' Set Verb property to activate for editing.
.Verb = acOLEVerbShow
' Activate object.
.Action = acOLEActivate
End With
End Sub
See Also For more information on the Action property or the Verb property, search the Help index for “Action property” or “Verb property.”
The AutoActivate property determines what the user must do to activate an OLE object. The default setting for the AutoActivate property is Double-Click, which specifies that the user can double-click the OLE object to activate it. If you set this property to GetFocus, the object is activated as soon as the user selects or tabs to it. To activate an OLE object whose AutoActivate property is set to Manual, the user must select the object, point to the appropriate object type on the Edit menu, and then click one of the verbs listed on the submenu. To set this property in Visual Basic, use one of the following constants: acOLEActivateDoubleClick, acOLEActivateGetFocus, or acOLEActivateManual.
You can set both the AutoActivate and Action properties for an OLE object. For example, you can set the AutoActivate property to one of these constants to determine how the user may activate the object, and also set the Action property to activate the object during a particular event.
To prevent a user from activating an embedded OLE object by double-clicking it, you can change the setting of the AutoActivate property within the DblClick event of the bound or unbound object frame that contains the embedded object. For example, embed an OLE object in an unbound object frame on a form and enter the following code in the form module. Then switch to Form view. When you double-click the embedded OLE object, it is not activated. This would not prevent a user from activating the object from the Edit menu, however.
Private Sub OLEUnbound0_DblClick(Cancel As Integer)
Dim ctl As Control
Set ctl = Me!OLEUnbound0
With ctl
.AutoActivate = acOLEActivateManual
End With
End Sub
You can then activate the OLE object from within a different event by setting the Action property to acOLEActivate, as shown in previous examples.
See Also For more information on the AutoActivate property, search the Help index for “AutoActivate property.”
Some OLE objects can be activated from within the control that contains the object—a bound or unbound object frame. When such an object is activated, the user can edit the object, or perform some other action, without leaving Microsoft Access. For example, you could edit an embedded OLE object that was created in Microsoft Word without having to leave Microsoft Access. This feature is called in-place activation.
For an OLE object to be activated in place, the following must be true:
Note If the Locked property of an embedded OLE object is set to Yes, you will be able to edit the object, but any changes that you make will be discarded when the object is deactivated. If the object is linked and not embedded, you can activate it in place, but you cannot edit it in place.
The list of verbs an OLE object supports will vary, depending on the type of object and its current state. If you know which verbs an object supports, you can present a list of available actions to the user.
See Also For more information on the verbs supported by an OLE object, consult the documentation for the application that provides the object.
You can use the Action, ObjectVerbsCount, and ObjectVerbs properties to return the list of verbs supported by an OLE object. Setting the Action property to acOLEFetchVerbs populates the ObjectVerbs property with an array containing all the supported verbs. You can use the ObjectVerbsCount property to determine the number of elements in the array, and then return each verb value from the array one at a time. The ObjectVerbsCount property returns the number of verbs supported by the object, starting with 0.
For example, the following function returns the verbs supported by an OLE object displayed in a bound object frame or an unbound object frame.
Function GetVerbs(ctl As Control) As Integer
Dim obj As Object
Dim intX As Integer, strGetVerb As String
Const conNoOLEObject As Integer = 2771
On Error GoTo ErrorHandler
If (ctl.ControlType = acObjectFrame) Or (ctl.ControlType = acBoundObjectFrame) Then
ctl.Action = acOLEFetchVerbs
For intX = 0 To ctl.ObjectVerbsCount - 1
strGetVerb = strGetVerb & vbCrLf & ctl.ObjectVerbs(intX)
Next intX
MsgBox "Object Verbs for '" & ctl.Name & "':" & vbCrLf & strGetVerb
GetVerbs = ctl.ObjectVerbsCount
Else
MsgBox "Control '" & ctl.Name & "' has no verbs and " & vbCrLf & _
" is not an OLE object."
Exit Function
End If
Exit Function
ErrorHandler:
If Err = conNoOLEObject Then
MsgBox Err & ": " & vbCrLf & Err.Description
Else
MsgBox "Unknown error."
End If
Exit Function
End Function
See Also For more information on the Action, ObjectVerbsCount, and ObjectVerbs properties, search the Help index for the name of the property.
Whenever the data in an OLE object has been modified, the Updated event occurs in the control that contains the object. This event returns a value telling you whether the data has been changed or saved by the component that created it. The Updated event also occurs when a file that an OLE object is linked to is closed or renamed, or when the source file that contains the linked object is closed or renamed.
For example, the following code determines what kind of update occurred and displays an appropriate message:
Private Sub OLEUnbound0_Updated(Code As Integer)
Select Case Code
Case acOLEChanged
MsgBox "OLE object file has been changed."
Case acOLESaved
MsgBox "OLE object file has been saved."
Case acOLEClosed
MsgBox "OLE object file has been closed."
Case acOLERenamed
MsgBox "OLE object file has been renamed."
End Select
End Sub
You can also use the UpdateOptions property to specify when a linked OLE object is updated. By default, the object is updated each time the linked data changes. You can change the property setting so that the object is updated each time any part of the linked document changes. When the Action property for the OLE object is set to acOLEUpdate, the OLE object is automatically updated. The OLE/DDE Links command (Edit menu) displays a dialog box where you can immediately update a linked OLE object or specify whether a linked OLE object is automatically updated.
See Also For more information on the Updated event or the UpdateOptions property, search the Help index for “Updated event” or “UpdateOptions property.”
You can use the Action property to display the Insert Object or Paste Special dialog box to a user. The Insert Object dialog box presents a list of available OLE object types and creates an OLE object based on the user’s selection. The Paste Special dialog box allows the user to paste an OLE object, or part of an OLE object—such as a certain range of cells from a spreadsheet—from the Clipboard into a control.
To display the Insert Object or Paste Special dialog box at run time, set the Action property to a defined constant. To display the Insert Object dialog box, set the Action property to acOLEInsertObjDlg; to display the Paste Special dialog box, set the Action property to acOLEPasteSpecialDlg. You can check the setting of the OLEType property to determine what type of OLE object is in a control.
| OLEType property setting | Control contents |
| acOLELinked | Linked object |
| acOLEEmbedded | Embedded object |
| acOLENone | No object |
For example, create a form with an unbound object frame control named OLEUnbound0 and two command buttons, InsertObject and PasteSpecial. For both command buttons, set the OnClick event property to [Event Procedure], then enter the following code in the form module. When the user clicks one of the buttons, Microsoft Access displays the appropriate dialog box.
Private Sub InsertObject_Click()
On Error GoTo Err_InsertError
' Display Insert Object dialog box.
Me!OLEUnbound0.Action = acOLEInsertObjDlg
Exit Sub
Err_InsertError:
MsgBox "Insert operation not completed."
Resume Next
End Sub
Private Sub PasteSpecial_Click()
On Error GoTo Err_PasteError
' Display Paste Special dialog box.
Me!OLEUnbound0.Action = acOLEPasteSpecialDlg
Exit Sub
Err_PasteError:
MsgBox "Paste operation not completed."
Resume Next
End Sub
As discussed earlier in this chapter, some components that support Automation also support OLE. For example, Microsoft Excel supports both. Using Visual Basic in Microsoft Access, you can work with the objects in the Microsoft Excel object model. You can also link or embed Microsoft Excel files in Microsoft Access forms and reports.
If you’ve embedded or linked a document created in a component that also supports Automation, such as Microsoft Excel, you can work with objects associated with that document much as you would if you were performing Automation. You can use the Object property to set and retrieve the properties of an object and apply its methods. The Object property returns an object reference to an Automation object that is related to the linked or embedded document.
To illustrate this concept, create a Microsoft Excel spreadsheet with some data in the first worksheet and embed the .xls document in an unbound object frame named OLEUnbound0 on a form. Create a button named EditSpreadsheet on the form, set its OnClick event property to [Event Procedure], and enter the following code into the form module. When the user clicks on the EditSpreadsheet button, the Microsoft Excel worksheet is activated and the data in cells in the first row is formatted in bold.
Private Sub EditSpreadsheet_Click()
Dim ctl As Control, wks As Excel.Worksheet
Dim intI As Integer
' Return reference to control containing OLE object.
Set ctl = Me!OLEUnbound0
' Activate OLE object.
ctl.Action = acOLEActivate
' Return reference to embedded Worksheet object.
Set wks = ctl.Object.Application.Workbooks(1).Worksheets(1)
' Initialize integer variable to 1.
intI = 1
' Check whether there is data in cells in first row.
Do Until IsEmpty(wks.Cells(1, intI).Value)
' Set font to bold.
wks.Cells(1, intI).Font.Bold = True
' Increment integer variable.
intI = intI + 1
Loop
End Sub
In this example, the Object property returns an object reference to the Worksheet object that represents the embedded Microsoft Excel worksheet. Once you have a reference to a Worksheet object, you can set and retrieve its properties and apply its methods through Automation.