ACC: Topics Supported by Microsoft Access as a DDE Server

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

Microsoft Access supports dynamic data exchange (DDE) as both a client (destination) and a server (source) application. This article lists the DDE topics that Microsoft Access supports as a DDE server and the valid DDE items for each topic.

NOTE: All the examples in this article require that you have the sample database Northwind.mdb in Microsoft Access version 7.0 (or NWIND.MDB in earlier versions) open.

NOTE: For information about using Microsoft Access as a DDE server in Microsoft Access 97, search the Help Index for "DDE, overview."

MORE INFORMATION

As a DDE server, Microsoft Access supports the following topics:

• The System topic
• The name of a database, table, or query
• A Microsoft Access SQL statement

The System Topic

The System topic is a standard topic for all Microsoft Windows-based DDE server applications, and returns information about the topics supported by the application. The System topic supports the following Microsoft Access data items:

• SysItems: Lists items supported by the System topic in Microsoft Access
• Formats: Lists formats Microsoft Access can copy to the Clipboard
• Status: "Busy" or "Ready"
• Topics: Lists all open databases

The following sample Microsoft Word for Windows WordBasic macro demonstrates how to use the System topic to get information about available topics from Microsoft Access:

   Chan = DDEInitiate("MSAccess", "System")
   Topics$ = DDERequest$(Chan, "Topics")
   DDETerminate Chan
   MsgBox Topics$, "Topics", 64

The Database Topic

The database topic is the file name of an existing database. After you initiate a DDE conversation with the database, you can request a list of objects in that database. This list of information is TAB delimited.

NOTE: You cannot use DDE to query the Microsoft Access Workgroup Information file (System.mda in Microsoft Access versions 1.x and 2.0).

The database topic supports the following items:

• TableList
• QueryList
• FormList
• ReportList
• MacroList
• ModuleList

   In version 7.0:

      Chan = DDEInitiate("MSAccess", "Northwind")
      TableList$ = DDERequest$(Chan, "TableList")
      MsgBox TableList$, "TableList", 64
      DDETerminate Chan

   In earlier versions:

      Chan = DDEInitiate("MSAccess", "Nwind")
      TableList$ = DDERequest$(Chan, "TableList")
      MsgBox TableList$, "TableList", 64
      DDETerminate Chan

The TABLE TableName, QUERY QueryName, and SQL SQLString Topics

The TABLE, QUERY, and SQL DDE topics are used to retrieve information from Microsoft Access tables. The returned information is TAB delimited. The syntax for these topics is as follows:

   <DatabaseName>; TABLE <TableName>

   <DatabaseName>; QUERY <QueryName>

   <DatabaseName>; SQL <SQLString>

   <DatabaseName>   The name of the database to which the table or query
                    belongs or the SQL statement applies, followed by
                    a semicolon (;). The database name can be either the
                    base name only (for example, Northwind or Nwind), or
                    its full path and .mdb extension (for example,
                    C:\Msoffice\Access\Samples\Northwind.mdb or
                    C:\Access\Nwind.mdb).

   <TableName>      The name of an existing table.

   <QueryName>      The name of an existing query.

   <SQLString>      A valid SQL SELECT statement of up to 256 characters,
                    ending with a semicolon (;). Note that you can
                    exchange more than 256 characters by omitting the
                    <SQLString> statement and using successive DDEPoke()
                    statements to build an SQL statement. For example, the
                    following WordBasic code uses the DDEPoke() function
                    to build an SQL statement and request the results of
                    the query:

                     Chan1 = DDEInitiate("MSAccess", "Northwind;SQL")
                     DDEPoke Chan1, "SQLText", "SELECT *"
                     DDEPoke Chan1, "SQLText", " FROM Orders"
                     DDEPoke Chan1, "SQLText", " WHERE [Freight] > 100;"
                     Res$ = DDERequest$(Chan1, "NextRow")
                     MsgBox Res$, "NextRow", 64
                     DDETerminate Chan1

Note that you cannot initiate a DDE link with Microsoft Access using an action query. Action queries, such as append (INSERT INTO), update (UPDATE...SET), Delete (DELETE...FROM), and make-table (SELECT...INTO) perform an action on data. A select query, which finds and returns data, can be used as the topic of a DDE conversation.

NOTE: In this article, an asterisk (*) indicates a feature that is not available in Microsoft Access version 1.x. You can perform SQL action queries using DDE by running the RunSQL macro action using the DDEExecute() function.

