Export (0) Print
Expand All

SQLRequest Function

Office 2007

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)




ValueMeaning
1The driver dialog box is always displayed.
2The 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.
3The 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).
4The dialog box isn’t displayed. If the connection is not successful, it returns an error.

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.

databaseName = "Northwind"
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


Community Additions

ADD
Show:
© 2014 Microsoft