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

• Microsoft Access versions 2.0, 7.0

SUMMARY

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

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:

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

2. 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.
   
   

3. 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.

REFERENCES

For more information about "Using Microsoft Access for Windows 95 as a DDE Server", search for "DDE," and then "Overview" using the Microsoft Excel, Word, or Visual Basic Help Index

For more information about implementing DDE in version 1.x, please see the following article here in the Microsoft Knowledge Base:

   ARTICLE-ID: Q100167
   Title     : ACC1x: How to Use DDE to Pass Information to MS Access 1.x