How to Retrieve Request Items Using DDERequest to MSQuery
ID: Q149581
|
The information in this article applies to:
-
Microsoft Excel for Windows, version 5.0
-
Microsoft Excel for Windows 95, version 7.0
-
Microsoft Excel 97 for Windows
-
Microsoft Excel for the Macintosh, version 5.0
-
Microsoft Query for Windows, version 1.0
-
Microsoft Query for Windows 95, version 2.0
-
Microsoft Query 97 for Windows
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
the Microsoft fee-based consulting line at (800) 936-5200. For more
information about the support options available from Microsoft, please see
the following page on the World Wide Web:
http://www.microsoft.com/support/supportnet/overview/overview.asp 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"
Additional query words:
5.0 5.0c 5.00a 5.00c
Keywords : kbcode kbprg kbtool PgmHowto
Version : MACINTOSH:5.0; WINDOWS:1.0,2.0,5.0,7.0,97
Platform : MACINTOSH WINDOWS
Issue type : kbhowto
|