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

Set connection = environment.OpenConnection(dsName[, prompt[, readonly[, connect[, options]]]])

The OpenConnection method syntax has these parts:

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 object’s UserName and Password properties, and a dsName argument must be provided.

If the provided dsName doesn’t 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:

You can use the following constant for the options argument:

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 object’s 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.