ACC: How to Buffer SQL Strings Using DDE (98668)

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("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()

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.

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.