SQLBind Function

Description

Don't use SQLBind 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.

SQLBind specifies where results are placed when they're retrieved with SQLRetrieve or SQLRetrieveToFile. Use SQLBind to change the column order of the result set from a query, or to place the result set columns in nonadjacent worksheet columns.

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 by using the References command (Tools menu).

Syntax

SQLBind(ConnectionNum, Column, Reference)

ConnectionNum Required. The unique connection ID (returned by SQLOpen) of the data source for which you want to bind results.

Column Optional. The column number of the result set you want to bind. Columns in the result set are numbered from left to right, starting with 1. If you omit Column, all bindings for ConnectionNum are removed.

Column 0 (zero) contains row numbers for the result set. You can return the row numbers by binding column 0 (zero).

Reference Optional. A Range object that specifies the location of a single cell on a worksheet where you want the results to be bound. If Reference is omitted, binding is removed for the column.

Return Value

This function returns an array that lists the bound columns for the current connection, by column number.

If SQLBind is unable to bind the column to the cell in the specified reference, it returns Error 2042.

If ConnectionNum isn't valid or if you try to bind a cell that isn't available, SQLBind returns Error 2015.

If Reference refers to more than a single cell, SQLBind returns Error 2023.

If SQLRetrieve doesn't have a destination parameter, SQLBind places the result set in the location indicated by Reference.

Remarks

SQLBind tells the ODBC Control Panel Administrator where to place results when they're received by way of SQLRetrieve The results are placed in the reference cell and the cells immediately below it.

Use SQLBind if you want the results from different columns to be placed in disjoint worksheet areas.

Use SQLBind for each column in the result set. A binding remains valid as long as the connection specified by ConnectionNum is open.

Call SQLBind after you call SQLOpen and SQLExecQuery, but before you call SQLRetrieve or SQLRetrieveToFile. Calls to SQLBind don't affect results that have already been retrieved.

See Also

SQLClose function, SQLError function, SQLExecQuery function, SQLGetSchema function, SQLOpen function, SQLRequest function, SQLRetrieve function, SQLRetrieveToFile function.

Example

This example runs a query on the NWind sample database, and then it uses the SQLBind function to display only the fourth and ninth columns of the query result set (the product name and the quantity on order) on Sheet1.

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 output1 = Worksheets("Sheet1").Range("A1")
Set output2 = Worksheets("Sheet1").Range("B1")
SQLBind chan, 4, output1
SQLBind chan, 9, output2
SQLRetrieve chan
SQLClose chan