Visual Basic: RDO Data Control

rdoConnection Object

See Also    Example    Properties    Methods    Events

An rdoConnection object represents an open connection to a remote data source and a specific database on that data source, or an allocated but as yet unconnected object, which can be used to subsequently establish a connection.

Remarks

Generally, an rdoConnection object represents a physical connection to the remote data source and corresponds to a single ODBC hDbc handle. A connection to a remote data source is required before you can access its data. You can open connections to remote ODBC data sources and create rdoConnection objects with either the RemoteData control or the OpenConnection method of an rdoEnvironment object.

To establish a connection to a remote server using the rdoConnection object, you can use the OpenConnection method to gather the connect, dsname, readonly and prompt arguments and open the connection. These arguments are then applied to the newly created rdoConnection object. You can also establish connections using the RemoteData control.

Creating Stand Alone rdoConnection Objects

You can also create a new rdoConnection object that is not immediately linked with a specific physical connection to a data source. For example, the following code creates a new stand-alone rdoConnection object:

Dim X as new rdoConnection.

Once created, you can set the properties of a stand-alone rdoConnection object and subsequently use the EstablishConnection method. This method determines how users are prompted based on the prompt argument, and sets the read-only status of the connection based on the readonly argument.

When using this technique, RDO sets the following properties based on rdoEngine default values: CursorDriver, LoginTimeout, UserName, Password and ErrorThreshold. The CursorDriver and LoginTimeout properties can be set in the rdoConnection object itself and the UserName and Password can be set through arguments in the connect string. Once the connection is open, all of these properties are read-only.

When you declare a stand-alone rdoConnection object or use the EstablishConnection method, the object is not automatically appended to the rdoConnections collection. Use the Add or Remove methods to add or delete stand-alone rdoConnection objects to or from the rdoConnections collection. It is not necessary, however to add  an rdoConnection object to the rdoConnections collection before it can be used to establish a connection.

Note   RDO 1.0 collections behave differently than Data Access Object (DAO) collections. When you Set a variable containing a reference to a RDO object like rdoResultset, the existing rdoResultset is not closed and removed from the rdoResultsets collection. The existing object remains open and a member of its respective collection.

In contrast, RDO 2.0 collections do not behave in this manner. When you use the Set statement to assign a variable containing a reference to an RDO object, the existing object is closed and removed from the associated collection. This change is designed to make RDO more compatible with DAO.

Asynchronous Operations

Both the EstablishConnection and OpenConnection methods support synchronous, asynchronous, and event-managed operations. By setting the rdAsyncEnable option, control returns to your application before the connection is established. Once the StillConnecting property returns False, and the Connect event fires, the connection has either been made or failed to complete. You can check the success or failure of this operation by examining errors returned through the rdoErrors collection.

Opening Connections without Data Source Names

In many situations, it is difficult to ensure that a registered Data Source Name (DSN) exists on the target system, and in some cases it is not advisable to create one. Actually, a DSN is not needed to establish a connection if you are using the default network protocol (named pipes) and you know the name of the server and ODBC driver. If this is the case, you can establish a DSN-less connection by following these steps:

1. Set the DSN argument of the connect string to an empty string (DSN='').

2. Include the server name in the connect string.

3. Include the ODBC driver name in the connect string. Since many driver names have more than one word, enclose the name in curly braces { }.

Note   This option is not available if you need to use other than the named pipes network protocol or one of the other DSN-set options such as OEMTOANSI conversion.

For example, the following code opens a read-only ODBC cursor connection against the SQL Server "SEQUEL" and includes a simple error handler:

Sub MakeConnection()
Dim rdoCn As New rdoConnection
On Error GoTo CnEh
With rdoCn
    .Connect = "UID=;PWD=;Database=WorkDB;" _
        & "Server=SEQUEL;Driver={SQL Server}" _
        & "DSN='';"
    .LoginTimeout = 5
    .CursorDriver = rdUseODBC
    .EstablishConnection rdDriverNoPrompt, True
