Microsoft KB Archive/113300

= ACC: How to Use DDE to Pass Information to MS Access 2.0, 95 =

Article ID: 113300

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition

-



This article was previously published under Q113300



Moderate: Requires basic macro, coding, and interoperability skills.



SUMMARY
In Microsoft Access, you cannot poke data into a table using a dynamic data exchange (DDE) channel. However, you can pass information to a Visual Basic for Applications function using a function with parameters in a DDE channel to the SQL topic. The data can then be processed by the function and added to a table.

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 the "Building Applications with Microsoft Access for Windows 95" manual.

NOTE: Visual Basic for Applications (used in Microsoft Access for Windows 95 version 7.0) is called Access Basic in version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.



MORE INFORMATION
The following example demonstrates how to add a new customer to the Customers table in the sample database Northwind.mdb (or NWIND.MDB in version 2.0) using data from Microsoft Excel and Microsoft Word for Windows:  Create a table called None with one field of any data type and one record.  Open a module and create a procedure with the parameters that you want to pass. The following Visual Basic for Applications function creates a new record in the Customers table, and accepts an argument for the CustomerID field and an argument for the CompanyName field.

NOTE: In version 2.0, there is a space in the Customer ID and the Company Name fields. Declarations Section Option Explicit

Function AddNewCust (CustomerID$, CompanyName$) Dim MyDB As Database, MyTable As Recordset Set MyDB = DBEngine.Workspaces(0).Databases(0)

' Trap any error that might occur. On Error Resume Next

' Open table. Set MyTable = MyDB.OpenRecordset("Customers", DB_OPEN_TABLE)

MyTable.AddNew                          ' Prepare new record. MyTable("CustomerID")= CustomerID$      ' Set record key.

' NOTE: The above line should read MyTable("Customer ID")= ' CustomerID$ in version 2.0.

MyTable("CompanyName") = CompanyName$   ' Set company name.

' NOTE: The above line should read MyTable("Company Name")= ' CompanyName$ in version 2.0.

MyTable.Update                          ' Save changes. MyTable.Close                           ' Close table.

' Return the error code. AddNewCust = Err

End Function This function returns 0 if successful; otherwise, a Microsoft Access error number is returned. If a record in the Customers table already exists with the specified Customer ID, error number 3022 is returned. This error would return the following error message if it were not trapped:

Duplicate value in index, primary key, or relationship.

Changes were unsuccessful.

  In the DDE client application, initiate a DDE link using MSACCESS as the application and a SQL select query as the topic. The topic should be of the form:

DatabaseName;SQL Select FunctionName(args) From None;

In the sample topic above, None is the name of the empty table, and FunctionName(args) is the call to the Visual Basic function that processes the arguments (args) passed to it.

For example, the following Microsoft Excel macro inserts a new record with "JOHNJ" as the CustomerID and "John's Place" as the Company Name into the Customers table:

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. AddNewCustomer chan=INITIATE("MSACCESS","NORTHWIND;SQL SELECT _        AddNewCust(""JOHNJ"",""John's Place"") FROM None;") =ALERT(REQUEST(chan,"FirstRow")) =TERMINATE(chan) =RETURN The following is the equivalent Microsoft Excel Visual Basic Sub procedure: Sub AddNewCustomer Chan = DDEInitiate("MSACCESS", "NORTHWIND;SQL SELECT _           AddNewCust(""TADO"",""Tad's Place"") FROM None;") Result = DDERequest(Chan, "FirstRow") Msgbox str(result(1)) DDETerminate Chan End Sub The following is the equivalent Microsoft Word for Windows Word- Basic macro: Sub MAIN var1$ = "JOHNJ" var2$ = "John's Place"

qt$ = Chr$(34)  ' create the quote character Funct$ = "AddNewCust(" + qt$ + var1$ + qt$ + "," + qt$ +_           var2$ + qt$ + ")" Chan = DDEInitiate("MSACCESS", "NORTHWIND;SQL SELECT " + _           Funct$ + " FROM None;") Result$ = DDERequest$(Chan, "FirstRow") MsgBox Result$ DDETerminate Chan End sub



How the SQL Initiate Technique Works
When you initiate a DDE link with Microsoft Access using an SQL statement, each function in the SELECT statement will run one time for each record in the table the query selects from.

In the examples above, the None table has only one record to query against. Because the table has only one record, the AddNewCust function runs only one time. If the None table had two records, the AddNewCust function would run twice, trying to add the same record two times.

The query's result will return the results of the function being called in the SELECT statement. The AddNewCust function returns the error code if an error occurs. This is useful because it lets the DDE client, such as Microsoft Excel, Word, or Visual Basic, know whether the function ran successfully or not.

