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

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.