SQL.REQUEST

Connects with an external data source, and runs a query from a worksheet. SQL.REQUEST then returns the result as an array without the need for macro programming. If this function is not available, you must install the Microsoft Excel ODBC add-in (XLODBC.XLA).

Syntax

SQL.REQUEST(connection_string,output_ref,driver_prompt,query_text,col_names_logical)

Connection_string   supplies information, such as the data source name, user ID, and passwords, required by the driver being used to connect to a data source and must follow the driver's format. The following table provides three example connection strings for three drivers.

• You must define the data source name (DSN) used in connection_string before you try to connect to it.
• You can enter connection_string as an array or a string. If connection_string exceeds 250 characters, you must enter it as an array.
• If SQL.REQUEST is unable to gain access to the data source using connection_string, it returns the #N/A error value.

Output_ref   is a cell reference where you want the completed connection string placed. If you enter SQL.REQUEST on a worksheet, then output_ref is ignored.

• Use output_ref when you want SQL.REQUEST to return the completed connection string (you must enter SQL.REQUEST on a macro sheet in this case).
• If you omit output_ref, SQL.REQUEST does not return a completed connection string.

Driver_prompt   specifies when the driver dialog box is displayed and which options are available. Use one of the numbers described in the following table. If driver_prompt is omitted, SQL.REQUEST uses 2 as the default.

Query_text   is the SQL statement that you want to execute on the data source.

• If SQL.REQUEST is unable to execute query_text on the specified data source, it returns the #N/A error value.
• You can update a query by concatenating references into query_text. In the following example, every time $A$3 changes, SQL.REQUEST uses the new value to update the query.

"SELECT Name FROM Customers WHERE Balance > "&$A$3&"".

Microsoft Excel limits strings to a length of 255 characters. If query_text exceeds that length, enter the query in a vertical range of cells, and use the entire range as the query_text. The values of the cells are concatenated to form the complete SQL statement.

Column_names_logical   indicates whether column names are returned as the first row of the results. Set this argument to TRUE if you want the column names to be returned as the first row of the results. Use FALSE if you do not want the column names returned. If column_names_logical is omitted, SQL.REQUEST does not return column names.

Return Value

• If this function completes all of its actions, it returns an array of query results or the number of rows affected by the query.
• If SQL.REQUEST is unable to access the data source using connection_string, it returns the #N/A error value.

Remarks

• SQL.REQUEST can be entered as an array. When you enter SQL.REQUEST as an array, it returns an array to fit that range.
• If the range of cells is larger than the result set, SQL.REQUEST adds empty cells to the returned array to increase it to the necessary size.
• If the result set is larger than the range entered as an array, SQL.REQUEST returns the whole array.
• The arguments to SQL.REQUEST are in a different order than the arguments to the SQLRequest function in Visual Basic® for Applications.

Example

Suppose you want to make a query of a dBASE database named DBASE4. When you enter the following formula in a cell, an array of query results is returned, with the first row being the column names:

SQL.REQUEST("DSN=NWind;DBQ=c:\msquery;FIL=dBASE4", c15, 2,

"Select Custmr_ID, Due_Date from Orders WHERE order_Amt>100", TRUE)