ACC: How to Run Visual or Access Basic Functions with DDE

• Microsoft Access 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

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

Microsoft Access does not have any facility for running Visual Basic for Applications functions as a dynamic data exchange (DDE) server. However, Visual Basic functions can be run from a DDE client application if they are contained in an SQL statement used to initiate a DDE conversation with Microsoft Access using the SQL topic.

This article describes how to run Visual Basic functions from a DDE client application.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0

MORE INFORMATION

A DDE client application such as Microsoft Excel or Microsoft Word for Windows can use DDE to run commands in Microsoft Access as a DDE server. When it is used as a DDE server, Microsoft Access recognizes any of the following as a valid command:

• The name of a macro in the currently open database
• Any action that you can run in Visual Basic using the DoCmd object (or DoCmd statement in versions 1.x and 2.0).
• The OpenDatabase and CloseDatabase commands, which are used only for DDE operations

How to Run Visual Basic Functions from a DDE Client Application

The following examples demonstrate how to run Visual Basic functions from DDE client applications. In both examples below, the MyFunct() function is run against the None table in the sample database Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0).

NOTE: In the following sample code, an underscore (_) is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

This is the DDE command issued by Microsoft Excel or Word for Windows:

   chan=DDEInitiate("MSACCESS", "Northwind;SQL SELECT MyFunct() _
      FROM None;")

The following example demonstrates how to add a new customer to the Customers table in the Northwind database by running a Visual Basic function from a DDE client application:

1. Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0 and 1.x) and create a new table called None with one field of any data type.

2. Create a module and type the following line in the Declarations section:

         Option Explicit
   

3. Type the following procedure:

         Function AddNewCust$ (CustomerID$, CompanyName$)
            Dim MyDB As Database, MyRD as Recordset
            Set MyDB = CurrentDB()
            Set MyRD = MyDB.OpenRecordset("Customers") ' Open the table.
            MyRD.AddNew                                ' Prepare new record.
            MyRD("CustomerID") = CustomerID$           ' Set record key.
            MyRD("CompanyName") = CompanyName$         ' Set company name.
            MyRD.Update                                ' Save changes.
            MyRD.Close                                 ' Close the table.
         End Function
   
      NOTE: In versions 1.x and 2.0, there is a space in Customer ID
            and Company Name.
   
   

4. In the DDE client application, initiate a DDE conversation with MSACCESS as the application and an SQL statement that calls the AddNewCust() function you created in step 2 as the topic.

NOTE: In the following sample code, an underscore (_) is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

   Sub AddNewCustomer
      Dim chan as Integer
      chan=DDEInitiate("MSACCESS", "NorthWIND.MDB;SQL SELECT _
        AddNewCust$(""JOHNJ"",""John's Place"") FROM None;")
      DDETerminate chan
   End Sub

NOTE: In the following sample code, an underscore (_) is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

   Sub MAIN
      DDETerminateAll
      qt$ = Chr$(34)
      Funct$ = "AddNewCust$(" + qt$ + "JOHNJ" + qt$ + "," + qt$ + _
         "John's Place" + qt$ + ")"
      Chan = DDEInitiate("MSACCESS", _
         "Northwind;SQL SELECT " + Funct$ + "FROM None;")
      DDETerminate Chan
   End Sub

REFERENCES

For more information about using Microsoft Access as a DDE server, search for "DDE" using the Microsoft Access 97 Help Index.

Keywords          : IntpDde kbinterop
Version           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto