ACC1x: How to Use DDE to Pass Information to MS Access 1.x

• Microsoft Access versions 1.0, 1.1

SUMMARY

You cannot poke data into a table through a dynamic data exchange (DDE) channel in Microsoft Access. However, you can use a function with parameters in a DDE channel to the SQL topic to pass information to an Access Basic function. The data can then be processed by the function and added to a table.

This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information on Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x, or the "Building Applications" manual, Chapter 3, "Introducing Access Basic" in version 2.0.

MORE INFORMATION

The following example explains how to add a new customer to the Customers table in the sample database NWIND.MDB, using data from Microsoft Excel:

1. Create a table called None, with one field of any data type.

2. Open a module and create the following sample function with the parameters that you want to pass:

         Function AddNewCust$ (CustomerID$, CompanyName$)
            Dim MyDB As Database, MyTable As Table
            Set MyDB = CurrentDB()
   
            Set MyTable = MyDB.OpenTable("Customers")  ' Open table.
   
            MyTable.AddNew                             ' Prepare new record.
            MyTable("Customer ID") = CustomerID$       ' Set record key.
            MyTable("Company Name") = CompanyName$     ' Set company name.
            MyTable.Update                             ' Save changes.
            MyTable.Close                              ' Close table.
         End Function
   
   

3. In the DDE client application, initiate a DDE link using MSACCESS as the application and use a SQL Select query as the topic. The topic will be of the form

         DatabaseName;SQL Select FunctionName(args) From None;
   

   where None is the empty table and FunctionName(args) is the call to the Access Basic function that processes the arguments, args, passed to it.

   For example, the following Microsoft Excel macro inserts a new record into the Customer table.

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

         AddNewCustomer
         chan=INITIATE("MSACCESS","NWIND.MDB;SQL SELECT_
              AddNewCust$(""JOHNJ"",""John's Place"") FROM None; ")
         =TERMINATE(chan)
         =RETURN()
   
      The following is the equivalent Microsoft Word for Windows Word Basic
      macro.
   
      NOTE: In the following sample code, an underscore (_) at the end of a
      line 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
         qt$ = Chr$(34)
         Funct$ = "AddNewCust$(" + qt$ + "JOHNJ" + qt$ + "," + qt$ + _
         "John's Place" + qt$ + ")"
         Chan = DDEInitiate("MSACCESS", "NWIND;SQL SELECT " + Funct$ + _
            " FROM None;")
         DDETerminate Chan
         End Sub
   
   

REFERENCES

Microsoft Access "Language Reference," version 1.0, pages 118-124