Use Microsoft Access as a DDE Server

See Also        

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

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

For information on using Microsoft Access as a DDE client, see information in the Microsoft Office 2000/Visual Basic Programmer's Guide. For information about how to obtain the Microsoft Office 2000/Visual Basic Programmer's Guide, click .

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.

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.

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]

intChan1 = DDEInitiate("MSAccess", "Northwind;SQL")
DDEPoke intChan1, "SQLText", "SELECT *"
DDEPoke intChan1, "SQLText", " FROM Orders"
DDEPoke intChan1, "SQLText", " WHERE [Freight] > 100;"
strResponse = DDERequest(intChan1, "NextRow")
DDETerminate intChan1

The following table lists the valid items for the TABLE tablename, QUERY queryname, and SQL sqlstring topics.

"SELECT * FROM Orders;"

"SELECT "

"* FROM "

"Orders;"

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