Microsoft KB Archive/89586

= ACC: Topics Supported by Microsoft Access as a DDE Server =

Article ID: 89586

Article Last Modified on 5/9/2003

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition

-



This article was previously published under Q89586



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 &quot;DDE, overview.&quot;



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: &quot;Busy&quot; or &quot;Ready&quot;
 * Topics: Lists all open databases

Note that any information returned from any item used with the System topic is tab delimited.

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(&quot;MSAccess&quot;, &quot;System&quot;) Topics$ = DDERequest$(Chan, &quot;Topics&quot;) DDETerminate Chan MsgBox Topics$, &quot;Topics&quot;, 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

The following sample Word for Windows WordBasic macros demonstrate how to retrieve a list of table names in the Northwind.mdb and NWIND.MDB databases:

  In version 7.0:

Chan = DDEInitiate(&quot;MSAccess&quot;, &quot;Northwind&quot;) TableList$ = DDERequest$(Chan, &quot;TableList&quot;) MsgBox TableList$, &quot;TableList&quot;, 64 DDETerminate Chan

In earlier versions:

Chan = DDEInitiate(&quot;MSAccess&quot;, &quot;Nwind&quot;) TableList$ = DDERequest$(Chan, &quot;TableList&quot;) MsgBox TableList$, &quot;TableList&quot;, 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:

  ; TABLE 

; QUERY 

; SQL 

Description of Syntax:

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

     The name of an existing table.

     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(&quot;MSAccess&quot;, &quot;Northwind;SQL&quot;) DDEPoke Chan1, &quot;SQLText&quot;, &quot;SELECT *&quot; DDEPoke Chan1, &quot;SQLText&quot;, &quot; FROM Orders&quot; DDEPoke Chan1, &quot;SQLText&quot;, &quot; WHERE [Freight] > 100;&quot; Res$ = DDERequest$(Chan1, &quot;NextRow&quot;) MsgBox Res$, &quot;NextRow&quot;, 64 DDETerminate Chan1

Note that the maximum length of a SQL SELECT statement is 255 characters in Microsoft Access 1.x and 2.0.

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:

<pre class="fixed_text">  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 &quot;SELECT * FROM table;&quot; 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 &quot;SELECT * FROM Orders;&quot; the item &quot;SQLText;7&quot; would return the following tab-delimited chunks:

SELECT * FROM Orders;

The following sample WordBasic macro demonstrates how to retrieve information from the Products table in Northwind.mdb:

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

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

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

' 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, &quot;NextRow&quot;) 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.

Note that 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 sample macro opens the Categories form, first minimized and then restored:

<pre class="fixed_text">  Chan = DDEInitiate(&quot;MSACCESS&quot;, &quot;System&quot;) AppActivate &quot;Microsoft Access&quot; DDEExecute Chan, &quot;[OpenForm Categories,,,,,2]&quot; DDEExecute Chan, &quot;[OpenForm Categories]&quot; 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.

<ul> <li>OpenDatabase <DatabaseName> [, Exclusive[, ReadOnly]]</li> <li> CloseDatabase

<pre class="fixed_text">   <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. </li></ul>

The following sample WordBasic macro demonstrates how to use the OpenDatabase actions to remotely open NWIND.MDB and then open the Employees form:

<pre class="fixed_text">  Chan = DDEInitiate(&quot;MSACCESS&quot;, &quot;System&quot;) DDEExecute Chan, &quot;[OpenDatabase Nwind.mdb]&quot; DDEExecute Chan, &quot;[OpenForm Employees,0,,,1,0]&quot; DDETerminate Chan

<div class="references_section">