Microsoft KB Archive/101313

From BetaArchive Wiki

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


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.


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<math display="inline">(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</math> = DDERequest<math display="inline">(Chan, "Topics") DDETerminate Chan MsgBox Topics</math>, “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<math display="inline">(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</math> = 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<math display="inline">(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</math>(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<math display="inline">) EmployeeCount = Val(DDERequest</math>(Chan2, “FirstRow”)) DDETerminate Chan2 ’ Quit if there are no records. If EmployeeCount <> 0 Then Msg$ = “NWIND Employee Information:” Msg$ = Msg$ + " Record Count:" + Str<math display="inline">(EmployeeCount) MsgBox Msg</math> ’ Request the first row of data from the Employees table. Data$ = DDERequest<math display="inline">(Chan1, "FirstRow") ' Display the records. For i = 1 To EmployeeCount MsgBox Data</math> ’ 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


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