MSQuery: How to Retrieve Request Items Using DDERequest to MSQuery (149581)


The information in this article applies to:

  • Microsoft Excel 97 for Windows
  • Microsoft Excel for Windows 95
  • Microsoft Excel for Windows 5.0
  • Microsoft Excel for the Macintosh 5.0
  • Microsoft Query
This article was previously published under Q149581

SUMMARY

When you use DDERequest statements to return request items from Microsoft Query, the data is always returned as an array. Because a number of DDERequest statements will return a single dimension array under some circumstances and a two dimension array under other circumstances, failure to retrieve the data properly may result in Subscript Out Of Range errors.

This article identifies which DDERequest statements return each of the following:

  • Single Dimension Array Result of One Element Each
  • Two Dimension Column Array Result
  • Multiple-Dimension Array Result (That is, an array result that may be one dimension under certain circumstances and two dimensions under other circumstances.)
  • Special Case: Two Dimension Table Array Result
This article also describes how you can use DDERequest to return data from Microsoft Query under each of these situations.

MORE INFORMATION

The code samples shown below assume that you are familiar with how to use Microsoft Query. (See the "References" section below for places you can refer to for more information about using Microsoft Query.)

Before you attempt to run any of these example subroutines, be sure that Microsoft Query is open, and that you run the subroutine from an active empty worksheet.

Sample Visual Basic Code

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site:

http://www.microsoft.com/partner/referral/

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

Single Dimension Array Result of One Element Each

The following DDERequest items will always return a one dimension, single element array:

  • NumRows--returns the number of Rows (records) in the query
  • NumCols--returns the number of Columns (fields) in the query
  • Query--returns the name of the active query

    Recest--returns a rough estimate of how many rows (records) can be fetched at a time
  • ODBCSQLStatement--returns the ODBC SQL Statement used to access the data
  • QueryDefinition--returns the SQL Statement used to access the data
  • DataSourceName--returns the name of the Data Source used by the active query
  • ErrorText--returns the error text, if any, from the most recently executed SQL statement
  • ConnectionString--returns the connection string used to connect to the active window
  • NameSeparator--returns the single character used as the ODBC qualifier name separator
  • TierType--returns a single digit indicating the type of data source
  • Database/source--returns all databases for the specified data source
  • Username/source/database--returns the user name (database owner) for the specified data source and database
  • Topics--returns the names of the topics open on the System Channel along with System
  • Status--returns Microsoft Query's Status
The following example shows how you can open a channel to Microsoft Query using DDE, build your own query in Microsoft Query and exit to Microsoft Excel, retrieve a one dimension array of data, display the results in a message box, and close the channel. 
   Sub OneDimArray()
      ' Open a channel to Microsoft Query using DDE.
      Chan = DDEInitiate("MSQUERY", "System")

      ' Enable a user to build his or her own query in Microsoft Query and
      ' Exit Microsoft Query using "Return to Excel" on the File Menu.
      DDEExecute Chan, "[UserControl('&Return to Excel',3,true)]"

      ' Return the request item desired. In this example you return
      ' the name of the Data Source in use by the active query.
      MsgBox DDERequest(Chan, "DataSourceName")(1)

      ' Exit Microsoft Query if this is the only Query open. If other
      ' queries are open and you want to close them all, use
      ' "[Exit(True)]".
      DDEExecute Chan, "[Exit(False)]"

      ' Terminate the DDE channel.
      DDETerminate Chan
   End Sub

Two Dimension Column Array Result

The following DDERequest items will always return a two dimension, single column (vertical array) result:

  • Sources--returns all current data source connections available in the Select Data Source dialog box
  • Logon--returns all remote databases available in the ODBC Data Sources dialog box
  • Tables/source/user/database--returns all the Tables (both Database and System) for the specified data source, user, and database
  • Users/source/database--returns the available users (Owners and Databases) for the specified data source and database
  • FileExt--returns the File Extension for the currently connected Data Source
The following example shows how you can open a channel to Microsoft Query using DDE, build your own query in Microsoft Query and exit to Microsoft Excel, retrieve a two dimension array of data, display the results in a series of message boxes, and close the channel. 
   Sub TwoDimArray()
      ' Open a channel to Microsoft Query using DDE.
      Chan = DDEInitiate("MSQUERY", "System")

      ' Enable a user to build his or her own query in Microsoft Query and
      ' Exit Microsoft Query using "Return to Excel" on the File Menu.
      DDEExecute Chan, "[UserControl('&Return to Excel',3,true)]"

      ' Return the request item desired. In this example you return
      ' the names of all remote databases available in the ODBC Data
      ' Sources.
      LogArray = DDERequest(Chan, "Logon")

      ' Determine how many elements are in the first dimension of the
      ' returned array. The second dimension of the array will always be
      ' one.
      LogLen = UBound(LogArray, 1)

      ' Set up a loop to display each database name.
      For i = 1 To LogLen

         ' Display each database name in a message box.
         MsgBox "Logon function - Returns the ODBC.INI Data Source" _
            & "Connections" & Chr(10) & Chr(10) & "Logon Connection " _
            & i & ": " & LogArray(i, 1)

         ' Return to "For I" above until all database names have been
         ' displayed.
      Next i

      ' Exit Microsoft Query if this is the only Query open. If other
      ' queries are open and you want to close them all, use
      ' "[Exit(True)]".
      DDEExecute Chan, "[Exit(False)]"

      ' Terminate the DDE channel.
      DDETerminate Chan

   End Sub

Multiple-Dimension Array Result

