MSQuery: Returning a Long SQL Statement from Query with DDE

ID: Q140539

1.00 2.00 WINDOWS kbtool kbole kbhowto xlquery

The information in this article applies to:

Microsoft Query for Windows, version 1.0
Microsoft Query for Windows 95, version 2.0

SUMMARY

The Microsoft Query "User's Guide" shows how to return an SQL statement recorded manually in Query, but if the SQL statement is longer than 255 characters that code does not work. This article shows how you can return an SQL statement of any length recorded manually in Query.

MORE INFORMATION

Page 142 of the Microsoft Query User's Guide shows how to program a DDE conversation between Query and any other application, so that you can launch Query from the other application, perform a query manually, and return the data, SQL connection string, and other information to the first application. However, the example will not return an SQL statement that is longer than 255 characters. Another problem with the example is that it will return renamed column names, which will not work if you try to use the SQL statement later with the SQL functions.

This procedure uses the "ODBCSQLStatement" and "ODBCSQLStatement/n" Request Items from page 138 of the Microsoft Query 1.0 "User's Guide" to bring back an SQL statement of any length. The "ODBCSQLStatement" and "ODBCSQLStatement/n" request items are used instead of "QueryDefinition" and "QueryDefinition/n" because "ODBCSQLStatement" and "ODBCSQLStatement/n" do not return renamed column names; therefore, these statements can be used with the SQL functions. Microsoft Query automatically renames column headers when column totals (such as Count, Sum, and so on) are used.

Note that the SQL statement returned from Query cannot be used with DAO (Data Access Objects), because DAO statements use a different SQL syntax than Microsoft Query functions. DAO uses the same SQL syntax as Microsoft Access. To manually record a DAO SQL statement, you must use Microsoft Access.

To determine whether "ODBCSQLStatement/n" or "ODBCSQLStatement" should be used, Query tests for the number of elements in the array created from "ODBCSQLStatement/n". If only one element is found, then "ODBCSQLStatement/n" cannot be used and "ODBCSQLStatement" is used instead. If more than one element is found, then "ODBCSQLStatement" cannot be used and "ODBCSQLStatement/n" is used instead.

Entering the SQL statement into worksheet cells will cause Wrap Text to be turned on between SQL clauses. This procedure will return each cell's Wrap Text status to its original state.

Microsoft provides examples of Visual Basic for applications procedures 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 Visual Basic procedure is provided 'as is' and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose.

Sub Return_Any_Length_SQL()

Dim SQL_Long As Variant Dim SQL_Short As Variant Dim WT_Flag As Boolean

'Open a DDE channel with Query:

chan = Application.DDEInitiate("MSQuery", "System")

'Launch Query and pass control to the user:

Application.DDEExecute chan, "[UserControl('&Return to Excel ',3,True)]"

'Get the SQL Statement from Query as a String, if <256 characters:

SQL_Short = Application.DDERequest(chan, "ODBCSQLStatement")

'Get the SQL Statement from Query, if >255 characters, as an array
'where each element of the array is no longer than 127 characters:

SQL_Long = Application.DDERequest(chan, "ODBCSQLStatement/127")

'Close the DDE channel:

Application.DDETerminate chan

    'If SQL is <256 characters:
    If UBound(SQL_Long) = 1 Then
    'Set a flag to the cell's WrapText status, because entering the
    'SQL onto a worksheet will turn on Wrap Text between SQL clauses:
    WT_Flag = Sheets("sheet1").Range("A1").WrapText
    'Enter SQL_Short into Sheet1 at Cell A1:
    Sheets("Sheet1").Range("A1") = SQL_Short
    'Reset the cell's WrapText status to it's original state:
    Sheets("sheet1").Range("A1").WrapText = WT_Flag
    'If SQL is >255 characters:
    Else
        'Loop through the elements in the SQL_Long array, entering each one
        'into a cell on Sheet1 in column A, starting at cell A1:
        For i = 1 To UBound(SQL_Long)
        'Set a flag to the cell's WrapText status, because entering the
        'SQL onto a worksheet will turn on Wrap Text between SQL clauses:
        WT_Flag = Sheets("sheet1").Cells(i, 1).WrapText
        'Enter one element of the SQL_Long array into one cell:
        Sheets("Sheet1").Cells(i, 1) = SQL_Long(i, 1)
        'Reset the cell's WrapText status to it's original state:
        Sheets("Sheet1").Cells(i, 1).WrapText = WT_Flag
        'End of loop:
        Next i
    'End of IF Statement:
    End If
End Sub

REFERENCES

"Microsoft Query User's Guide", version 1.0, Chapter 9, "Using Microsoft Query with Other Applications"

KBCategory: kbtool kbole KBSubcategory: xlquery DDE kbhowto

Additional reference words: 1.00 2.00 5.00 5.00c 7.00

Keywords          : xlquery kbhowto DDE 
Version           : 1.00 2.00
Platform          : WINDOWS

Last Reviewed: September 16, 1996