ACC2000: How to Programmatically Link or Embed an Object on a Form

This article shows you how to programmatically link or embed an object in an unbound object frame on a form by using the object frame properties in Microsoft Access.

You can set the object frame Action property at run time to perform a number of operations on an object frame. These operations include the ability to link and embed objects in an object frame, as well as other operations for programmatic access to OLE functionality.

There are other object frame properties that you must set before you set the Action property. Those other properties depend on the type of OLE object that you are working with, and the type of action that you want to perform using the Action property.

Linking or Embedding an OLE Object

To link or embed an OLE object in an unbound object frame on a form, first set the following properties of the frame:

• OLETypeAllowed: Set to acOLELinked to indicate that the OLE field contains a linked object, acOLEEmbedded for an embedded object, or acOLEEither for either linked or embedded.
• SourceDoc: Set to the path and file name of the file to be linked or embedded. Do not set this property if you are creating an empty OLE object.
• Class: Set to the class name of the file extension. You can get the class from the Windows registry file in HKEY_CLASSES_ROOT listed under the file extension. You can also find the information in the documentation for the application supplying the object. This property may not be required, depending upon the OLE server and object being used.
• SourceItem: Indicates the portion of data in the source document to link to. For example, it can be a cell or cell range in a Microsoft Excel spreadsheet or a bookmark in a Microsoft Word for Windows document. This property setting is optional.

After you set these properties, you can set the Action property to acOLECreateLink to link an object in the object frame, or acOLECreatEmbed to embed an object.

The following methods show examples of both linking and embedding an OLE object in an unbound object frame.

Method to Link an OLE Object

1. Start Microsoft Excel.
2. Create a new spreadsheet with the following entries:

      A1 : Name             B1 : Salary  C1 : Dept.   D1 :  Qty Sold
      A2 : Nancy Davolio    B2 : 16,000  C2 :     9   D2 :        19
      A3 : Andrew Fuller    B3 : 25,000  C3 :     3   D3 :       129
      A4 : Michael Suyama   B4 : 28,500  C4 :     3   D4 :       234
      A5 : Janice Leverling B5 : 30,000  C5 :    12   D5 :       199
      A6 : Linda Callahan   B6 : 50,000  C6 :     9   D6 :       126
      A7 : Johnathan King   B7 : 50,000  C7 :    12   D7 :        45
   					

3. Save the spreadsheet as TestOLEAuto.xls, and then quit Microsoft Excel.
4. Start Microsoft Access.
5. Create a new form not based on any table or query in Design view.
6. Add an unbound object frame control to the Detail section of the form.
7. In the Insert Object dialog box, click Create New, and then select Microsoft Excel Worksheet in the Object Type box. Click OK.
8. Set the Name property of the unbound object frame to OLEExcelSheet.
9. Add a command button to the form, and set its Name property to cmdOLEAuto.
10. Set the On Click property of the cmdOLEAuto command button to the following event procedure:

    Private Sub cmdOLEAuto_Click()
    
       On Error GoTo Error_cmdOleAuto_Click
       With Me![OLEExcelSheet]
          .Enabled = True
          .Locked = False
          ' Specify what kind of object can appear in the field.
          .OLETypeAllowed = acOLELinked
          ' Class statement--optional for Excel worksheet.
          .Class = "Excel.Sheet"
          ' Specify the file to be linked.
          ' Type the correct path name.
          .SourceDoc = "c:\<pathname>\TestOLEAuto.xls"
          ' Range statement--optional for Excel worksheet.
          .SourceItem = "R1C1:R7C4"
          ' Create the linked object.
          .Action = acOLECreateLink
          ' Optional size adjustment.
          .SizeMode = acOLESizeZoom
       End With
    Exit_cmdOLEAuto_Click:
       Exit Sub
    Error_cmdOleAuto_Click:
       MsgBox CStr(Err) & " " & Err.Description
       Resume Exit_cmdOLEAuto_Click
    End Sub
    					

11. Save the form as frmOLEAutoLink.
12. Open the form in Form view and click the cmdOLEAuto command button.
    
    Note that the TestOLEAuto.xls spreadsheet appears in the form.

Method to Embed an OLE Object

1. Start Microsoft Word.
2. Create a new document and type some text into it.
3. Save the document as TestOLEAuto.doc, and then quit Microsoft Word.
4. Start Microsoft Access.
5. Create a new form not based on any table or query in Design view.
6. Add an unbound object frame control to the Detail section of the form.
7. In the Insert Object dialog box, click Create New, and then select Microsoft Word Document in the Object Type box. Click OK.
8. Set the following properties for the unbound object frame:
   Name: OLEWordDoc
   Height: 11"
9. Add a command button to the form, and set its Name property to cmdOLEAuto.
10. Set the On Click property of the cmdOLEAuto command button to the following event procedure:

    Private Sub cmdOLEAuto_Click()
       On Error GoTo Error_cmdOLEAuto_Click
       With Me![OLEWordDoc]
          .Enabled = True
          .Locked = False
          ' Specify what kind of object can appear in the field.
          .OLETypeAllowed = acOLEEmbedded
          ' Class statement for Word document.
          .Class = "Word.Document"
          ' Specify the file to be embedded.
          ' Type the correct path name.
          .SourceDoc = "c:\<pathname>\TestOLEAuto.doc"
          ' Create the embedded object.
          .Action = acOLECreateEmbed
          ' Optional size adjustment.
          .SizeMode = acOLESizeZoom
       End With
    Exit_cmdOLEAuto_Click:
       Exit Sub
    Error_cmdOLEAuto_Click:
       MsgBox CStr(Err) & " " & Err.Description
       Resume Exit_cmdOLEAuto_Click
    End Sub
    					

11. Save the form as frmOLEAutoEmbed.
12. Open the form in Form view and click the cmdOLEAuto command button.
    
    Note that the TestOLEAuto.doc document appears in the form.

For more information about the Action property, click Microsoft Access Help on the Help menu, type action property in the Office Assistant or the Answer Wizard, and then click Search to view the topic.