Don’t use SQLRetrieve and the other ODBC functions in the Xlodbc.xla add-in; use the objects, methods, and properties in the Microsoft ActiveX Data Objects (ADO) library instead. For more information about ActiveX Data Objects, see Welcome To ADO.
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 (Tools menu).
Syntax
SQLRetrieve(ConnectionNum, DestinationRef, MaxColumns, MaxRows, ColNamesLogical, RowNumsLogical, NamedRngLogical, FetchFirstLogical)
ConnectionNum Required. The unique connection ID returned by SQLOpen and for which you have pending query results that were generated by SQLExecQuery.
If ConnectionNum isn’t valid, SQLExecQuery returns Error 2015.
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.
ColNamesLogical Optional. True to have the column names be returned as the first row of results. False or omitted to have the column names not be returned.
RowNumsLogical Optional. Used only when DestinationRef is included in the function call. True to have the first column in the result set contain row numbers. False or omitted to have the row numbers not be returned. You can also retrieve row numbers by binding column 0 (zero) with SQLBind.
NamedRngLogical Optional. True to have each column of the results be declared as a named range on the worksheet. The name of each range is the result column name. The named range includes only the rows that are returned with SQLRetrieve. The default value is False.
FetchFirstLogical Optional. Allows you to request results from the beginning of the result set. If FetchFirstLogical is False, SQLRetrieve can be called repeatedly to return the next set of rows until all the result rows have been returned. When there are no more rows in the result set, SQLRequest returns 0 (zero). If you want to retrieve results from the beginning of the result set, set FetchFirstLogical to True. To retrieve additional rows from the result set, set FetchFirstLogical to False in subsequent calls. The default value is False.
Return Value
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).
Remarks
Before calling SQLRetrieve, you must do the following: