Using SQL.REQUEST to Return an Array of Data

You can use the ODBC Add-in for Excel to access and retrieve data from the DP4 data source directly, without using Microsoft Query. If you selected the Data Access check box in the Complete/Custom option you have all the files you need.

The ODBC Add-in contains additional functions that extend the Excel macro language for ODBC and a special function called SQL.REQUEST.

SQL.REQUEST connects with an ODBC data source and runs a query from the Excel worksheet, returning the result as an array, without the need for macro programming. If this function is not available, you must install the ODBC Add-in (XLODBC.XLA) (see Installing the ODBC Add-In later in this section).

The following example uses the SQL.REQUEST function to retrieve customer names and balances, selected by their balances, but assumes that SALESORD is an available ODBC data source.

  1. Select the array range by highlighting a block of cells, two columns wide by ten rows deep
  2. Select Function on the Insert menu. Step 1 of the Function Wizard appears
  3. In the Function Category list, click Database
  4. In the Function Name list, click SQL.REQUEST, followed by Next

Step 2 of the Function Wizard appears. It displays a number of boxes for entering the parameters of the SQL.REQUEST function:

Parameter Description

connection_string

A string or array that supplies information required by the DP4 driver to connect to the data source, in the format:

 

DSN=<data_source_name>;DBQ=Datafit

[;UID=<username>;PWD=<password>] 

output_ref

(optional). A cell reference where you want the completed connection string placed

driver_prompt

(optional) An integer from 1 through 4 that determines when the DP4 driver dialog is displayed

query_text

The SQL statement you want to execute on the DP4 data source. You can update a query by concatenating references into query_text. In this example, every time $C$2 changes, SQLREQUEST uses the new value to update the query:

 

". . . WHERE balance >" & $C$2,

column_names_logical

TRUE returns column names as the first row of the query results. FALSE suppresses column names in the query results
  1. In the connection_string box, type:
    DSN=salesord;DBQ=DP4
    to establish a connection with the SALESORD data source. Press <Tab>
  2. In the output_ref box, press <Tab> to leave it blank
  3. In the driver_prompt box, type: 3 to display the dialog only if information provided by the connection_string is insufficient to complete the connection. Press <Tab>
  4. In the query_text box, type:
    "SELECT name, balance FROM customer WHERE balance >" & $C$2
    to select names and balances of customers with balances greater than the value in cell C2. Press <Tab>
  5. In the column_names_logical box, type: TRUE to return NAME and BALANCE as headings in the first row of the results
  6. Click Finish to close the Function Wizard. The Formula bar should now display the formula:

=SQL.REQUEST("DSN=salesord;DBQ=DP4",,3,"SELECT name, balance FROM customer WHERE balance >" & $C$2,TRUE)

  1. Click anywhere in the Formula bar and press <Ctrl+Shift+Enter>

Excel executes the SQL.REQUEST function and fills the array range with the results of the query on the SALESORD database

If you want to change the selection value, changing the value of C2 refreshes the array with new data.

The sample salesord.xls worksheet supplied contains an array that makes use of the SQL.REQUEST function. For more information on this function or about array formulas, refer to the information provided by Microsoft Excel.

Installing the ODBC Add-In

To make the Excel ODBC Add-in available:

  1. Select Add-Ins on the Tools menu. The Add-Ins Available dialog appears
  2. Select XLODBC.XLA (normally found in the \excel\library\msquery directory)