Overview -- ADO Objects
The following elements are key parts of the ADO programming model:
 | Connection |
| Command |
| Parameter |
| Recordset |
| Field |
| Error |
| Property |
| Collection |
| Event |
Access from your application to a data source is through a connection, the environment necessary for exchanging data. Your application can gain access to a data source directly (sometimes called a two-tier system), or indirectly (sometimes called a three-tier system) through an intermediary like the Microsoft® Internet Information Server.
The object model embodies the concept of a connection with the Connection object.
A transaction delimits the beginning and end of a series of data access operations that transpire across a connection. ADO ensures that changes to a data source resulting from operations in a transaction either all occur successfully, or not at all.
If you cancel the transaction or one of its operations fails, then the ultimate result will be as if none of the operations in the transaction had occurred. The data source will be as it was before the transaction began.
The object model does not explicitly embody the concept of a transaction, but represents it with a set of Connection object methods.
ADO accesses data and services from OLE DB providers. The Connection object is used to specify a particular provider and any parameters.
Open Method (ADO Connection)Opens a connection to a data source. Syntax connection.Open ConnectionString, UserID, Password, OpenOptions Parameters ConnectionString Optional. A String containing connection information. UserID Optional. A String containing a user name to use when establishing the connection. Password Optional. A String containing a password to use when establishing the connection. OpenOptions Optional. A ConnectOptionEnum value. If set to adConnectAsync, the connection will be opened asynchronously. A ConnectComplete event will be issued when the connection is available. Remarks Using the Open method on a Connection object establishes the physical connection to a data source. After this method successfully completes, the connection is live and you can issue commands against it and process the results. Use the optional ConnectionString argument to specify a connection string containing a series of argument = value statements separated by semicolons. The ConnectionString property automatically inherits the value used for the ConnectionString argument. Therefore, you can either set the ConnectionString property of the Connection object before opening it, or use the ConnectionString argument to set or override the current connection parameters during the Open method call. If you pass user and password information both in the ConnectionString argument and in the optional UserID and Password arguments, the UserID and Password arguments will override the values specified in ConnectionString. When you have concluded your operations over an open Connection, use the Close method to free any associated system resources. Closing an object does not remove it from memory; you can change its property settings and use the Open method to open it again later. To completely eliminate an object from memory, set the object variable to Nothing.
|
Close Method (ADO)Closes an open object and any dependent objects. Syntax object.Close Remarks Use the Close method to close either a Connection object or a Recordset object to free any associated system resources. Closing an object does not remove it from memory; you can change its property settings and open it again later. To completely eliminate an object from memory, set the object variable to Nothing. Connection Using the Close method to close a Connection object also closes any active Recordset objects associated with the connection. A Command object associated with the Connection object you are closing will persist, but it will no longer be associated with a Connection object; that is, its ActiveConnection property will be set to Nothing. Also, the Command object's Parameters collection will be cleared of any provider-defined parameters. You can later call the Open method to re-establish the connection to the same or another data source. While the Connection object is closed, calling any methods that require an open connection to the data source generates an error. Closing a Connection object while there are open Recordset objects on the connection rolls back any pending changes in all of the Recordset objects. Explicitly closing a Connection object (calling the Close method) while a transaction is in progress generates an error. If a Connection object falls out of scope while a transaction is in progress, ADO automatically rolls back the transaction.
|
A command issued across an established connection manipulates the data source in some way. Typically the command adds, deletes, or updates data in the data source, or retrieves data in the form of rows in a table.
The object model embodies the concept of a command with the Command object. The existence of a Command object gives ADO the opportunity to optimize the execution of the command.
Execute Method (ADO Command)Executes the query, SQL statement, or stored procedure specified in the CommandText property. Syntax For a row-returning Command: Set recordset = command.Execute( RecordsAffected, Parameters, Options ) For a non–row-returning Command: command.Execute RecordsAffected, Parameters, Options Return Value Returns a Recordset object reference. Parameters RecordsAffected Optional. A Long variable to which the provider returns the number of records that the operation affected. Parameters Optional. A Variant array of parameter values passed with an SQL statement. (Output parameters will not return correct values when passed in this argument.)
|
Often, commands require variable parts, parameters, that can be altered before you issue the command. For example, you could issue the same data retrieval command repeatedly, but each time vary your specification of the information to be retrieved.
Parameters are especially useful for executing commands that behave like functions. In this case you know what the command does, but not necessarily how it works. For example, you issue a bank transfer command that debits one account and credits another. You specify the amount of money to be transferred as a parameter.
The object model embodies the concept of a parameter with the Parameter object.
If your command is a query that returns data as rows of information in a table (that is, it is a row-returning query), then those rows are placed in local storage.
The object model embodies this storage as a Recordset object. However, there is no object that represents a single row of a Recordset.
The Recordset is the primary means of examining and modifying data in the rows. The Recordset object allows you to:
| Specify which rows are available for examination. |
| Traverse the rows. |
| Specify the order in which the rows may be traversed. |
| Add, change, or delete rows. |
| Update the data source with changed rows. |
| Manage the overall state of the Recordset |
Open Method (ADO Recordset)Opens a cursor. Syntax recordset.Open Source, ActiveConnection, CursorType, LockType, Options Parameters Source Optional. A Variant that evaluates to a valid Command object variable name, an SQL statement, a table name, a stored procedure call, or the file name of a persisted Recordset. ActiveConnection Optional. Either a Variant that evaluates to a valid Connection object variable name, or a String containing ConnectionString parameters. CursorType Optional. A CursorTypeEnum value that determines the type of cursor that the provider should use when opening the Recordset. Can be one of the following constants (see the CursorType property for definitions of these settings).
LockType Optional. A LockTypeEnum value that determines what type of locking (concurrency) the provider should use when opening the Recordset. Can be one of the following constants (see the LockType property for more information).
Remarks Using the Open method on a Recordset object opens a cursor that represents records from a base table, the results of a query, or a previously saved Recordset. Use the optional Source argument to specify a data source using one of the following: a Command object variable, an SQL statement, a stored procedure, a table name, or a complete file path name. The ActiveConnection argument corresponds to the ActiveConnection property and specifies in which connection to open the Recordset object. If you pass a connection definition for this argument, ADO opens a new connection using the specified parameters. You can change the value of this property after opening the Recordset to send updates to another provider. Or, you can set this property to Nothing (in Microsoft Visual Basic) to disconnect the Recordset from any provider. For the other arguments that correspond directly to properties of a Recordset object (Source, CursorType, and LockType), the relationship of the arguments to the properties is as follows:
Note For Recordset objects whose Source property is set to a valid Command object, the ActiveConnection property is read-only, even if the Recordset object isn't open. If you pass a Command object in the Source argument and also pass an ActiveConnection argument, an error occurs. The ActiveConnection property of the Command object must already be set to a valid Connection object or connection string. If you pass something other than a Command object in the Source argument, you can use the Options argument to optimize evaluation of the Source argument. If the Options argument is not defined, you may experience diminished performance because ADO must make calls to the provider to determine if the argument is an SQL statement, a stored procedure, or a table name. If you know what Source type you're using, setting the Options argument instructs ADO to jump directly to the relevant code. If the Options argument does not match the Source type, an error occurs. The default for the Options argument is adCommandFile if no connection is associated with the recordset. This will typically be the case for persisted Recordset objects. If the data source returns no records, the provider sets both the BOF and EOF properties to True, and the current record position is undefined. You can still add new data to this empty Recordset object if the cursor type allows it. When you have concluded your operations over an open Recordset object, use the Close method to free any associated system resources. Closing an object does not remove it from memory; you can change its property settings and use the Open method to open it again later. To completely eliminate an object from memory, set the object variable to Nothing. Call Open with no operands, and before the ActiveConnection property is set, to create an instance of a Recordset created by appending fields to the Recordset Fields collection.
|
||||||||||||||||||||||||||
Recordset CloseUsing the Close method to close a Recordset object releases the associated data and any exclusive access you may have had to the data through this particular Recordset object. You can later call the Open method to reopen the Recordset with the same or modified attributes. While the Recordset object is closed, calling any methods that require a live cursor generates an error. If an edit is in progress while in immediate update mode, calling the Close method generates an error; call the Update or CancelUpdate method first. If you close the Recordset object during batch updating, all changes since the last UpdateBatch call are lost.
|
A row of a Recordset consists of one or more fields. If you envision the Recordset as a two-dimensional grid, the fields line up to form columns. Each field (column) has among its attributes a name, a data type, and a value. It is this value that contains the actual data from the data source.
The object model embodies a field as a Field object.
In order to modify data in the data source, you modify the value of Field objects in Recordset rows. Ultimately, changes to a Recordset are propagated to the data source. As an option, the transaction management methods on the Connection object can guarantee that the changes succeed or fail in unison.
Errors can occur at any time in your application, usually as the result of not being able to establish a connection, execute a command, or perform an operation on an object in a suitable state (for example, attempting to use a Recordset object that has not been initialized).
The object model embodies an error as an Error object.
Any given error produces one or more Error objects. The next error that occurs will discard the previous set of Error objects.
Each ADO object has a set of unique properties that either describe or control the behavior of that object.
There are two types of properties: built-in and dynamic. Built-in properties are part of the ADO object and are always available. Dynamic properties are added to the ADO object's Properties collection by the underlying data provider, and exist only while that provider is being used.
The object model embodies a property as a Property object.
ADO provides collections, a type of object that conveniently contains other objects of a particular type. The objects in the collection can be retrieved with a collection method either by name, as a text string, or by ordinal, as an integer number.
ADO provides four types of collections:
| The Connection object has the Errors collection, which contains all Error objects created in response to a single failure involving the data source. |
| The Command object has the Parameters collection, which contains all Parameter objects that apply to that Command object. |
| The Recordset object has the Fields collection, which contains all Field objects that define the columns of that Recordset object. |
| In addition, the Connection, Command, Recordset, and Field objects all have a Properties collection, which contains all the Property objects that apply to their respective containing objects. |
ADO objects possess properties where you set or retrieve values with common data types like INTEGER, CHARACTER, or BOOLEAN. However, it's useful to think of certain properties as returning values of data type "COLLECTION OBJECT." The collection object, in turn, has methods to store and retrieve other objects suitable for the collection.
For example, you can think of the Recordset object as having a Properties property that returns a collection object. That collection object has methods to store and retrieve Property objects describing attributes of that Recordset.
ADO 2.0 introduces the concept of events to the programming model. Events are notifications that certain operations are about to occur, or have already occurred. You can use events, in general, to efficiently orchestrate an application consisting of several asynchronous tasks.
The object model does not explicitly embody events, but represents them as calls to event handler routines.
Event handlers called before the operation starts offer you the opportunity to examine or modify the operation parameters, then either cancel or allow the operation to complete.
Event handlers called after an operation completes notify you at the completion of an asynchronous operation. ADO 2.0 introduces several operations that have been enhanced to optionally execute asynchronously. For example, an application that starts an asynchronous Recordset.Open operation is notified by an execution complete event when the operation concludes.
There are two families of events:
| ConnectionEvents—Events are issued when transactions on a connection begin, are committed, or rolled back; when Commands execute; and when Connections start or end. |
| RecordsetEvents—Events are issued to report the progress of data retrieval; when you navigate through the rows of a Recordset object; when you change a field in a row of a recordset, change a row in a recordset, or make any change in the entire recordset. |