|Excel Developer Reference|
It is recommended that you use the objects, methods, and properties in the Microsoft ActiveX Data Objects (ADO) library instead of SQLRetrieve and the other ODBC functions in the Xlodbc.xla add-in.
SQLRetrieve retrieves all or part of the results from a previously executed query.
Before using SQLRetrieve, you must establish a connection with SQLOpen, execute a query with SQLExecQuery, and have the results pending.
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.
SQLRetrieve(ConnectionNum, DestinationRef, MaxColumns, MaxRows, ColNamesLogical, RowNumsLogical, NamedRngLogical, FetchFirstLogical)
DestinationRef Optional. A Range object that specifies where the results should be placed. This function overwrites any values in the cells, without confirmation. If DestinationRef refers to a single cell, SQLRetrieve returns all the pending results in that cell and in the cells to the right of and below it. If DestinationRef is omitted, the bindings established by previous calls to SQLBind are used to return results. If no bindings exist for the current connection, SQLRetrieve returns Error 2023. If a particular result column hasn’t been bound and DestinationRef is omitted, the results are discarded.
MaxColumns Optional. The maximum number of columns returned to the worksheet, starting at DestinationRef. If MaxColumns specifies more columns than are available in the result, SQLRetrieve places data in the columns for which data is available and clears the additional columns. If MaxColumns specifies fewer columns than are available in the result, SQLRetrieve discards the rightmost result columns until the results fit the specified size. The order in which the data source returns the columns determines column position. If MaxColumns is omitted, all the results are returned.
MaxRows Optional. The maximum number of rows to be returned to the worksheet, starting at DestinationRef. If MaxRows specifies more rows than are available in the results, SQLRetrieve places data in the rows for which data is available and clears the additional rows. If MaxRows specifies fewer rows than are available in the results, SQLRetrieve places data in the selected rows but doesn’t discard the additional rows. You can retrieve extra rows by using SQLRetrieve again and setting FetchFirstLogical to False. If MaxRows is omitted, all the rows in the results are returned.
SQLRetrieve returns the number of rows in the result set.
If SQLRetrieve is unable to retrieve the results on the specified data source or if there are no results pending, it returns Error 2042. If no data is found, SQLRetrieve returns 0 (zero).
Before calling SQLRetrieve, you must do the following:
- Establish a connection with a data source by using SQLOpen.
- Use the connection ID returned in SQLOpen to send a query with SQLExecQuery.
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.