Microsoft KB Archive/101313

INF: Using Microsoft Access as a DDE Server

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

1.00 1.10 WINDOWS

= 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: ; 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, NWIND) or its full path  and .MDB extension (for example, C:\ACCESS\NWIND.MDB).  The name of an existing table.  The name of an existing query.  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; An SQL statement in -character chunks that represents the table or query, where  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$ + &quot; Record Count:&quot; + 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  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.