Use Microsoft Access as a DDE Server [Access 2003 VBA Language Reference]
Microsoft Access supports dynamic data exchange (DDE) as either a destination (client) application or a source (server) application. For example, an application such as Microsoft Word, acting as a client, can request data through DDE from a Microsoft Access database that's acting as a server.
Tip
A DDE conversation between a client and server is established on a particular topic. A topic can be either a data file in the format supported by the server application, or it can be the System topic, which supplies information about the server application itself. Once a conversation has begun on a particular topic, only a data item associated with that topic can be transferred.
For example, suppose you are running Microsoft Word and want to insert data from a particular Microsoft Access database into a document. You begin a DDE conversation with Microsoft Access by opening a DDE channel with the DDEInitiate function and specifying the database file name as the topic. You can then transfer data from that database to Microsoft Word through that channel.
As a DDE server, Microsoft Access supports the following topics:
- The System topic
- The name of a database (database topic)
- The name of a table (tablename topic)
- The name of a query (queryname topic)
- A Microsoft Access SQL string (sqlstring topic)
Once you've established a DDE conversation, you can use the DDEExecute statement to send a command from the client to the server application. When used as a DDE server, Microsoft Access recognizes any of the following as a valid command:
- The name of a macro in the current database.
- Any action that you can carry out in Visual Basic by using one of the methods of the DoCmd object.
- The OpenDatabase and CloseDatabase actions, which are used only for DDE operations. (For an example of how to use these actions, see the example later in this topic.)
Note When you specify a macro action as a DDEExecute statement, the action and any arguments follow the DoCmd object syntax and must be enclosed in brackets ([ ]). However, applications that support DDE don't recognize intrinsic constants in DDE operations. Also, string arguments must be enclosed in quotation marks (" ") if the string contains a comma. Otherwise, quotation marks aren't required.
The client application can use the DDERequest function to request text data from the server application over an open DDE channel. Or the client can use the DDEPoke statement to send data to the server application. Once the data transfer is complete, the client can use the DDETerminate statement to close the DDE channel, or the DDETerminateAll statement to close all open channels.
Note When your client application has finished receiving data over a DDE channel, it should close that channel to conserve memory resources.
The following example demonstrates how to create a Microsoft Word procedure with Visual Basic that uses Microsoft Access as a DDE server. (For this example to work, Microsoft Access must be running.)
Sub AccessDDE()
Dim intChan1 As Integer, intChan2 As Integer
Dim strQueryData As String
' Use System topic to open Northwind sample database.
' Database must be open before using other DDE topics.
intChan1 = DDEInitiate("MSAccess", "System")
' You may need to change this path to point to actual location
' of Northwind sample database.
DDEExecute intChan1, "[OpenDatabase C:\Access\Samples\Northwind.mdb]"
' Get all data from Ten Most Expensive Products query.
intChan2 = DDEInitiate("MSAccess", "Northwind.mdb;" _
& "QUERY Ten Most Expensive Products")
strQueryData = DDERequest(intChan2, "All")
DDETerminate intChan2
' Close database.
DDEExecute intChan1, "[CloseDatabase]"
DDETerminate intChan1
' Print retrieved data to Debug Window.
Debug.Print strQueryData
End Sub
The following sections provide information about the valid DDE topics supported by Microsoft Access.
The System Topic
The System topic is a standard topic for all Microsoft Windows–based applications. It supplies information about the other topics supported by the application. To access this information, your code must first call the DDEInitiate function with "System" as the topic argument, and then execute the DDERequest statement with one of the following supplied for the item argument.
| Item | Returns |
|---|---|
| SysItems | A list of items supported by the System topic in Microsoft Access. |
| Formats | A list of the formats Microsoft Access can copy onto the Clipboard. |
| Status | "Busy" or "Ready". |
| Topics | A list of all open databases. |
The following example demonstrates the use of the DDEInitiate and DDERequest functions with the System topic:
' In Visual Basic, initiate DDE conversation with Microsoft Access.
Dim intChan1 As Integer, strResults As String
intChan1 = DDEInitiate("MSAccess", "System")
' Request list of topics supported by System topic.
strResults = DDERequest(intChan1, "SysItems")
' Run OpenDatabase action to open Northwind.mdb.
' You may need to change this path to point to actual location
' of Northwind sample database.
DDEExecute intChan1, "[OpenDatabase C:\Access\Samples\Northwind.mdb]"
The database Topic
The database topic is the file name of an existing database. You can type either just the base name (Northwind), or its path and .mdb extension (C:\Access\Samples\Northwind.mdb). After you start a DDE conversation with the database, you can request a list of the objects in that database.
Note You can't use DDE to query the Microsoft Access workgroup information file.
The database topic supports the following items.
| Item | Returns |
|---|---|
| TableList | A list of tables. |
| QueryList | A list of queries. |
| FormList | A list of forms. |
| ReportList | A list of reports. |
| MacroList | A list of macros. |
| ModuleList | A list of modules. |
| ViewList | A list of views |
| StoredProcedureList | A list of stored procedures |
| DatabaseDiagramList | A list of database diagrams |
The following example shows how you can open the Employees form in the Northwind sample database from a Visual Basic procedure:
' In Visual Basic, initiate DDE conversation with
' Northwind sample database.
' Make sure database is open.
intChan2 = DDEInitiate("MSAccess", "Northwind")
' Request list of forms in Northwind sample database.
strResponse = DDERequest(intChan2, "FormList")
' Run OpenForm action and arguments to open Employees form.
DDEExecute intChan2, "[OpenForm Employees,0,,,1,0]"
The TABLE tablename, QUERY queryname, and SQL sqlstring Topics
These topics use the following syntax:
databasename**; TABLE**tablename
databasename**; QUERY**queryname
databasename**; SQL [sqlstring]**
| Part | Description |
|---|---|
| databasename | The name of the database that the table or query is in or that the SQL statement applies to, followed by a semicolon (;). The database name can be just the base name (Northwind) or its full path and .mdb extension (C:\Access\Samples\Northwind.mdb). |
| tablename | The name of an existing table. |
| queryname | The name of an existing query. |
| sqlstring | A valid SQL statement up to 256 characters long, ending with a semicolon. To exchange more than 256 characters, omit this argument and instead use successive DDEPoke statements to build an SQL statement.
For example, the following Visual Basic code uses the DDEPoke statement to build an SQL statement and then request the results of the query. |
|
The following table lists the valid items for the TABLE tablename, QUERY queryname, and SQL sqlstring topics.
| Item | Returns |
|---|---|
| All | All the 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 | A two-row list of field names (first row) and their data types (second row). |
| These are the values returned and the data types they represent: | |
| Value | |
| 0 | |
| 1 | |
| 2 | |
| 3 | |
| 4 | |
| 5 | |
| 6 | |
| 7 | |
| 8 | |
| 9 | |
| 10 | |
| 11 | |
| 12 | |
| NextRow | The data in the next row in the table or query. When you open a channel, NextRow returns the data in the first row. If the current row is the last record and you run 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 form "SELECT * FROM table;". |
| SQLText;n | An SQL statement, in n-character chunks, representing the table or query, where n is an integer up to 256. For example, suppose a query is represented by the following SQL statement:
The item "SQLText;7" returns the following tab-delimited chunks: |
The following example shows how you can use DDE in a Visual Basic procedure to request data from a table in the Northwind sample database and insert that data into a text file:
Sub NorthwindDDE
Dim intChan1 As Integer, intChan2 As Integer, intChan3 As Integer
Dim strResp1 As Variant, strResp2 As Variant, strResp3 As Variant
' In a Visual Basic module, get data from Categories table,
' Catalog query, and Orders table in Northwind.mdb.
' Make sure database is open first.
intChan1 = DDEInitiate("MSAccess", "Northwind;TABLE Shippers")
intChan2 = DDEInitiate("MSAccess", "Northwind;QUERY Catalog")
intChan3 = DDEInitiate("MSAccess", "Northwind;SQL SELECT * " _
& "FROM Orders " _
& "WHERE OrderID > 10050;")
strResp1 = DDERequest(intChan1, "All")
strResp2 = DDERequest(intChan2, "FieldNames;T")
strResp3 = DDERequest(intChan3, "FieldNames;T")
DDETerminate intChan1
DDETerminate intChan2
DDETerminate intChan3
' Insert data into text file.
Open "C:\DATA.TXT" For Append As #1
Print #1, strResp1
Print #1, strResp2
Print #1, strResp3
Close #1
End Sub
See Also | DDE Function | DDEInitiate Function | DDEPoke Statement | DDERequest Function | DDETerminate Statement | DDETerminateAll Statement | DEExecute Statement