Microsoft KB Archive/98668

= ACC: How to Buffer SQL Strings Using DDE =

Article ID: 98668

Article Last Modified on 5/9/2003

-

APPLIES TO


 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q98668



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

Microsoft Access can handle SQL strings larger than 255 characters through dynamic data exchange (DDE). To do this, break the SQL string into portions with fewer than 255 characters, and then send these strings to Microsoft Access using DDE. The separate portions are buffered until a semicolon is sent; at that point, Microsoft Access runs the query.



MORE INFORMATION
The following steps demonstrate how to create a DDE macro in Microsoft Excel:

 Open the sample database NWIND.MDB.  Open a new macro sheet in Microsoft Excel. Enter the following macro:

     Cell    Command --     B1      SQLDDEExample B2     B3      chan=INITIATE(&quot;MSACCESS&quot;,&quot;nwind.mdb;SQL&quot;) B4     =POKE(chan,&quot;SQLText&quot;,C3) B5     =POKE(chan,&quot;SQLText&quot;,C4:C5) B6     QueryResult=REQUEST(chan,&quot;All&quot;) B7     =TERMINATE(chan) B8     =FOR(&quot;FldPos&quot;,1,COLUMNS(QueryResult)) B9     =FOR(&quot;RecPos&quot;,1,ROWS(QueryResult)) B10    =FORMULA(INDEX(QueryResult,RecPos,FldPos),                  OFFSET(C7,RecPos,FldPos)) B11     =NEXT B12     =NEXT B13     =RETURN   Enter the following pieces of the SQL string on the macro sheet:

     Cell     Command ---     C3        SELECT * From categories C4        ORDER BY categories.[category id] C5        DESC;

NOTE: Be sure to include one space in front of the words &quot;ORDER&quot; and &quot;DESC&quot; in the step above.  To run the macro, select cell B3, choose Run from the Macro menu, and choose OK. The query then runs.

The results of the query are placed in cells D8:G16 on the Microsoft Excel macro sheet.

Additional query words: queries sql dde

Keywords: kbhowto kbinterop KB98668

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.