SQLRequest Function
It is recommended that you use the objects, methods, and properties in the Microsoft ActiveX Data Objects (ADO) library instead of SQLRequest and the other ODBC functions in the Xlodbc.xla add-in.
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. Before you use the function, you must establish a reference to the add-in by using the References command (on the Tools menu) in the Visual Basic Editor.
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 ConnectionStrbefore you try to connect to it. If SQLRequestis 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 SQLRequestis unable to execute QueryTexton the specified data source, it returns Error 2042.
OutputRef Optional. A Rangeobject (must be a single cell) where you want the completed connection string to be placed.
DriverPromptOptional. Specifies whether the driver dialog box is displayed and which options are available. Use one of the values described in the following table. If DriverPromptis omitted, SQLRequestuses 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 is not 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 is not 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 is not successful, it returns an error. |
ColNamesLogical Optional. Trueto have the column names returned as the first row of results. Falseto not have the column names returned. If ColNamesLogicalis 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.
Example
This example runs a query on the Northwind 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.
|