The following is a list of valid DDE items for the TABLE TableName, QUERY QueryName, and SQL SQLString DDE topics:

   All              All data in the table, including field names.

   Data             All rows of data, without field names.

   *FieldNames      A single-row list of field names.

   FieldNames;T     Two data records, the first a list of field names,
                    and the second a list of data types. The data types
                    are:

                     0    Invalid
                     1    True/False (non-NULL)
                     2    Unsigned byte (Byte)
                     3    2-byte signed integer (Integer)
                     4    4-byte signed integer (Long)
                     5    8-byte signed integer (Currency)
                     6    4-byte single-precision floating point (Single)
                     7    8-byte double-precision floating point (Double)
                     8    Date/Time (date is integer, time is fraction)
                     9    Binary data, 255-byte maximum
                    10    ANSI text, not case sensitive, 255-byte
                          maximum (Text)
                    11    Long binary (OLE Object)
                    12    Long text (Memo)

   NextRow          The data in the next row in the table or query.
                    When you first open a channel, NextRow returns the
                    data in the first row. If the current row is the last
                    record and you execute NextRow, the request fails.

   PrevRow          The data in the previous row in the table or query.
                    If PrevRow is the first request on a new channel, the
                    data in the last row of the table or query is
                    returned. If the first record is the current row, the
                    request fails.

   FirstRow         The data in the first row of the table or query.

   LastRow          The data in the last row of the table or query.

   FieldCount       The number of fields in the table or query.

   *SQLText         An SQL statement representing the table or query.
                    For tables, this item returns an SQL statement in the
                    "SELECT * FROM table;" format.

   *SQLText;<n>     An SQL statement in <n>-character chunks that
                    represents the table or query, where <n> is an
                    integer lower than 255. For example, for a query
                    represented by the SQL statement "SELECT * FROM
                    Orders;" the item "SQLText;7" would return the
                    following tab-delimited chunks:

                       SELECT
                       * FROM
                       Orders;

   Sub MAIN
      Chan1 = DDEInitiate("MSAccess", "Northwind;TABLE Products")
      'Get a count of the number of Product records where the
      'Product ID is less than 10.
      SQL$ = "SELECT Count([Product ID]) AS [CountOfProducts] "
      SQL$ = SQL$ + "FROM Products WHERE [Product ID] < 10;"
      Chan2 = DDEInitiate("MSAccess", "Northwind;SQL " + SQL$)
      ProductCount =  Val(DDERequest$(Chan2, "FirstRow"))
      DDETerminate Chan2

      ' Quit if there are no records.
      If ProductCount <> 0 Then
         Msg$ = "Northwind Product Information:"
         Msg$ = Msg$ + "       Record Count:" + Str$(ProductCount)
         MsgBox Msg$

         ' Request the first row of data from the Products table.
         Data$ = DDERequest$(Chan1, "FirstRow")

         ' Display the records.
         For i = 1 To ProductCount
            MsgBox Data$
            ' Get the next row of data, if not at the end.
            If i <> ProductCount Then
               Data$ = DDERequest$(Chan1, "NextRow")
            End If
         Next i
      End If

   End Sub

Running Macros and Commands in Microsoft Access Using DDE

When you are using Microsoft Access as a DDE server, you can use the DDEExecute() function to instruct your application to carry out a command. Microsoft Access recognizes any of the following valid commands.

NOTE: In this article, an asterisk (*) indicates a feature that is not available in Microsoft Access version 1.x.

• The name of a macro in the currently open database. A macro can be run on a channel with any of the five topics described above.
• Any action that you can run in Access Basic using the DoCmd() statement. You cannot run the following macro actions: AddMenu, MsgBox, RunApp, RunCode, SendKeys, SetValue, StopAllMacros, and StopMacro.
• The OpenDatabase and CloseDatabase actions, run only for DDE operations using the System topic.

The following sample macro opens the Categories form, first minimized and then restored:

   Chan = DDEInitiate("MSACCESS", "System")
   AppActivate "Microsoft Access"
   DDEExecute Chan, "[OpenForm Categories,,,,,2]"
   DDEExecute Chan, "[OpenForm Categories]"
   DDETerminate Chan

Using the OpenDatabase and CloseDatabase Commands

The following commands can be carried out on a channel opened to the System topic. These commands facilitate the remote opening and closing of databases in Microsoft Access from the client application.

• OpenDatabase <DatabaseName> [, Exclusive[, ReadOnly]]
• CloseDatabase

      <DatabaseName>   A string expression that is the name of an existing
                       database. This can include the fully qualified
                       MS-DOS path.
  
      [Exclusive]      A Boolean value that is True (-1) if the database is
                       to be opened with exclusive (nonshared) access and
                       False (0) if the database is to be opened with shared
                       access. The default is shared access.
  
      [ReadOnly]       A Boolean value that is True if the database is to be
                       opened with read-only access and False if it is to be
                       opened with read/write access. The default is
                       read/write access.
  
  

   Chan = DDEInitiate("MSACCESS", "System")
   DDEExecute Chan, "[OpenDatabase Nwind.mdb]"
   DDEExecute Chan, "[OpenForm Employees,0,,,1,0]"
   DDETerminate Chan

REFERENCES