SQLOpen Function

Description

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

SQLOpen establishes a connection to a data source.

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

SQLOpen(ConnectionStr, OutputRef, DriverPrompt)

ConnectionStr Required. Supplies the information required by the driver being used to connect to a data source; must follow the driver's format.

ConnectionStr supplies the data source name and other information — such as user ID and passwords — that the driver requires to make a connection.

You must define the data source name (DSN) used in ConnectionStr before you try to connect to it.

OutputRef Optional. A Range object (must be a single cell) that contains the completed connection string.

Use OutputRef when you want SQLOpen to return the completed connection string to a worksheet.

DriverPrompt Optional. Specifies whether the driver dialog box is displayed and, if it is, which options are available in it. Use one of the values described in the following table. If DriverPrompt is omitted, SQLOpen uses 2 as the default.

Value	Meaning

1		The driver dialog box is always displayed.
2		The driver dialog box is displayed only if information provided by the connection string and the data source specification aren't sufficient to complete the connection. All dialog box options are available.

(continued)

Value	Meaning

3		The same as 2 except that dialog box options that aren't required are dimmed (unavailable).
4		The driver dialog box isn't displayed. If the connection isn't successful, SQLOpen returns an error.

Return Value

If successful, SQLOpen returns a unique connection ID number. Use the connection ID number with the other ODBC functions.

If SQLOpen is unable to connect using the information you provide, it returns Error 2042. Additional error information is placed in memory for use by SQLError.

See Also

SQLBind function, SQLClose function, SQLError function, SQLExecQuery function, SQLGetSchema function, SQLRequest function, SQLRetrieve 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