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

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.