Visual Basic Example Using BuildSQL (142357)

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. When you use the BuildSQL command with an SQL statement that is greater than 255 characters, you can send smaller pieces of information to a Microsoft Excel macro to build the complete SQL statement. The BuildSQL command is available only on the System channel.

The following example runs a query, returns the query string in pieces to an array, builds the SQL statement, runs the new query, and returns the result set to Sheet1. To use the following macro, Microsoft Query must be running when you start the macro. When you use the following macro in Microsoft Excel 7.0, you must create a data source with the name "NWind" using the dBASE files in the MSQuery directory.

Sub UsingBuildSQL()

   ' Dimension the variables.
   Dim s As Variant
   Dim myrequest As Variant
   Dim chan As Integer

   ' Initiate a channel to MSQuery.
   chan = DDEInitiate("MSQuery", "System")

   ' Log on to the Nwind datasource.
   DDEExecute chan, "[Logon('NWind')]"

   ' Execute a SQL statement.
   DDEExecute chan, "[Open('Select * From Employee')]"

   ' Request the ODBC SQL statement in strings of 120 characters
   ' and places it into an array.
   myrequest = DDERequest(chan, "ODBCSQLStatement/120")

   ' Execute the BuildSQL statement for each element returned.
   For Each s In myrequest
      DDEExecute chan, "[Buildsql('" & s & "')]"
   Next

   ' Execute the SQL statement built by BuildSQL.
   DDEExecute chan, "[QueryNow()]"

   ' Request the number of rows.
   NumRows = DDERequest(chan, "NumRows")

   ' Request the number of columns.
   NumCols = DDERequest(chan, "NumCols")

   ' Return the data to Sheet1.
   DDEExecute chan, "[Fetch('Excel','Sheet1','R1C1:R" & NumRows(1) & _
      "C" & NumCols(1) & "','All/Headers')]"

   ' Terminate the DDE channel.
   DDETerminate chan

End Sub