Microsoft Corporation

April 2, 1998
Updated March 30, 2004

Applies to:
    Microsoft® ActiveX® Data Objects (ADO)

Summary: This is the first in a series of columns that will explore the individual objects in Microsoft ActiveX Data Objects (ADO).

Contents
What Is a Connection Object?
What Are the Connection Object's Methods and Properties?
How Do I Use the Connection Object to Connect to a Data Store?
How Do I Use the Connection Object to Execute a Command?

What Is a Connection Object?
A Connection object represents a physical connection to a data store. To create a Connection object, you will supply the name of either an ODBC data store or an OLE DB provider. When you open the Connection object, you attempt to connect to the data store. The State property of the Connection object tells you whether you succeeded or failed. You can send SQL statements or run stored procedures by using the Execute method of the Connection object. If the command you send to the data store returns records, a Recordset object will be created automatically. You close the Connection object when you are through with it.

What Are the Connection Object's Methods and Properties?
The following table lists some of the more commonly used methods of the Connection object.

The following table lists some of the more commonly used methods of the Connection object.

Method	Description
Open	Opens a connection to a data store.
Close	Closes a connection and any dependent objects.
Execute	Executes the specified query, SQL statement, stored procedure, or provider-specific text.
BeginTrans	Begins a new transaction.
CommitTrans	Saves any changes and ends the current transaction. It may also start a new transaction.
RollbackTrans	Cancels any changes made during the current transaction and ends the transaction. It may also start a new transaction.

The following table lists some of the more commonly used properties of the Connection object.

Property	Description
ConnectionString	Contains the information used to establish a connection to a data store.
ConnectionTimeout	Indicates how long to wait while establishing a connection before terminating the attempt and generating an error.
CommandTimeout	Indicates how long to wait while executing a command before terminating the attempt and generating an error.
State	Indicates whether a connection is currently open, closed, or connecting.
Provider	Indicates the name of the provider used by the connection.
Version	Indicates the ADO version number.
CursorLocation	Sets or returns a value determining who provides cursor functionality.

How Do I Use the Connection Object to Connect to a Data Store?
To use a Connection object, simply specify a connection string, which identifies the data store you want to work with, and then call the Open method to connect.

The easiest way to open a connection is to pass the connection string information to the Open method. To determine whether the Connection object worked, you can use the State property of the Connection object. State returns adStateOpen if the Connection object is open and adStateClosed if it isn't. Here is an example of connecting to SQL Server by using an ODBC data store:

Sub ConnectionExample1() Sub  ConnectionExample1()
    Dim  cnn  As  ADODB.Connection
    Set  cnn  =   New  ADODB.Connection

    '  Open a Connection using an ODBC DSN named "Pubs".
   cnn.Open  " Pubs " ,  " MyUserName " ,  " MyPassword "

    '  Find out if the attempt to connect worked.
    If  cnn.State  =  adStateOpen  Then
       MsgBox   " Welcome to Pubs! "
    Else
       MsgBox   " Sorry. No Pubs today. "
    End   If

    '  Close the connection.
   cnn.Close

End Sub

If you need to connect to only one data store, the procedure followed in the above code is the easiest way. Alternatively, you can create a Connection object and set the ConnectionString property before calling the Open method. This approach allows you to connect to one data store and then reuse the Connection object to connect to another data store.

This method also gives you the opportunity to set other properties of the Connection object before connecting. For instance, you might want to set the connection time-out:

 

 

This syntax for the ConnectionString property assumes that the data store has already been created by using the ODBC Administrator (or in code). It is becoming increasingly popular to not have to rely on existing ODBC data stores. This eases the setup burden. The next example shows an alternative method for connecting to SQL Server, relying merely on the existence of the ODBC driver itself:

 

Today there are a wide variety of ODBC drivers you can use with ADO to talk to data. In the future, there will be more OLE DB providers available to connect to data stores. The Microsoft® OLE DB Provider for ODBC is currently the default provider for ADO. You can use a different provider by setting the Provider property of the Connection object.

 

 

In the code above, setting the Provider property is not necessary because the OLE DB Provider for ODBC is the default provider for ADO. However, this shows you how you would change the provider when you want to use other OLE DB providers.

How Do I Use the Connection Object to Execute a Command?
The Execute method is used to send a command (an SQL statement or some other text) to the data store. If the SQL statement returns rows, a Recordset object is created. (The Execute method always returns a Recordset object, but it is a closed Recordset if the command doesn't return results.)

Remember that the returned Recordset object from connection.execute is always a read-only, forward-only cursor. If you need a Recordset object with more functionality, you should first create a Recordset object with the desired property settings and then use the Recordset object's Open method to execute the query and return the desired cursor type.

In the following example, the command passed to the data source is a Delete statement. Because no rows are returned, you do not need to explicitly use a Recordset object. How many rows were deleted? You can use the recordsAffected parameter to find out.

 

In the next example, the command passed to the data store specifies the name of a stored procedure to run. Because rows are returned, you do need to use a Recordset object.