ACC: How to Buffer SQL Strings Using DDE

ID: Q98668


The information in this article applies to:


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          : kbinterop 
Version           : 1.10 2.00
Platform          : WINDOWS 
Issue type        : kbhowto 

Last Reviewed: March 22, 1999