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

ID: Q109397

The information in this article applies to:

Microsoft Access versions 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

Note that Microsoft Access does not have DDE server functionality for running Visual Basic functions.

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 <tablename>;")

Note: You should replace <tablename> with the name of any table that exists in the database.

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:

Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0 and 1.x).

Create a module and type the following line in the Declarations section:
Option Explicit

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.

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. If you are using Access 2.0 as you DDE server, you will have to have Access open with the Northwind (Nwind.mdb) database active for this code to work.

The following example demonstrates how to run the AddNewCust() function from Microsoft Excel versions 5.0 and 7.0, Microsoft Excel 97, and Microsoft Word 97:

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 Shippers;")
      DDETerminate chan
   End Sub

Note: In the DDEInitiate statement above, you will have to replace the word Northwind with the actual path to the Northwind database. DDE does not support long file names, so the DOS alias names must be used for directory names longer than eight characters. (i.e. C:\mydocu~1\file.mdb)

The following example demonstrates how to run the AddNewCust() function from Word for Windows versions 6.0 and 7.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.


   Sub MAIN
      DDETerminateAll
      qt$ = Chr$(34)
      Funct$ = "AddNewCust$(" + qt$ + "JOHNJ" + qt$ + "," + qt$ + _
         "John's Place" + qt$ + ")"
      Chan = DDEInitiate("MSACCESS", _
         "Northwind;SQL SELECT " + Funct$ + "FROM Shippers;")
      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.

Additional query words:

Keywords : kbinterop
Version : WINDOWS:1.0,1.1,2.0,7.0,97
Platform : WINDOWS
Issue type : kbhowto