Use Microsoft Access as a DDE Server
Access Developer 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
If you need to manipulate Microsoft Access objects from another application, you may want to consider using Automation.
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.)
|
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
|
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:
|
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:
|
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:
|
See Also