ACC1x: How to Execute Macro Actions Using DDE

ID: Q99407

The information in this article applies to:

SUMMARY

Microsoft Access supports the execution of certain macro actions over a dynamic data exchange (DDE) channel.


MORE INFORMATION

The only macro actions that you can execute over a DDE channel to Microsoft Access are those supported by the DoCmd() function in Access Basic. For this reason, the following macro actions cannot be executed: 


   AddMenu
   MsgBox
   RunApp
   RunCode
   SendKeys
   SetValue
   StopAllMacros
   StopMacro

The syntax for macro actions executed through DDE is the same as the syntax for macro actions called by the DoCmd() function, that is: 

   MacroActionName Argument1,Argument2,...,ArgumentN

You cannot use the predefined constants that are available inside Access Basic, such as A_NORMAL, as arguments when you are executing macro actions with DDE. Instead, you must use the actual values of the constants. The values of the constants can be determined using the following procedure:
  1. Open a macro in Design view.


  2. Select the macro action you want to use from the list of macro actions in the Action column.


  3. In the Action Arguments section, locate the argument for which you need to supply a value.


  4. In the list box associated with that argument, locate the option that you want to use.


  5. Count down the list, starting at zero (0), until you reach that option.


The number you count to to reach the option is the number you need to supply as the argument when you execute the macro over a DDE channel.

The following example creates a macro in Microsoft Excel that demonstrates how to execute macro actions in Microsoft Access using DDE:

NOTE: This macro will not work in Microsoft Access version 1.0.
  1. Open Microsoft Access.(This macro only works if Microsoft Access is already running.)


  2. Open a new macro sheet in Microsoft Excel. Enter the following macro: 
    
      Cell    Command
      --------------------------------------------------
      A1     MacroActionDDE
      A2
      A3    chan=INITIATE("MSACCESS","SYSTEM")
      A4    =APP.ACTIVATE("Microsoft Access",FALSE)
      A5    =EXECUTE(chan,"[opendatabase nwind.mdb]")
      A6    =EXECUTE(chan,"[beep]")
      A7    =EXECUTE(chan,"[openform categories,,,,,2]")
      A8    =EXECUTE(chan,"[beep]")
      A9    =EXECUTE(chan,"[openform categories]")
      A10   =EXECUTE(chan,"[close]")
      A11   =EXECUTE(chan,"[closedatabase]")
      A12   =TERMINATE(chan)
      A13   =APP.ACTIVATE(,FALSE)
      A14   =RETURN()


  3. Size your Microsoft Access and Microsoft Excel windows so that you can see both applications at the same time.


  4. To run the macro, select cell A3, choose Run from the Macro menu, and then choose OK.


The commands in cells A3 and A4 initiate a DDE channel to Microsoft Access on the SYSTEM topic and activate the Microsoft Access application.

The command in cell A5 opens the sample database NWIND.MDB in the current working directory using the OpenDatabase pseudo action. (Pseudo actions are actions that are only valid over the DDE channel.)

The commands in cells A6 and A8 execute the Microsoft Access macro action Beep.

The command in cell A7 executes the Microsoft Access macro action OpenForm to open the Categories form in a minimized state.

The command in cell A9 executes the Microsoft Access macro action OpenForm to open the Categories form in a normal state.

The command in cell A10 closes the currently active object in Microsoft Access.

The command in cell A11 closes the currently opened database file using the CloseDatabase pseudo action.

The commands in cells A12 through A14 terminate the DDE channel, activate the Microsoft Excel application, and end the macro.


REFERENCES

Microsoft Access "Language Reference," version 1.0, pages 149-150 


Keywords          : kbinterop 
Version           : 1.1
Platform          : WINDOWS 
Issue type        : kbhowto

Last Reviewed: March 22, 1999