Establishing an RDO Connection

Before you can reference the data in a remote database, you must establish a connection to the data source. The source could be a remote database server, like SQL Server or Oracle, or another database that has a suitable ODBC driver.

There are a number of ways to establish connections with RDO, as described in the following topics. However, unlike DAO, RDO does not manage connections for your application — it simply collects parameters to be passed to the SQLDriverConnect function and calls the SQLDisconnect function to close the connection. RDO does not cache connections or attempt to share them based on similar DSN entries. When you use the RDO Close method to close a connection, it is closed immediately.

When you are ready to open a connection, the options available to you are as follows:

• Use the RemoteData Control to establish a connection based on its properties and create an rdoConnection object as referenced by its Connection property.
  
  
• Declare an rdoConnection object and use the rdoEnvironment object's OpenConnection method.
  
  
• Create a stand-alone rdoConnection object using the Dim x As New syntax, set its properties, and use the EstablishConnection method.
  
  
• Use the EstablishConnection on an existing rdoConnection object after having either created a stand-alone rdoConnection object, or after having used the Close method on an existing rdoConnection object.

Each of these techniques is designed to address specific programming requirements. For example, in situations where you need to submit the same query to a number of remote databases, you might choose to create a stand-alone rdoConnection object that can be assigned to a number of rdoQuery objects via the ActiveConnection property. In other situations, you might prefer the simplicity of the RemoteData control.

All of these techniques establish a physical link to a data source, like a specific SQL Server or Oracle database server. To establish a connection, you must provide the network location of the data source, the driver type, and a number of optional parameters used to identify the user to the data source.

Once you have established a connection, you can use it to:

• Execute a query that returns one or more result sets using the OpenResultset method.
  
  
• Execute an action query using the Execute method.
  
  
• Create an rdoQuery object to subsequently execute a parameter query or stored procedure.
  
  
• Define a query that exposes one or more stored procedures as methods of the rdoConnection object.
  
  
• Associate the rdoConnection object with a specific rdoResultset object by setting the ActiveConnection property.
  
  
• Add the rdoConnection object to a chosen rdoConnections collection by using the Add method.
  
  
• Remove an rdoConnection object from its parent rdoConnections collection using the Remove method.