Request status and other information
Once you've opened a DDE channel to Microsoft Query, you can return a result set or other information using DDERequest.
The DDERequest method has two arguments.
| Argument | Description |
|---|---|
| channel | The DDE channel value returned by DDEInitiate. |
| request_item | The data to be returned. This data can be the contents of a field or range of fields in a query window, or other types of information, such as the number of records in a query window. |
Request items can be requested on both the query channel and the system channel. On the query channel, you can return information only about the query, such as the number of rows or columns, or the query definition. On the system channel, you can return general information about Microsoft Query, such as the current connections, topic names, and program status, as well as information specific to the active query.
Request items for a query channel or the active query on a system channel can be one of the following.
| Request item | Information returned |
|---|---|
| ColumnTitles | An array of column titles. The column title is the same as the field name, unless the user has modified the column title in Microsoft Query. |
| ConnectionString | An array of connection string segments (each of which can be up to 255 characters long) that makes up the connection string used to connect to the active query window. If the query was created from a File Data Source Name (DSN), this item returns a DSN-less connection string. |
| ConnectionString/n | An array of connection string segments (each of which can be up to n characters long) that makes up the connection string used to connect to the active query window. If the query was created from a File DSN, this item returns a DSN-less connection string. |
| DataSourceName | The data source name (DSN) used by the active query. |
| ErrorText | The error text, if any, from the most recently executed SQL statement. This item can be requested multiple times to retrieve all the errors that have occurred. After all errors are retrieved, the item returns an empty string. The item returns a null string if the most recently executed SQL statement was successful, even if previously executed SQL statements were not successful. |
| FieldDef | An array of values that describes the expressions and data types of the columns (fields) in the query window. Each row of the array describes a field in the query. The array returns the following data in columnar format: the field name, the field Q+E data type, the field width, the field precision, and the field SQL data type. |
| GetUniqueItems | An array of the unique items in a given column of the query results. The actual request item must be the zero-based column index of the column appended to "GetUniqueItems" (for example, the unique items in the second column are returned by "GetUniqueItems1"). You cannot request the unique items for a column not included in the query.
This item returns the same values as a SELECT DISTINCT statement for the requested column, with any joins and criteria clauses in effect. Any criteria clauses that use parameters are not included. |
| NameSeparator | The single character used as the ODBC qualifier name separator. |
| NumCols | The number of columns (fields) in the query. |
| NumRows | The number of rows (records) in the query. |
| ODBCSQLStatement | An array of SQL segments (each of which can be up to 255 characters long) that makes up the ODBC SQL statement for the query. With this string, you can bypass Microsoft Query and send an ODBC SQL statement directly to ODBC for processing. |
| ODBCSQLStatement/n | An array of SQL segments (each of which can be up to n characters long) that makes up the ODBC SQL statement for the query. You can use this request item to store an ODBC SQL statement in several smaller segments. |
| ParameterNames | An array of parameters in the query. An empty array is returned if no parameters exist. This item can be requested at any time, even if parameters are disabled for the query. |
| Query | The name of the query. |
| QueryDefinition | An array of query definition segments (each of which can be up to 255 characters long) that makes up the definition of the query (an SQL statement), as defined by Microsoft Query. This item can be retrieved and saved for future queries. |
| QueryDefinition/n | An array of query definition segments (each of which can be up to n characters long) that makes up the query definition of the query (an SQL statement). |
| Recest | A rough estimate of how many rows can be retrieved at a time. |
| TierType | A single digit specifying the type of data source: 1 indicates a file that can contain only a single table; 2 indicates a file for a database containing one or more tables; 3 indicates the data can't be browsed. This request item is used if the program needs to provide an Open dialog box for browsing the data on a disk. |
When you use the above request items on the system channel, information is returned for the query window most recently used in a DDE operation.
For the system channel, in addition to the above request items, you can also request the following items.
| Request item | Information returned |
|---|---|
| Sources | All current data source connections (DSN only). |
| Logon | All remote databases you can connect to (DSN entries from the registry). |
| Logoff | All currently connected remote databases (DSN only). |
| Tables/source/user/database | All tables for the specified DSN connection, user, and database. Note that the source must be a valid DSN. |
| Users/source/database | All users for the specified DSN connection and database. Note that the source must be a valid DSN. |
| Database/source | All databases for the specified DSN connection. Note that the source must be a valid DSN. |
| Username/source/database | The user name for the specified DSN connection and database. Note that the source must be a valid DSN. |
| Topics | The names of the topics open on the system channel, along with System. |
| Status | The status of the program, which can be Ready or Busy. Ready means that the program can have a DDE conversation with Microsoft Query. Busy means that Microsoft Query isn't ready to have a DDE conversation. |
This example inserts the value returned by the NumRows request item into cell A10 in Microsoft Excel.
ActiveWorksheet.Range("A10").Value = DDERequest(chan, "NumRows")