Description
In Microsoft Excel for Windows 95, do not 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 returns the result 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 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 and 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 that 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 single worksheet cell, as a Range object, where you want the completed connection string placed.
driverPrompt
Optional. Specifies when the driver dialog box is displayed and which options are available. Use one of the numbers described in the following table. If driverPrompt is omitted, SQLRequest uses 2 as the default.
Value
Meaning
1
Driver dialog box is always displayed.
2
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
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 are dimmed and unavailable if they are not required.
4
Dialog box is not displayed. If the connection is not successful, it returns an error.
colNamesLogical
Optional. True if you want the column names to be returned as the first row of results. It should contain False if you do not want the column names returned. The default value, if colNamesLogical is omitted, 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