Visual Basic: RDO Data Control
OpenConnection Method (Remote Data)
Opens a connection to an ODBC data source and returns a reference to the rdoConnection object that represents a specific database.
Syntax
Setconnection = environment**.OpenConnection(dsName[, prompt[, readonly[, connect[, options]]]])**
The OpenConnection method syntax has these parts:
| Part | Description |
| connection | An object expression that evaluates to an rdoConnection object that youre opening. |
| environment | An object expressionthat evaluates to an existing rdoEnvironment object. You must provide an rdoEnvironment object. |
| dsName | A string expression that is the name of a registered ODBC data source name or a zero-length string () as described in Settings. |
| prompt | A Variant or constant that determines how the operation is carried out, as specified in Settings. |
| readonly | A Boolean value that is True if the connection is to be opened for read-only access, and False if the connection is to be opened for read/write access. If you omit this argument, the connection is opened for read/write access. |
| connect | A string expression used to pass arguments to the ODBC driver manager for opening the database the connect string as described in Settings. |
| options | A Variant or constant that determines how the operation is carried out, as specified in Settings. |
Settings
The connect argument constitutes the ODBC connect arguments, and is dependent on the ODBC driver. See the Connect property for syntax and typical settings. If the connect argument is an empty string (), the user name and password are taken from the rdoEnvironment objects UserName and Password properties, and a dsName argument must be provided.
If the provided dsName doesnt refer to a valid ODBC data source name, and the Data Source Name (DSN) parameter does not appear in the connect argument an error occurs if prompt is rdDriverNoPrompt; otherwise, the user is prompted to select from a list of registered data source names. If dsName is a zero-length string, the connect string must indicate the driver and server names.
Based on the prompt value, the ODBC driver manager exposes a dialog which prompts the user for connection information such as DSN, user name, and password. Use one of the following constants that defines how the user should be prompted:
| promptConstant | Value | Description |
| rdDriverPrompt | 0 | The driver manager displays the ODBC Data Sources dialog box. The connection string used to establish the connection is constructed from the DSN selected and completed by the user via the dialog boxes, or, if no DSN is chosen and the DataSourceName property is empty (in the case of the RemoteData control), the default DSN is used. |
| rdDriverNoPrompt | 1 | The driver manager uses the connection string provided in dsName and connect. If sufficient information is not provided, the OpenConnection method returns a trappable error. |
| rdDriverComplete | 2 | (Default) If the connection string provided includes the DSN keyword, the driver manager uses the string as provided in connect. Otherwise it behaves as it does when rdDriverPrompt is specified. |
| rdDriverCompleteRequired | 3 | Behaves like rdDriverComplete except the driver disables the controls for any information not required to complete the connection. If the controls are disabled, users cannot select or specify missing arguments. |
You can use the following constant for the options argument:
| options Constant | Value | Description |
| rdAsyncEnable | 32 | Execute operation asynchronously. |
Remarks
When you successfully execute the OpenConnection method, a new rdoConnection object is instantiated and added to the rdoConnections collection, and a network connection is established to the remote server. If the connection cannot be established, no object is created and a trappable error is fired.
Note RDO 2.0 behaves differently than RDO 1.0 in how it handles orphaned references to rdoConnection objects. When you Set a variable already assigned to an rdoConnection object with another rdoConnection object using the OpenConnection method, the existing rdoConnection object is closed and dropped from the rdoConnections collection. In RDO 1.0, the existing object remained open and was left in the rdoConnections collection.
If you set the options argument to rdAsyncEnable, the connection operation is executed asynchronously. That is, control returns to your application before the connection has been established to prevent your application from blocking while the connection is being made. You can check for completion of the connection by polling the rdoConnection objects StillConnecting property, which returns False when the connection operation is complete. You can also code an event procedure for the Connect event which is fired when the connect operation is complete. If you use the Cancel method while waiting for an asynchronous connection to be established, the connection attempt is abandoned.
Before the process of establishing a connection is started, the BeforeConnect event is fired. This event procedure permits you to examine and modify the connect string and prompt levels as needed.
There are a variety of reasons why a connection might not be made. These include but are not limited to the following:
Lack of proper user ID and password.
Incorrect driver or options configuration.
Lack of correct network or server permissions.
The remote server could not be found on the network, or is not operating.
The remote server did not have sufficient resources or connections to permit another user to connect.
DSN-Less Connections
In some cases, it might not be necessary to create and register a Data Source Name (DSN) before attempting to open a connection to a data source. If your remote server uses the named pipes LAN protocol and the default OEMTOANSI settings, you can simply provide the name of the server and ODBC driver in the connect string. You must provide an empty DSN entry in the connect string, or in the dsName parameter as the last argument. If the ODBC driver manager finds a null DSN entry, it attempts to locate it unless it has already determined the driver and server values. The connect string shown below is used to establish a DSN-less connection to a SQL Server named BETAV486:
Connect = "UID=;PWD=;Database=WorkDB;" _
& "Server=BETAV486;Driver={SQL Server}" _
& "DSN='';"
Other Connect String Options
Establishing an rdoConnection may require that the user specified by the UserName property, or UID connect string argument have permission to access the network, the specific data source server, and the chosen database on that server. Failure to meet these qualifications might result in failure to connect.
If you do not specify a database either through the DATABASE parameter of the connect argument or through the data source entry, the database opened when you establish a connection is determined by the default database assigned to the user by the database administrator. In some cases, you can change the default database by executing an action query containing an SQL command such as the Transact SQL USE database statement.
Note The connect part of the OpenConnection method is coded differently than the source part of the OpenDatabase method as used with DAO. The connect part neither requires nor supports use of the ODBC; keyword at the beginning of the connect string. In addition, the connect part does not support use of the LOGINTIMEOUT argument use the LoginTimeout property of the rdoEnvironment object instead.
Use the Close method on the object to close a database associated with an rdoConnection, remove the connection from the rdoConnections collection, and disconnect from the data source.
You can also declare a new rdoConnection object using the Dim statement as follows:
Dim myCn as New rdoConnection
Once instantiated in this manner, you can set the rdoConnection properties as required, and use the EstablishConnection method to open the connection.
For more information about ODBC drivers and the specific connect string arguments they require, see the Help file provided with the driver.