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