The following DDERequest items return a one dimension array if the number of data items in the requested list is one, and a two dimension array if the number of data items in the requested list is more than one.

  • Logoff--returns all currently connected remote databases (those currently open in Microsoft Query)
  • QueryDefinition/n--returns the SQL Statement parsed into an array of "n" characters per element
  • ODBCSQLStatement/n--returns the ODBC SQL Statement parsed into an array of "n" characters per element
The following example shows how you can open a channel to Microsoft Query using DDE, build your own query in Microsoft Query and exit to Microsoft Excel, retrieve the data, determine if the data is in a one or two dimension array, display the results in a series of message boxes, and close the channel. 
   Sub MultiDimArray()
      ' Open a channel to Microsoft Query using DDE.
      Chan = DDEInitiate("MSQUERY", "System")

      ' Enable a user to build his or her own query in Microsoft Query and
      ' Exit Microsoft Query using "Return to Excel" on the File Menu.
      DDEExecute Chan, "[UserControl('&Return to Excel',3,true)]"

      ' Return the request item desired. In this example we are returning
      ' the names of all the currently connected remote databases.
      OffArray = DDERequest(Chan, "Logoff")

      ' Determine how many elements are in the first dimension of the
      ' array. The second dimension of the array will always be one.
      OffLen = UBound(OffArray, 1)

      ' If the first dimension of the array contains only one element...
      If UBound(OffArray) = 1 Then

         ' Then display the database name in a message box.
         MsgBox "Logoff function - Returns the currently" _
            & " connected database name" & Chr(10) & Chr(10) _
            & "Database: " & OffArray(1)

         ' However, if there is more than one value in the first dimension
         ' of the array...
      Else

         ' Then loop through all of the values returned.
         For i = OffLen To 1 Step -1

            ' And display each one in a message box. In this case, we are
            ' displaying the most recently accessed database name first.
            MsgBox "Logoff function - Returns all currently" _
               & " connected remote databases" _
               & Chr(10) & Chr(10) & "Connected Database " _
               & i & ": " & OffArray(i, 1)

            ' Return to "For I" above until all database names have been
            ' displayed.
         Next i

      ' End the Block If statement.
      End If

      ' Exit Microsoft Query if this is the only Query open. If other
      ' queries are open and you want to close them all, use
      ' "[Exit(True)]".
      DDEExecute Chan, "[Exit(False)]"

      ' Terminate the DDE channel.
      DDETerminate Chan
   End Sub

Special Case: Two Dimension Table Array Result

The following DDERequest item will always return a two dimension array.

  • FieldDef--returns a table describing the Field Name, Field Data Type, Field Width, Field Precision (number of decimal places) and the SQL Data Type
At least one Request Item, FieldDef, always returns more than one element in the first dimension of the array.

If there is only one row (record) in the FieldDef result, the first dimension of the array returns a 5, the number of columns (fields) in the array. If there are two or more records, the first dimension returns the number of records (rows) and the second dimension returns the number of columns.

If the result is a single record, you cannot determine the number of array dimensions by counting the elements in the first dimension. If you try to test the second dimension you can get a "subscript out of range" error message.

To address this situation you can use the "On Error Resume Next" logic as in the following example to open a channel to Microsoft Query, to build your own query in Microsoft Query and exit to Microsoft Excel, to retrieve the data, to determine if the data is in a one or two dimension array, to insert the requested table into the active worksheet, and to close the channel. 
  Sub TableArray()
      ' Open a channel to Microsoft Query using DDE.
      Chan = DDEInitiate("MSQUERY", "System")

      ' Enable a user to build his or her own query in Microsoft Query and
      ' Exit Microsoft Query using "Return to Excel" on the File Menu.
      DDEExecute Chan, "[UserControl(' &Return to Excel' ,3,true)]"

      ' Return the request item desired. In this example we are returning
      ' a table of values that describe the attributes of the database data
      ' result.
      FieldArray = DDERequest(Chan, "FieldDef")

      ' Turn on error handling. In this case, you want to resume with the
      ' next line of code if an error is detected.
      On Error Resume Next

      ' You can now use "IsError" to test to see if trying to access the
      ' second dimension will produce an error.
      If IsError(Fieldcols = UBound(FieldArray, 2)) Then

      ' If you get an error, suspect that there is only one dimension
      ' in the array (other errors could also occur) and set the number
      ' of field rows to 1 and the field column count to 5. This code does
      ' not address any other error condition.
         Fieldrows = 1
         Fieldcols = UBound(FieldArray, 1)

         ' Once you pass this point, you want turn off the error
         ' handler (unless you want to write code to handle other
         ' potential errors)
         On Error GoTo 0

         ' If there was no error, then this is a two dimension array.
      Else
         ' Get the number of records from the first dimension.
         Fieldrows = UBound(FieldArray, 1)

         ' Get the number of columns from the second dimension.
         Fieldcols = UBound(FieldArray, 2)

         ' End the Block If statement.
      End If

      ' Resize the worksheet range for the number of rows and columns
      ' in the table and load the data onto the worksheet.
      Worksheets("Sheet1").Range("A1").Resize(Fieldrows, Fieldcols) = _
         FieldArray

      ' Exit Microsoft Query if this is the only Query open. If other
      ' queries are open and you want to close them all, use
      ' "[Exit(True)]".
      DDEExecute Chan, "[Exit(False)]"

      ' Terminate the DDE channel.
      DDETerminate Chan
   End Sub

REFERENCES

"Getting Started with Microsoft Office for Windows 95"

Microsoft Query Online Help

"Microsoft Query version 1.0 User's Guide," Chapter 9,"Using Dynamic Data Exchange with Microsoft Query"
Modification Type:MinorLast Reviewed:10/11/2006
Keywords:kbcode kbhowto kbProgramming KB149581
©2004 Microsoft Corporation. All rights reserved.