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

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:

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

Additional query words: ddepoke

Keywords : kbinterop kbprg
Version : WINDOWS:2.0,7.0
Platform : WINDOWS
Issue type : kbinfo