Share via

Connection Object

Represents an open connection to a data source.


A Connection object represents a unique session with a data source. In the case of a client/server database system, it may be equivalent to an actual network connection to the server. Depending on the functionality supported by the provider, some collections, methods, or properties of a Connection object may not be available.

With the collections, methods, and properties of a Connection object, you can do the following:

  • Configure the connection before opening it with the ConnectionString, ConnectionTimeout, and Mode properties. ConnectionString is the default property of the Connection object.

  • Set the CursorLocation property to client to invoke the Microsoft Cursor Service for OLE DB, which supports batch updates.

  • Set the default database for the connection with the DefaultDatabase property.

  • Set the level of isolation for the transactions opened on the connection with the IsolationLevel property.

  • Specify an OLE DB provider with the Provider property.

  • Establish, and later break, the physical connection to the data source with the Open and Close methods.

  • Execute a command on the connection with the Execute method and configure the execution with the CommandTimeout property.

    Note   To execute a query without using a Command object, pass a query string to the Execute method of a Connection object. However, a Command object is required when you want to persist the command text and re-execute it, or use query parameters.

  • Manage transactions on the open connection, including nested transactions if the provider supports them, with the BeginTrans, CommitTrans, and RollbackTrans methods and the Attributes property.

  • Examine errors returned from the data source with the Errors collection.

  • Read the version from the ADO implementation used with the Version property.

  • Obtain schema information about your database with the OpenSchema method.

You can create Connection objects independently of any other previously defined object.

You can execute named commands or stored procedures as if they were native methods on a Connection object, as shown below. When a named command has the same name as that of a stored procedure, invoke the "native method call" on a Connection object always execute the named command instead of the store procedure.

Note   Do not use this feature (calling a named command or stored procedure as if it were a native method on the Connection object) in a Microsoft® .NET Framework application, because the underlying implementation of the feature conflicts with the way the .NET Framework interoperates with COM.

Execute a command as a native method of a Connection object

To execute a command, give the command a name using the Command object Name property. Set the Command object's ActiveConnection property to the connection. Then issue a statement where the command name is used as if it were a method on the Connection object, followed by any parameters, then followed by a Recordset object if any rows are returned. Set the Recordset properties to customize the resulting Recordset. For example:

Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset
cnn.Open "..."
cmd.Name = "yourCommandName"
cmd.ActiveConnection = cnn
'Your command name, any parameters, and an optional Recordset.
cnn.yourCommandName "parameter", rst

Execute a stored procedure as a native method of a Connection object

To execute a stored procedure, issue a statement where the stored procedure name is used as if it were a method on the Connection object, followed by any parameters. ADO will make a "best guess" of parameter types. For example:

Dim cnn As New ADODB.Connection
'Your stored procedure name and any parameters.
cnn.sp_yourStoredProcedureName "parameter"

The Connection object is safe for scripting.

This section contains the following topic.

See Also

Command Object | Errors Collection | Properties Collection | Recordset Object | Appendix A: Providers