Description
In Microsoft Excel for Windows 95, do not use SQLRetrieve 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.
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 (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
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 generated by SQLExecQuery.
If connectionNum is not 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 of the pending results in that cell and in the cells to the right 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 has not 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.
All of the results are returned if maxColumns is omitted.
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 does not discard the additional rows. Extra rows are retrieved by using SQLRetrieve again and by setting fetchFirstLogical to False.
All of the rows in the results are returned if maxRows is omitted.
colNamesLogical
Optional. True if you want the column names to be returned as the first row of results. False or omitted if you do not want the column names returned.
rowNumsLogical
Optional. Used only when destinationRef is included in the function call. If rowNumsLogical is True, the first column in the result set contains row numbers. If destinationRef is False or omitted, the row numbers are not returned. You can also retrieve row numbers by binding column number 0 (zero) with SQLBind.
namedRngLogical
Optional. True if you want each column of the results to 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 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 are 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 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, it returns 0 (zero).
Remarks
Before calling SQLRetrieve, you must do the following:
1. Establish a connection with a data source using SQLOpen.
2. Use the connection ID returned in SQLOpen to send a query with SQLExecQuery.
See Also
SQLBind Function, SQLClose Function, SQLError Function, SQLExecQuery Function, SQLGetSchema Function, SQLOpen Function, SQLRequest 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.
If Application.OperatingSystem Like "*Win*" Then
databaseName = "NWind"
Else 'Macintosh
databaseName = "NorthWind"
End If
queryString = "SELECT * FROM product.dbf WHERE (product.ON_ORDER<>0)"
chan = SQLOpen("DSN=" & databaseName)
SQLExecQuery chan, queryString
Set output = Worksheets("Sheet1").Range("A1")
SQLRetrieve chan, output, , , True
SQLClose chan