Microsoft's ActiveX Data Objects (ADO) is a set of Component
Object Model (COM) objects for accessing data sources. It provides
a layer between programming languages and OLE DB (a means of
accessing data stores, whether they be databases or otherwise, in a
uniform manner). ADO allows a developer to write programs that
access data without knowing how the database is implemented. You
must be aware of your database for connection only. No knowledge of
SQL is required to access a database when using ADO, although one
can use ADO to execute arbitrary SQL commands. The disadvantage of
this (i.e. using SQL directly) is that it introduces a dependency
upon the type of database used.
It is positioned as a successor to Microsoft's earlier object
layers for accessing data sources, including RDO (Remote Data
Objects) and DAO (Data Access Objects). ADO was introduced by
Microsoft in October 1996.
ADO is made up of four collections and twelve objects:
ADO collections
Fields
This collection contains a set of Field objects. The Collection
can be used in either a Recordset object or in a Record object. In
a Recordset object, each of the Field objects that make up the
Fields collection corresponds to a column in that Recordset object.
In a Record object, a Field can be an absolute or relative URL that
points into a tree-structured namespace (used for semi-structured
data providers like the Microsoft OLE DB Provider for Internet
Publishing) or as a reference to the default Stream object
associated with that Record object.
Properties
An object can have more than one Property object, which are
contained in the object's Properties collection.
Parameters
A Command object can have several Parameter commands to change its
predefined behaviour, and each of the Parameter objects are
contained in the Command object's Parameters collection
Errors
All provider created errors are passed to a collection of Error
objects, while the Errors collection itself is contained in a
Connection object. When an ADO operation creates an error, the
collection is cleared and a new group of Error objects are created
in the collection.
ADO objects
Connection
The connection object is ADO's connection to a data store via OLE
DB. The connection object stores information about the session and
provides methods of connecting to the data store. As some data
stores have different methods of establishing a connection, some
methods may not be supported in the connection object for
particular OLE DB providers. A connection object connects to the
data store using its 'Open' method with a connection string which
specifies the connection as a list of key value pairs (for example:
"Provider='SQLOLEDB';Data Source='TheSqlServer'; Initial
Catalog='Northwind';Integrated Security='SSPI';"). The start of
which must identify the type of data store connection that the
connection object requires. This must be one of:
* an OLE DB provider (for example SQLOLEDB), using the syntax
"provider=";
* a file name, using the syntax "file name=";
* a remote provider and server (see RDS), using the syntax "Remote
provider=" and "Remote server="; or
* an absolute URL, using the syntax "URL="
Command
After the connection object establishes a session to the data
source, instructions are sent to the data provider via the command
object. The command object can send SQL queries directly to the
provider through the use of the CommandText property, send a
parameterised query or stored procedure through the use of a
Parameter object or Parameters collection or run a query and return
the results to a dataset object via the Execute method. There are
several other methods that can be used in the Command object
relating to other objects, such as the Stream, RecordSet or
Connection objects.
Recordset
A recordset is a group of records, and can either come from a base
table or as the result of a query to the table. The RecordSet
object contains a Fields collection and a Properties collection.
The Fields collection is a set of Field objects, which are the
corresponding columns in the table. The Properties collection is a
set of Property objects, which defines a particular functionality
of an OLE DB provider. The RecordSet has numerous methods and
properties for examining the data that exists within it. Records
can be updated in the recordset by changing the values in the
record and then calling on the Update or UpdateBatch method. Adding
new records is performed through the AddNew function and then by
calling on the Update or UpdateBatch method. Records are also
deleted in the recordset with the Delete method and then by calling
on the Update method. However, if for some reason the deletion
cannot occur, such as because of violations in referential
integrity, then the recordset will remain in edit mode after the
call to the Update method. The programmer must explicitly call on
the CancelUpdate function to cancel the update. Additionally, ADO
can rollback transactions (if this is supported) and cancel batch
updates. Recordsets can also be updated in one of three ways: via
an immediate update, via a batch update, or through the use of
transactions:
Immediate
The recordset is locked using the adLockOptimistic or
adLockPessimistic lock. The data are updated at the data source
after the record is changed and the Update method is called.
Batch
The recordset is locked using adLockBatchOptimistic and each time
Update is called the data are updated in a temporary buffer.
Finally, when UpdateBatch is called the data are completely updated
back at the data source. This has the advantage of it all being
done in memory, and if a problem occurs then UpdateCancel is called
and the updates are not sent to the data source
Transaction
If the OLE DB provider allows it, transactions can be used. To
start the transaction, the programmer invokes the BeginTrans method
and does the required updates. When they are all done, the
programmer invokes the CommitTrans method. RollbackTrans can be
invoked to cancel any changes made inside the transaction and
rollback the database to the state before the transaction
began
Record
This object represents one record in the database, and contains a
fields collection. A RecordSet consists of a collection of Record
objects.
Stream
A stream, mainly used in a RecordSet object, is a means
of reading and writing a stream of bytes. It is mostly used to save
a recordset in an XML format, to send commands to an OLE DB
provider as an alternative to the CommandText object and to contain
the contents of a binary or text file.
Parameter
A parameter is a means of altering the behaviour of a common piece
of functionality, for instance a stored procedure might have
different parameters passed to it depending on what needs to be
done; these are called parameterised commands.
Field
Each Record object contains many fields, and a RecordSet object
has a corresponding Field object also. The RecordSet object's Field
object corresponds to a column in the database table that it
references.
Property
This object is specific to the OLE DB provider and defines an
ability that the provider has implemented. A property object can be
either a built-in property - it is a well defined property
implemented by ADO already and thus cannot be altered - or can be a
dynamic property - defined by the underlying data provider and can
be changed
Error
When an OLE DB provider error occurs during the use of ADO, an
Error object will be created in the Errors collection. Other errors
do not go into an Error object, however. For instance, any errors
that occur when manipulating data in a RecordSet or Field object
are stored in a Status property.
Basic Usage
Some basic steps are required in order to be able to access and
manipulate data using ADO:
1. Create a connection object to connect to the database.
2. Create a recordset object in order to receive data in.
3. Open the connection
4. Populate the recordset by opening it and passing the desired
table name or SQL statement as a parameter to open function.
5. Do all the desired searching/processing on the fetched
data.
6. Commit the changes you made to the data (if any) by using
Update or UpdateBatch methods.
7. Close the recordset
8. Close the connection