How Do I Use the Connection Object in ADO?

 

Microsoft Corporation

April 2, 1998
Updated May 5, 2010

Applies to:
    Microsoft ActiveX Data Objects (ADO)

Note: This article refers to a data access technology that is no longer actively developed. Please review the current data access technologies and base your future development on them rather than the technology referred to in this article.

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.

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()
   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.

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

   ' Open a connection using an ODBC DSN "Pubs".
   cnn.ConnectionString = "DSN=Pubs;UID=MyUserName;PWD=MyPassword;"
   cnn.Open

   ' 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

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:

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

   ' Set properties of the Connection.
   cnn.ConnectionString = "DSN=Pubs;UID=MyUserName;PWD=MyPassword;"
   cnn.ConnectionTimeout = 30

   ' Open the connection.
   cnn.Open

   ' 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

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:

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

   ' Open a connection by referencing the ODBC driver.
   cnn.ConnectionString = "driver={SQL Server};" & _
      "server=MySqlServer;uid=MyUserName;pwd=MyPassword;database=pubs"
   cnn.Open

   ' 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

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.

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

   'Set the provider property to the OLE DB Provider for ODBC.
   cnn.Provider = "MSDASQL"

   ' Open a connection using an ODBC DSN.
   cnn.ConnectionString = "driver={SQL Server};" & _
      "server=MySqlServer;uid=MyUserName;pwd=MyPassword;database=pubs"
   cnn.Open

   ' 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 Sub ConnectionExample5()
   Dim cnn As ADODB.Connection
   Set cnn = New ADODB.Connection

   'Set the provider property to the OLE DB Provider for ODBC.
   cnn.Provider = "MSDASQL"

   ' Open a connection using an ODBC DSN.
   cnn.ConnectionString = "driver={SQL Server};" & _
      "server=MySqlServer;uid=MyUserName;pwd=MyPassword;database=pubs"
   cnn.Open

   ' 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

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.)

Sub ConnectionExample6()
   Dim cnn As ADODB.Connection
   Dim rs As ADODB.Recordset

   Set cnn = New ADODB.Connection

   ' Open a connection by referencing the ODBC driver.
   cnn.ConnectionString = "driver={SQL Server};" & _
      "server=MySqlServer;uid=MyUserName;pwd=MyPassword;database=pubs"
   cnn.Open

   ' Create a Recordset by executing an SQL statement.
   Set rs = cnn.Execute("Select * From authors")

   ' Show the first author.
   MsgBox rs("au_fname") & " " & rs("au_lname")

   ' Close the connection.
   rs.Close

End Sub

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.

Sub ConnectionExample7()
   Dim cnn As ADODB.Connection
   Dim rs As ADODB.Recordset

   Set cnn = New ADODB.Connection

   ' Open a connection by referencing the ODBC driver.
   cnn.ConnectionString = "driver={SQL Server};" & _
      "server=MySqlServer;uid=MyUserName;pwd=MyPassword;database=pubs"
   cnn.Open

   ' Send a Delete statement to the database.
   cnn.Execute ("Delete From authors Where au_id = '011-01-0111'")

   ' Find out how many rows were affected by the Delete.
   Set rs = cnn.Execute("Select @@rowcount")
   ' Display the first field in the recordset.
   MsgBox rs(0) & " rows deleted"

   ' Close the connection.
   rs.Close

End Sub

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.

Sub ConnectionExample8()
   Dim cnn As ADODB.Connection
   Dim rs As ADODB.Recordset

   Set cnn = New ADODB.Connection

   ' Open a connection by referencing the ODBC driver.
   cnn.ConnectionString = "driver={SQL Server};" & _
      "server=MySqlServer;uid=MyUserName;pwd=MyPassword;database=pubs"
   cnn.Open

   ' Create a recordset by running a stored procedure.
   Set rs = cnn.Execute("Exec byroyalty 50")

   ' Loop through the recordset and show the author's ID.
   Do While Not rs.EOF
      MsgBox rs("au_id")
      rs.MoveNext
   Loop

   ' Close the connection.
   rs.Close

End Sub