Connection

The connection object provides the connection between the data source and the application. The connection object is used to create an open connection to your data source.
The main way to open a connection is with the Connection.Open method.
If you want to access a database multiple times you should establish a connection using the Connection object and hold this connection in a variable. So it can be re-used.

You can create an invisible connection by passing a connection string via the Command or Recordset object although this is not encouraged and should really only be used when executing a single query

Connection pooling preserves and reuses your connections for each queary. Connections can be reused as long as the connection strings are identical.

An asynchronous call returns control to the calling program before the operation is completed and allows execution of the code to continue

Connection Properties and Methods

Creating a Connection

Connection.Open [ConnectionString] [, UserID] [, Password] [, OpenOptions]
You can open a connection by using Connection.Open method.
dbADOConnection = new ADODB.Connection
dbADOConnection.Open ConnectionString, UserID, Password, OpenOptions

ConnectionString - contains the information needed to establish a connection to a data source. The string is composed of various parameter values, sperated by semicolons, and is database specific.
UserID -
Password -
OpenOptions - [Optional] specifies how the connection object should be returned, synchronously (the default) or asynchonously.

Using an Implicit Connection

If you want to quickly return a single recordset of data you do not have to explicitly create a connection.
You can use either the Command.Execute or RecordSet.Open when you do not want to reuse the database connection.

Command.Execute

RecordSet.Open Source, ActiveConnection, CursorType, LockType, Options
where the activeconnection could be a literal string or a connection object representing an open connection
Source -
ActiveConnection -
CursorType -
LockType -
Options -

Connection.Execute [CommandText] [, RecordsAffected ] [, Options]
CommandText - can contain any executable string typically a SQL statement
RecordsAffected - is a return value used to indicate how many reocrds have been affected
Options - this should always be used to tell the provider how to interpret the CommandText type, ie the CommandType. This is critical to optimising the execution efficiency of your command

Cursor Location

ADO supports the creation of cursors on both the client and server side.
The Cursor location indicates which engine manages the records in the recordset, either serverside or clientside
A cursor is a collection of pointers to a resultset returned by the datasource. Cursors can support features such as scrolling forward and backwards, to update the recordset and to view data changed by other users. The more features you ask for the more resources that will be required.
Server side cursors will make multiple trips to the data source to work with rows from the resultset. The number of trips is affected by the CacheSize property of the recordset object, which indicates how many rows the data provider will keep in its cache with each call to the data source. The default cachesize is 1.
When when client side cursors are employed, the entire resultset is sent to the client using one SQL statement with the rows being held in memory

Server side cursors are more efficient when an application is likely to address a small percentage of rows in the resultset. Fewer round trips to the data source will be required. You should note, however that applications should be designed to ask for only rows required by the client.

Unfortunately, not all types of cursors or locks are recognised by all providers. No error will occur if an unsupported value is used for the CursorType or LockType. The provider will just change it to one that it does support.

The location of the cursor can be specified by setting the CursorLocation property of either the Connection or Recordset object before creating the record set. You should always explicitely specify your cursor location. Server side cursors are the default in ADO.NET all data source support both methods.

If the "CursorLocation" property is set to use "adClient" the only valid setting for the "CursorType property is adOpenStatic

Cursor Type

Cursors define the functionality of a specific recordset
The ADO cursor engine sits between ADO and ??
Some cursors allow you to see data in real time (data changes as they are being made when you are working with the recordset)
Some allow only a snapshot of the data at a particular point in time.
Some allow you to move up and down and others only in a forward direction.

Locking

The LockType property indicates the type of lock in effect on a record for editing
To use BatchUpdating (adLockBatchOptimistic) you should select either a keyset or static cursor.