ACC: How to Buffer SQL Strings Using DDE

• 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.

Additional query words: queries sql dde

Keywords : kbinterop
Version : 1.10 2.00
Platform : WINDOWS
Issue type : kbhowto