How to Perform Microsoft Access Macro Action Via DDE from VB 3.0 (112767)

SUMMARY

From Visual Basic, you cannot directly use a form or report that was created by the Microsoft Access engine. This article shows by example how to use DDE to do it indirectly. The example prints one of the built-in reports from the NWIND.MDB sample database by using DDE and the OpenReport macro action in Microsoft Access.

MORE INFORMATION

For more information about Microsoft Access macro actions, please see the Microsoft Access documentation or Help menu. A Visual Basic application can call most of these actions by using DDE.

Step-by-Step Example

Start a new project in Visual Basic. Form1 is created by default.
Add a text box (Text1) and Command button (Command1) to Form1.

Place the following code in the Command1 button's click event:

   ' Note the time-out has to be long enough to allow for the print
   ' to complete or an error will occur.
   Sub Command1_Click ()
      Text1.LinkTimeout = 600 'Set DDE Time-out for 60 Seconds
      Text1.LinkTopic = "MSACCESS|SYSTEM"
      Text1.LinkMode = 2  ' Establish manual DDE link to Microsoft Access.
      Text1.LinkExecute "[OPENREPORT Catalog]" 'Open and Print     Report
      'Uncomment the next line to show a report with a where clause
      'Text1.LinkExecute "[OpenReport Freight Charges,,, [Order ID]=10506]"
      Text1.LinkMode = 0  ' Terminate the DDE link to Microsoft Access
   End Sub

Please note that for Microsoft Access version 1.1, the link topic needs to be ACCESS|SYSTEM.

Start Microsoft Access and open the NWIND.MDB sample database.

NOTE: It is very important that the database NOT be opened for Exclusive access. If this is done, the example will fail and an error 282 ("No foreign application has responded...") will be generated. Exclusive can be inherited from a previous open if multiuser is not explicitly specified.
Run the Visual Basic program, and click the Command1 button.