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.
' 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.
' 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.
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.
Get ADO: ActiveX Data Objects now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.