ACC: Using the DDE Item SQLText to Request the Text of a Query

 Last reviewed: June 3, 1997
Article ID: Q102519
The information in this article applies to:
  • Microsoft Access versions 1.1, 2.0

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

Microsoft Access version 1.1 introduces the new DDE item, SQLText, which can be used to retrieve the SQL statement representing the table or query topic on which the DDE link has been established.

The SQLText item allows the DDE client to request that the SQLText be either returned as a whole or divided into substrings. The length of the substring is specified by the client. The SQLText is returned as an array of substrings.

This article describes the use of this DDE item to request the text of a query and how to place it in a Microsoft Excel worksheet.

MORE INFORMATION

The syntax for the SQLText item name is as follows: 

   SQLText;<number>
where <number> is the maximum number of characters for each substring. If a value for <number> is not provided, the entire text is sent as one string.

The last substring of the array may be shorter than the previous substrings if there are not enough characters.

Creating the Sample Macro

NOTE: The following example assumes that you have a working knowledge of Microsoft Excel macros. For information about using macros, please refer to the Microsoft Excel documentation.

The following sample macro should be pasted or entered in a Microsoft Excel macro sheet, starting with cell A1. (Some formulas are dependent on starting at that location in the macro sheet.) Also, formulas designated in opening and closing braces ({}) must be entered as array formulas, using the CTRL+SHIFT+ENTER key combination. For example, use the three steps below to yield the following array formula: 

   {=FORMULA.ARRAY(TRANSPOSE(SQL))}

  • Go to the cell that contains the formula.

  • Remove the braces ({}).

  • Press CTRL+SHIFT+ENTER.

    To verify that the formula is entered as an array, the braces will reappear around the formula in the Microsoft Excel formula bar. For more information about using array formulas, please refer to the Microsoft Excel documentation. 

       SQLText
   StringLength=50
   chan=INITIATE("MSACCESS","NWIND.MDB;Query Sales for 1991")
   {=FORMULA.ARRAY(REQUEST(chan,"SqlText;255"),$B$1:$U$1)}
   =IF(LEN($B$1)<255)
   =  SET.NAME("SQLLen",LEN($B$1))
   =ELSE()
   {=  SET.NAME("SQLLen",SUM(IF(ISNA($B$1:$U$1),0,LEN($B$1:$U$1))))}
   =END.IF()
   =SET.NAME("SQLPieces",INT(SQLLen/StringLength)+1)
   =SET.NAME("SQL",REQUEST(chan,"SqlText;"&StringLength))
   =TERMINATE(chan)
   =NEW(1)
   =SELECT("r1c1:r"&SQLPieces&"c1")
   {=FORMULA.ARRAY(TRANSPOSE(SQL))}
   =COPY()
   =PASTE.SPECIAL(3)
   =CANCEL.COPY()
   =FORMULA.REPLACE(CHAR(13)&CHAR(10)," ",2,2,FALSE)
   =COLUMN.WIDTH(,,,3)
   =RETURN()

    How the Sample Macro Works

    The length of the result string is set to 50, a channel is initiated to the Microsoft Access query, the SQL statement is requested in 255-character chunks, and the total length of the query is calculated.

    Using SQLLen, the number of rows needed to display the query is calculated. The SQLText is requested in chunks of 50, based on the variable StringLength, and then the DDE channel is terminated.

    Finally, a new worksheet is opened and the appropriate number of cells are selected. The Formula.Array() function places the SQLText in the currently selected cells. The result originally appears as a formula, but the Copy and Paste.Special() commands change it to text. The carriage returns and line feeds are changed to spaces and the ColumnWidth property is set to Best Fit.

    • Keywords : IntpDde kbinterop
    Version : 1.10 2.00
    Platform : WINDOWS
    Hardware : X86
    Issue type : kbinfo

    THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

    Last reviewed: June 3, 1997
    © 1998 Microsoft Corporation. All rights reserved. Terms of Use.