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
Last Reviewed: November 6, 2000 |