SQL.OPEN

Macro Sheets Only

Establishes a connection with a data source. If the connection is successfully established SQL.OPEN will return a connection ID number. Use the connection ID number with other ODBC macro functions to identify a connection.

If this function is not available, you must install the Microsoft ODBC add-in (XLODBC.XLA).

Syntax

SQL.OPEN(connection_string, output_ref, driver_prompt)

Connection_string is a text string that contains the information necessary to establish a connection to a data source. Any data-source-name that is used in connection_string must be an existing data source name defined with ODBC Setup or the ODBC Administration Utility.

• Connection_string must follow the format described in Chapter 24, "ODBC Function Reference", of the Microsoft Open Database Connectivity Programmer's Reference for SQLDriverConnect. In this string the user supplies the data source name, one or more user ID's, one or more passwords, and any other information necessary to successfully connect to a DBMS. An example of a SQL.OPEN connection_string entered would be: "DSN=MyServer; UID=dbayer; PWD=123; Database=pubs"
• Enter the connection_string as an array when the length exceeds 255 characters. Or enter connection_string as an array of cells containing the same information. The connection string should be horizontal array.

Output_ref is a single cell reference where you want the completed connection string to be placed. Use output_ref when you want SQL.OPEN to return the completed connection string. If output_ref is omitted, a completed connection string will not be returned.

Driver_prompt is a number from 1 to 4 specifying if and how you want to be prompted by the driver. This sets the fDriverCompletion flag in ODBC's SQLDriverConnect.

Number Description

1 Always brings up a dialog box.

2 Bring up dialog only if there is not enough information to connect. The driver uses information from the connection string and from the data source specification as defaults.

3 Same as 2, but the driver grays and disables any prompts for information not needed.

4 If the connection string is unsuccessful, do not bring up a dialog box.

Remarks

• If SQL.OPEN is unable to connect with the information provided then it will return the error value #N/A. In such a case, SQL.OPEN will place error information in memory for the SQL.ERROR function, if more information is available.
• If the call is successful then SQL.OPEN will return a unique connection ID number that can be used in future function calls to identify the connection.
• If connection_array does not allow SQL.OPEN to connect to a data source, then the error value #N/A will be returned.

Example

conn1=SQL.OPEN('DSN=NWind;DBQ=C:\MSQUERY;FIL=dBASE4',C15, 2) sets the name conn1 to the return value of SQL.OPEN, which connects to the NWind data source, specifies where to place the connection string, and displays the driver dialog box only if additional informaion is needed.

Related Functions

SQL.EXEC.QUERY Sends a query to a data source

SQL.BIND Specifies storage for a result column

SQL.RETRIEVE.TO.FILE Retrieves query results and places them in a file

SQL.RETRIEVE Retrieves query results

SQL.GET.SCHEMA Gets information about a connected data source.

SQL.CLOSE Closes a data source connection

SQL.ERROR Returns detailed error information

List of Command-Equivalent Functions