INF: Using Microsoft Access as a DDE Server

PSS ID Number: Q101313
Article last modified on 08-22-1993

1.00 1.10
WINDOWS

---------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Access versions 1.0 and 1.1
 - Microsoft Access Distribution Kit version 1.1
---------------------------------------------------------------------

SUMMARY
=======
Microsoft Access supports dynamic data exchange (DDE) as both a
destination (client) application and a source (server) application.
This article documents the DDE topics that Microsoft Access supports
as a DDE server and the valid DDE items for each topic.
NOTE: In this article, an asterisk (*) indicates a new feature in
Microsoft Access version 1.1.
The examples below require that you have the sample database NWIND.MDB
open in Microsoft Access. 

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
This article contains descriptions of the five topics and the items
that are supported in each topic.

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  - A list of items supported by the System topic in
                Microsoft Access
    Formats   - A list of formats Microsoft Access can copy to the
                Clipboard
    Status    - "Busy" or "Ready"
    Topics    - A list of all open databases
Information returned from any item used with the System topic is
tab delimited, Chr$(9).
The following Microsoft Word for Windows WordBasic macro demonstrates
how to use the System topic to get information on 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 filename 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, Chr$(9).
NOTE: You cannot query the SYSTEM.MDB file using DDE.
The database topic supports the following items:
   TableList  
   QueryList  
   FormList   
   ReportList 
   MacroList  
   ModuleList 
The following WordBasic macro demonstrates how to retrieve a list of
table names:
   Chan = DDEInitiate("MSAccess", "NWIND")
   TableList$ = DDERequest$(Chan, "TableList")
   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. This list is tab-delimited, Chr$(9).
The syntax for these topics is as follows:
   <DatabaseName>; TABLE <TableName>
   <DatabaseName>; QUERY <QueryName>
   <DatabaseName>; SQL <SqlString>
Description of Syntax:
   <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, NWIND) or its full path 
                    and .MDB extension (for example, C:\ACCESS\NWIND.MDB).
   <TableName>      The name of an existing table.
   <QueryName>      The name of an existing query.
   <SqlString>      A valid SQL statement up to 255 characters,
                    followed by a semicolon.
   *NOTE: You can exchange more than 255 characters by omitting this
   argument 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:
      Chan = DDEInitiate("MSAccess", "NWIND;SQL")
      DDEPoke Chan, "SQLText", "SELECT * FROM Orders "
      DDEPoke Chan, "SQLText", "WHERE [Order Amount] > 1000;"
      Results = DDERequest$(Chan, "Data")
      DDETerminate Chan
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 records of data, the first a list of field
                    names and the second a list of data types.
                    The following are the values returned and the data 
                    type each value represents:
                        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 for
                    PrevRow 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
                    format "SELECT * FROM table;".
   *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, suppose a query is 
		   represented by the following SQL statement:
                       SELECT * FROM Orders;
                    The item "SQLText;7" would return the following
                    tab-delimited chunks:
                       "SELECT "
                       "* FROM "
                       "Orders;"
The following WordBasic macro demonstrates how to get information from
the Employees table in NWIND.MDB:
    Chan1 = DDEInitiate("MSAccess", "NWIND;TABLE Employees")
    ' Get a count of the number of Employee records.
    SQL$ = "SELECT Count([Employee ID]) AS [CountOfEmployees] "
    SQL$ = SQL$ + "FROM Employees;"
    Chan2 = DDEInitiate("MSAccess", "NWIND;SQL " + SQL$)
    EmployeeCount =  Val(DDERequest$(Chan2, "FirstRow"))
    DDETerminate Chan2
    ' Quit if there are no records.
    If EmployeeCount <> 0 Then
       Msg$ = "NWIND Employee Information:"
       Msg$ = Msg$ + "       Record Count:" + Str$(EmployeeCount)
       MsgBox Msg$
       ' Request the first row of data from the Employees table.
       Data$ = DDERequest$(Chan1, "FirstRow")
       ' Display the records.
       For i = 1 To EmployeeCount
           MsgBox Data$
           ' Get the next row of data, if not at the end.
           If i <> EmployeeCount Then
               Data$ = DDERequest$(Chan1, "NextRow")
           End If
       Next i
    End If

Executing 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 execute a
command. Microsoft Access recognizes any of the following valid
commands:
 - The name of a macro in the database that is currently open. A macro
   can be executed on a channel with any of the five topics described
   above.
*- Any action that you can execute in Access Basic using the DoCmd()
   statement. You cannot execute the following macro actions: AddMenu,
   MsgBox, RunApp, RunCode, SendKeys, SetValue, StopAllMacros,
   and StopMacro.
*- The OpenDatabase and CloseDatabase actions, executed only for DDE
   operations using the System topic.
NOTE: When you specify an action as a DDEExecute command, the action
and any arguments follow the DoCmd() syntax and must be enclosed in
brackets ([]). However, applications that support DDE do not recognize
intrinsic constants, such as A_NORMAL, in DDE operations. Therefore,
you must use the actual number as an argument. Also, string arguments
must be enclosed in quotation marks only if the string contains a
comma. Otherwise, quotation marks are not required.
The following 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
--------------------------------------------------
Microsoft Access 1.1 introduces some new commands that can be executed
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.
The following WordBasic macro demonstrates how to use the OpenDatabase
actions to remotely open NWIND.MDB and then open the Employees form:
   Chan = DDEInitiate("MSACCESS", "System")
   DDEExecute Chan, "[OpenDatabase NWIND.MDB]"
   DDEExecute Chan, "[OpenForm Employees,0,,,1,0]"
   DDETerminate Chan

REFERENCES
==========
"User's Guide," Chapters 9 and 13 
README.TXT for Microsoft Access version 1.0

For more information on using DDE with Microsoft Access, query on the
following words here in the Microsoft Knowledge Base
   dde and <the name of the other application in the conversation>
or search for "DDE" using the Microsoft Access Help menu.

Additional reference words: 1.00 1.10 Dynamic Data Exchange container
KBCategory:
KBSubcategory: IntropDdesvr IntropOthr GnrlNw
Copyright Microsoft Corporation 1993.