Microsoft KB Archive/100167

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

Q100167

The information in this article applies to:

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:

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

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

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 
```

Note that this technique works only with functions that perform operations that can run successfully in the Microsoft Access query by example (QBE) grid. For example, a function that performs an OpenForm action will fail with the error message "Can't run this action while in current code context." The function fails because it tries to perform an OpenForm action while a query is running, which is not allowed.

REFERENCES

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

Additional query words: dde excel macro ddepoke

Keywords : kbinterop
Issue type : kbhowto
Technology : kbAccessSearch kbAccess110 kbAccess100