ADO: ActiveX Data Objects

Opening and Closing a Connection: Implicit Versus Explicit

The Connection object is used to establish a unique physical connection to a given data source. This connection defines how you can obtain, interact with, and manipulate data from the specified source. While a Connection object is always required, you can choose whether to instantiate a connection explicitly or to allow ADO to create one implicitly on your behalf.

Opening a Connection

Example 4-1 illustrates how to open a Recordset object on a table in a data source without explicitly creating a Connection object.

Example 4-1. Implicit Creation of a Connection Object

' declare and instantiate a Recordset
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

' open the Recordset object and implicitly create a Connection
rst.Open "Titles", _
         "DSN=BiblioDSN", _
         adOpenForwardOnly, _
         adLockReadOnly, _
         adCmdTable
'
' do something

' close the Recordset and clean up
rst.Close
Set rst = Nothing

Don’t worry about not understanding the entire example now -- I will explain everything soon. Do notice, however, how easy it is to open a table within a data source. Example 4-1 relies on no other code to first establish a connection; the simple connection string DSN=BiblioDSN tells ADO that the table, Titles, is in the BiblioDSN data source.

Some objects in ADO -- in particular, the Recordset and the Command objects -- do not require a pre-existing Connection object to operate. Both objects can read and write data to a data source, and both need a physical connection to a data source to do so. But the Recordset and the Command objects can create their own Connection objects in the background with information that you supply. The choice of declare and establish a connection with the Connection object or to let the Recordset or Command object handle the work for you.

By using your own Connection object, you gain greater control over your data access and manipulation. For instance, with a Connection object, you can execute queries through stored procedures that reside in a data source or through SQL statements that you explicitly declare to your application at runtime. The Connection object also offers transaction management so that at critical points in your data-manipulation code, the integrity of your data source can be preserved if an error were to occur.

Take a look at Example 4-2, which first explicitly creates and opens a Connection object to establish a connection before opening the table from the database.

Example 4-2. Explicit Creation of a Connection Object

' declare and instantiate a Connection and a Recordset
Dim con As ADODB.Connection
Dim rst As ADODB.Recordset

Set con = New ADODB.Connection
Set rst = New ADODB.Recordset

' first establish a connection to the data source
con.Open "DSN=BiblioDSN"

' now open the recordset using the established Connection
rst.Open "Titles", _
         con, _
         adOpenForwardOnly, _
         adLockReadOnly, _
         adCmdTable

' do something

' close the Recordset and clean up
rst.Close
Set rst = Nothing

' close the Connection and clean up
con.Close
Set con = Nothing

Notice the amount of extra work that is needed to open the Connection object before opening the Recordset object. Instead of passing a connection string to the Open method of the Recordset object, we are passing the already opened Connection object. This longer piece of code is accomplishing exactly what the previous example did in fewer lines. If a connection string is passed to a Recordset, the Recordset object creates its own Connection object from that string. If you pass a Connection object to a Recordset object, a new Connection object is not created. When opening a lot of Recordset objects, it would be advantageous to pass a Connection object, not a connection string, so that only one connection to the database is created. Figure 4-1 shows us the difference between creating Connection objects implicitly versus explicitly.

Figure 4-1. Implicit versus explicit creation of Connection objects

Creating a Connection object should be done implicitly when you need only one or a small number of connections to a data source. If you plan on having multiple recordsets, or views, of the same data source, you should create your own Connection object, which requires fewer system resources and offers better control, as you will learn in the following sections.

Closing a Connection

Although I have not specifically defined how to close a Connection object, you have seen it in all of the code presented thus far. You can use the Close method to close or disconnect the Connection object from the data source. When you use this method, the physical connection is lost, but the Connection object itself remains. It can be reopened with the same properties, or those properties can be altered before the Connection object is opened again. To fully remove the Connection object from memory, to free resources, and to remain respectable in the development community, set the object to the value Nothing, as shown here:

Set con = Nothing

Now that you know how to both establish and break a connection to a data source, we should take a look at the various options that we can use when connecting. These options dictate the ways in which our data is presented to us in the rest of our applications.

Tip

When you are using a client-side Connection object with Remote Data Service ( RDS), the connection with the server is not actually established by the Open method. Instead, RDS waits until a Recordset object is opened on the Connection object.