Description
In Microsoft Excel for Windows 95, do not 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 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 and must follow the driver's format.
The connectionStr supplies the data source name and other information, such as user ID and passwords, that is required by the driver 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 single cell, as a Range object, that contains the completed connection string.
Use outputRef when you want SQLOpen to return the completed connection string to a worksheet.
driverPrompt
Optional. Specifies when the driver dialog box is displayed and which options are available. Use one of the numbers described in the following table. If driverPrompt is omitted, SQLOpen uses 2 as the default.
Value
Meaning
1
Driver dialog box is always displayed.
2
Driver dialog box is displayed only if information provided by the connection string and the data source specification are not sufficient to complete the connection. All dialog box options are available.
3
The same as 2 except that dialog box options that are not required are dimmed and unavailable.
4
Driver dialog box is not displayed. If the connection is not 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