SQLRequest Function

Description

Don't use SQLRequest and the other ODBC functions in the Xlodbc.xla add-in; use the objects, methods, and properties in the Data Access Objects (DAO) library instead.

SQLRequest connects to an external data source and runs a query from a worksheet, and then it returns the result of the query as an array.

This function is contained in the Xlodbc.xla add-in (ODBC Add-In on the Macintosh). Before you use the function, you must establish a reference to the add-in by using the References command (Tools menu).

Syntax

SQLRequest(ConnectionStr, QueryText, OutputRef, DriverPrompt, ColNamesLogical)

ConnectionStr Required. Supplies information — such as the data source name, user ID, and passwords — required by the driver being used to connect to a data source; must follow the driver's format.

You must define the data source name (DSN) used in ConnectionStr before you try to connect to it.

If SQLRequest is unable to access the data source using ConnectionStr, it returns Error 2042.

QueryText Required. The SQL statement you want to execute on the data source.

If SQLRequest is unable to execute QueryText on the specified data source, it returns Error 2042.

OutputRef Optional. A Range object (must be a single cell) where you want the completed connection string to be placed.

DriverPrompt Optional. Specifies whether the driver dialog box is displayed and which options are available. Use one of the values described in the following table. If DriverPrompt is omitted, SQLRequest uses 2 as the default.

Value	Meaning

1		The driver dialog box is always displayed.
2		The driver dialog box is displayed only if information provided by the connection string and the data source specification isn't sufficient to complete the connection. All dialog box options are available.
3		The driver dialog box is displayed only if information provided by the connection string and the data source specification isn't sufficient to complete the connection. Dialog box options that aren't required are dimmed (unavailable).
4		The dialog box isn't displayed. If the connection isn't successful, it returns an error.

ColNamesLogical Optional. True to have the column names be returned as the first row of results. False to not have the column names be returned. If ColNamesLogical is omitted, the default value is False.

Remarks

The arguments to the SQLRequest function are in a different order than the arguments to the SQL.REQUEST macro function.

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 SQLRequest is unable to complete all of its actions, it returns an error value and places the error information in memory for SQLError.

If SQLRequest is unable to access the data source using ConnectionStr, it returns Error 2042.

See Also

SQLBind function, SQLClose function, SQLError function, SQLExecQuery function, SQLGetSchema function, SQLOpen function, SQLRetrieve function, SQLRetrieveToFile function.

Example

This example runs a query on the NWind sample database. The result of the query, displayed on Sheet1, is a list of all products that are currently on order. The SQLRequest function also writes the full connection string to Sheet2.

If Application.OperatingSystem Like "*Win*" Then
    databaseName = "NWind"
Else            'Macintosh.
    databaseName = "NorthWind"
End If
queryString = "SELECT * FROM product.dbf WHERE (product.ON_ORDER<>0)"
returnArray = SQLRequest("DSN=" & databaseName, _
            queryString, _
            Worksheets("Sheet1").Range("A1"), _
            2, True)
For i = LBound(returnArray, 1) To UBound(returnArray, 1)
    For j = LBound(returnArray, 2) To UBound(returnArray, 2)
        Worksheets("Sheet1").Cells(i, j).Formula = returnArray(i, j)
    Next j
Next i