ACC: Programmatically Link or Embed an Object on a Form (95/97)

ID: Q158929

The information in this article applies to:

Microsoft Access versions 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

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.

MORE INFORMATION

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 you are working with, and the type of action 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 the OLE field will contain 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, and then
   quit Microsoft Excel when it starts.

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 OnClick 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, and then
   quit Microsoft Word when it starts.

8. Set the following properties for the unbound object frame:

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

REFERENCES

For more information about the Action property, search the Help Index for "Action property," or ask the Microsoft Access 97 Office Assistant.

For more information about OLE linking and embedding, please see the following articles in the Microsoft Knowledge Base:

   ARTICLE-ID: Q123859
   TITLE     : ACC: Sample OLE Automation for MS Word and MS Excel

   ARTICLE-ID: Q132003
   TITLE     : ACC: How to Save a Copy of an Embedded MS Word Document

   ARTICLE-ID: Q114214
   TITLE     : ACC2: How to Programmatically Embed or Link an Object in a
               Form

Additional query words:

Keywords          : kbole kbprg FmsHowto IntpOle 
Version           : 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto

Last Reviewed: November 21, 1998