ACC: How to Use SendKeys in a Macro to Change an OLE Link

• Microsoft Access versions 1.0, 1.1, 2.0

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article describes two methods to automate the changing of a linked object's source. The first method is to use a macro, and the second method is to use OLE Automation, which is available in Microsoft Access version 2.0.

MORE INFORMATION

Macro Method

The following example demonstrates how to create and use a macro that will automatically change the source of a linked OLE object based on a file name stored in a table.

1. Create a new table with the following fields, and then save it as Linked References:

         Table: Linked References
         ------------------------
         Field Name: Key
            Data Type: Counter
         Field Name: File Name
            Data Type: Text
         Field Name: Linked Object
            Data Type: OLE Object
   
   

2. In Datasheet view, add the following records to the table. Note that the bitmaps used in this example are stored in the Microsoft Windows directory (by default, C:\WINDOWS). To embed the objects, open each bitmap in Microsoft Windows Paintbrush, select the bitmap with the selection tool, and then choose Copy from the Edit menu. Switch to Microsoft Access, place the pointer in the Linked Object field, and choose Paste Link from the Edit menu.

         Key           Filename      Linked Object
         --------------------------------------------------
         (counter)     ARCADE.BMP     Paste Link ARCADE.BMP
         (counter)     ARCHES.BMP     Paste Link ARCHES.BMP
         (counter)     ARGYLE.BMP     Paste Link ARGYLE.BMP
   
   

3. Create a new form based on the table Linked References, and add all three fields to the form.

4. Save the form as OLE Test.

5. Create a new macro with the following actions:

         In Microsoft Access version 1.x:
   

            Action           Arguments
            ----------------------------------------------------------
            GoToControl      Control Name: Linked Object
            SendKeys         Keystrokes: %eol
                             Wait: No
            SendKeys         Keystrokes: =Forms![OLE Test]![File Name]
                             Wait: No
            SendKeys         Keystrokes: {ENTER}
                             Wait: No
   
         In Microsoft Access version 2.0:
   
            Action           Arguments
            ----------------------------------------------------------
            GoToControl      Control Name: Linked Object
            SendKeys         Keystrokes: %ekl%c
                             Wait: No
            SendKeys         Keystrokes: =Forms![OLE Test]![File Name]
                             Wait: No
            SendKeys         Keystrokes: {ENTER}{ENTER}
                             Wait: No
   
   

6. Save the Macro as Update OLE Link.

7. Open the form OLE Test in Design view and select the control File Name.

8. Set the AfterUpdate property to the macro Update OLE Link.

9. To test the form, switch to Form view. Change the field File Name in the first record to LEAVES.BMP. The OLE object should update to display the bitmap LEAVES.BMP.

OLE Automation

Microsoft Access version 2.0 supports OLE Automation, a feature of the OLE 2.0 standard. You can use OLE Automation to directly access and manipulate another application's objects from outside that application.

1. Create the following subprocedure in the File Name control's AfterUpdate event procedure. The procedure uses the SourceDoc and Action properties to change the source of the linked object.

         Sub File_Name_AfterUpdate ()
           Const OLE_LINKED = 0
           Const OLE_CREATE_LINK = 1
           Const OLE_SIZE_ZOOM = 3
           [Linked Object].OLETypeAllowed = OLE_LINKED ' Object type allowed
           [Linked Object].SourceDoc = [File Name]     ' Specify source file
           [Linked Object].Action = OLE_CREATE_LINK    ' Create linked object
           [Linked Object].SizeMode = OLE_SIZE_ZOOM    ' Adjust control size
         End Sub
   
   

2. View the Linked References table in Form view.

3. Change the File Name control to REDBRICK.BMP. The OLE object should update to display the REDBRICK.BMP bitmap.

REFERENCES

Microsoft Access "User's Guide," versions 1.0 and 1.1, Chapter 13

Microsoft Access "User's Guide," version 2.0, Chapter 19