ACC: How to Buffer SQL Strings Using DDE

Last reviewed: June 6, 1997
Article ID: Q98668
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 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:

  1. Open the sample database NWIND.MDB.

  2. Open a new macro sheet in Microsoft Excel. Enter the following macro:

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

  3. 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 "ORDER"
       and "DESC" in the step above.
    
    

  4. 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 : IntpDde kbinterop
Version : 1.10 2.00
Platform : WINDOWS
Hardware : X86
Issue type : kbhowto


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 6, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.