ACC1x: Cannot Auto Update an OLE-Linked Object in a Table

• Microsoft Access version 1.0, 1.1

SYMPTOMS

The Auto Update check box is unavailable in the Edit Paste Special dialog box when you are attempting to paste link an OLE object.

CAUSE

You cannot paste link an object into an OLE-object field in a table in Microsoft Access and specify that the link be "hot," or automatically updated.

You can place linked OLE objects on forms and reports when they are in Design view. If you paste a link to an OLE object into a form or report in Design view, the Auto Update check box will be available.

RESOLUTION

One workaround is to force Microsoft Access to update the OLE-linked field by selecting the object to update, choosing Object from the Edit menu, and then selecting Update.

This process can be automated using the Access Basic UpdateOLE() function listed below, which can be assigned to a button on the form or to the OnCurrent property of the form. If it is assigned to the OnCurrent property, Microsoft Access will automatically perform the macro whenever you select a different record. This will ensure that the data in the selected record will always be current, effectively simulating auto updating.

Create a new module with the following declarations and UpdateOLE() function:

   '****************************************************************
   ' DECLARATIONS SECTION
   '****************************************************************
   Option Explicit

   '****************************************************************
   ' FUNCTION: UpdateOLE
   '
   ' PURPOSE: Automates updating an OLE-linked field.
   '
   ' ARGUMENTS:
   '    FieldName - A string with the name of the field to update.
   '
   ' EXAMPLE USAGE:
   '    The OnPush property for a button could be set to:
   '       =UpdateOLE("OLEField")
   '
   ' SIDE EFFECTS:
   '    Focus will be left on the OLE-object field.
   '    The record will be put in Edit Mode.
   '
   '****************************************************************
   Function UpdateOLE (ByVal FieldName As String)
      On Error GoTo ErrUpdateOLE
      DoCmd GoToControl FieldName
      DoCmd DoMenuItem A_FORMBAR, A_EDITMENU, A_OBJECT, A_OBJECTUPDATE

   ByeUpdateOLE:
      Exit Function

   ErrUpdateOLE:
      Resume ByeUpdateOLE

   End Function

Using the UpdateOLE() Function

You can use the UpdateOLE() function by assigning it to a button on the form or to the OnCurrent property of the form. If performance is a problem when you are using the OnCurrent solution, the button approach allows you to decide when the data should be refreshed.

For a linked OLE field named "OLEField" on your form, set the OnPush property of the button or the OnCurrent property for the form to the following:

   =UpdateOLE("OLEField")

For three linked OLE fields on a form named "OLEField1", "OLEField2", and "OLEField3", create the following macro and save it with the name "UpdateAllOLEFields"

   Action           Argument
   -------------------------------------------------------
   RunCode          Function Name: =UpdateOLE("OLEField1")
   RunCode          Function Name: =UpdateOLE("OLEField2")
   RunCode          Function Name: =UpdateOLE("OLEField3")

   UpdateALLOLEFields

STATUS

This problem no longer occurs in Microsoft Access version 2.0.

MORE INFORMATION

Hot linking, or automatically updating an OLE object, requires a significant number of system resources. Large numbers of hot-linked OLE linked objects can cause out of memory or other system errors.

However, Auto Update is available to unbound OLE objects inserted in a form or report in Design view. This is because the number of objects inserted in a form is generally minimal.

Steps to Reproduce Behavior

1. Start Microsoft Excel.

2. In cell A1, type "123" (without the quotation marks), and press ENTER.

3. Select the cell and choose Copy from the Edit menu.

4. Switch to or start Microsoft Access and create a table that has a field with the datatype set to "OLE Object".

5. Create a new, blank form, based on the table created in step 4.

6. From the View menu, choose Field List.

7. Drag the OLE field from the Field List window to the middle of the blank form.

8. From the View menu, choose Form.

9. Select the OLE field, and then choose Paste Special from the Edit menu.