End With
Exit Sub
CnEh:
Dim er As rdoError
    Debug.Print Err, Error
    For Each er In rdoErrors
        Debug.Print er.Description, er.Number
    Next er
    Resume Next
End Sub

Choosing a Specific Database

Once a connection is established, you can manipulate a database associated with the rdoConnection using the rdoConnection object and its methods and properties. For servers that support more than one database per connection, the default database is:

• Assigned to the user name by the database system administrator

• Specified with the DATABASE connect argument used when the rdoConnection is created.

• Specified in the registered ODBC data source entry.

• Selected by using an SQL statement such as USE <database> submitted with an action query.

All queries executed against the server assume this default database unless another database is specifically referenced in your SQL query.

Preparing for Errors when Connecting

There are a variety of reasons why you might be unable to connect to your remote database. Consider the following conditions that can typically prevent connections from completing:

• Your server might not have sufficient connection resources due to administrative settings or licensing restrictions.

• Your user might not have permission to access the network, server, or database with the password provided.

• The server, network or WAN bridges might be down or simply running slower than expected.

Closing the rdoConnection

When you use the Close method against an rdoConnection object, any open rdoResultset, or rdoQuery objects are closed. However, if the rdoConnection object simply loses scope, these objects remain open until the rdoConnection or the objects are explicitly closed. Closing a connection is not recommended when there are incomplete queries or uncommitted transactions pending.

Closing a connection also removes it from the rdoConnections collection. However, the rdoConnection object itself is not destroyed. If needed, you can use the EstablishConnection method to re-connect to the same server using the same settings, or change the rdoConnection object's properties and then use EstablishConnection to connect to another server.

Closing a connection also instructs the remote server to discard any instance-specific objects associated with the connection. For example, server-side cursors, temporary tables or any other objects created in the TempDB database on SQL Server are all dropped.

Working with rdoConnection Methods and Properties

You can manipulate the connection, databases, and queries associated with them using the methods and properties of the rdoConnection object. For example, you can:

• Use the CursorDriver property to determine the type of cursor requested by result sets created against the connection.

• Use the OpenResultset method to create a new rdoResultset object.

• Use the LastQueryResults to reference the last rdoResultset created against this connection.

• Use the QueryTimeout or LoginTimeout properties to specify how long the ODBC driver manager should wait before abandoning a query or connection attempt.

• Use the RowsAffected property to determine how many rows were affected by the last action query.

• Use the Execute method to run an action query or pass an SQL statement to a database for execution.

• Use the CreateQuery method to create a new rdoQuery object.

• Use the Close method to close an open connection, remove the rdoConnection object from the rdoConnections collection, deallocate the connection handle, and terminate the connection.

• Use the Transactions property to determine if the connection supports transactions, which you can implement using the BeginTrans, CommitTrans, and RollbackTrans methods.

• Use the AsyncCheckInterval property to determine how often RDO should poll for a completed asynchronous operation.

• Use the ODBC API with the hDbc property to set connection options.

• Use the Connect property to determine the connect argument used in the OpenConnection method, or the Connect property of the RemoteData control.

rdoConnection Events

The following events are fired as the rdoConnection object is manipulated. These can be used to micro-manage the process of connecting and disconnecting and provide additional retry handling in query timeout situations.

Addressing the rdoConnection Object

The Name property setting of an rdoConnection specifies the data source name (DSN) parameter used to open the connection. This property is often empty as it is not used when making a DSN-less connection. In cases where you specify a different DSN to open each connection, you can refer to any rdoConnection object by its Name property setting using the following syntax. This code Refers to the connection opened against the Accounting DSN:

rdoConnections("Accounting") 

You can also refer to the object by its ordinal number using this syntax (which refers to the first member of the rdoConnections collection):

rdoConnections(0)