Use Microsoft Query with other programs

Some of the content in this topic may not be applicable to some languages.

Typically, other programs use Microsoft Query to accomplish three main tasks:

To create a new query
To update existing data
To modify the query definition or data

Microsoft Query as a data query tool for users

Using DDE, you can have Microsoft Query function as a tool for retrieving data. Once your macro or program opens a DDE channel, it can activate Microsoft Query and specify which command replaces Exit on the File menu in Microsoft Windows or Quit on the File menu on the Macintosh, what program state (minimized [Windows only], maximized, or restored) Microsoft Query is in, and whether to display a series of dialog boxes.

A user can then create, open, or edit a query as if Microsoft Query were running as a standalone program. When the user has finished building the query, the user can click the custom Exit command or click the Exit button on the toolbar.

Once control returns to the destination program (the document, worksheet, or other object) data can be requested from Microsoft Query. For example, the user may request the number of rows or columns in the query, the SQL string defining the query, the connection string for the data source, or the data source name. Data can be returned to the destination program before the DDE channel is closed and the macro or program ends.

Update retrieved data

After you have executed a query and returned a result set, you may later want to update the data with whatever is then current in the data source. Using DDE, you can update data without input from the user: A program can send an SQL statement to Microsoft Query, which can execute the query and then return the result set to the destination program.

Modify a query definition or data

Another way a program can use Microsoft Query is to allow a user to modify an existing query definition in Microsoft Query or to edit data in a data source.

A program starts this process by opening a DDE channel, connecting to a data source, and sending an SQL string (saved from the previous query) to Microsoft Query to build a new query. Once the program has opened the new query, user control begins, and the user can modify the query or edit the data as needed. Once the user exits Microsoft Query, the data is returned to the program, the new query definition is saved, and the query is closed.

For example, a user working in Microsoft Word could choose a custom command called "Get Sales Figures" to start the corresponding macro. Without the user being aware of it, this macro could then start Microsoft Query, build a query, and then return the sales data to the Word document.