With the release of Visual Basic 6, a new, component-based data access standard has emerged from Microsoft. ActiveX Data Objects (ADO) and the underlying OLE DB interface have matured into a powerful technology designed to fulfill the vision of the Microsoft Universal Data Access initiative. The objective of Universal Data Access is to allow applications to access information from any location, regardless of where or how it is stored. Using ADO, you can easily access information from a variety of sources such as relational databases, mainframes, directory services, and Web-based content. As this technology continues to develop in the future, its open architecture will provide the capability to support many other nontraditional data sources.

In this chapter, we first discuss the architecture behind this new technology and examine each component in detail. Next, we’ll cover the techniques necessary to use ADO in your VB programs. The final portion of this chapter describes several of the unique features available when using ADO.

Microsoft’s newest champion of data access methods sports an ActiveX affiliation. Adding to Microsoft’s alphabet soup of data access and management object libraries including DAO, RDO, OLE-DB, DMO, and ODBC, ActiveX Data Objects provide a slim and trim data access method designed for use in Active Server Pages, Visual Basic applications, and anywhere a developer could benefit from high speed, ease of use, low memory overhead, and a simplied object hierarchy.

What does ADO mean to the developer? With the release of ADO 2.0, a great deal of flexibility is provided to the developer. The combination of ADO/OLE-DB provider presents a high and low set of interfaces for data access. Furthering the functionality of ADO, Remote Data Service 1.5 has been combined with the ADO programming model to provide a simplified yet powerful and efficient method for pulling recordsets down to the client for reduced server overhead. With such a powerful model at their fingertips, its would seem obvious that familiarity with such a simplified data access method would be wise, prior to taking the exam.

Take a careful look at the ADO object model. Its simplified over predecessors, so it should not be a daunting task. Be familiar with the 7 objects and 4 collections, including the proper syntax for establishing a collection and the minimum commands for doing so.

— by Michael Lane Thomas, MCSE+I, MCSD, MCT, A+

Microsoft ADO is a set of high-level objects that encapsulate the data access functionality of OLE DB .ADO is a provider-neutral object model designed for use in many languages, including VB, C++, Java, and VBScript .Regardless of the development language and the data platform, the ADO model provides a consistent set of objects and methods for data access. ADO supports nearly all of the functionality of previous data access methods such as Data Access Objects (DAO), Remote Data Objects (RDO), and Open Database Connectivity (ODBC). In addition to high-performance data access functionality, ADO provides the following benefits:

• Simple object model facilitates development
• Minimizes network traffic and client memory requirements
• Improved scalability and built-in support for resource pooling
• Free-threaded objects for improved performance
• Asynchronous operations, including event notification
• Improved support for stored procedures and parameters
• Supports multiple result sets and hierarchical record sets
• Batch record updates and improved record set resynchronization
• Supports client-side and server-side cursor locations
• Record sets support local find, sort, and filter operations
• Record sets can be fabricated without a database connection
• Record sets can be persisted (saved) to a physical file

It is very important that you are aware of the difference in the roles played by ADO and OLE DB. ADO is simply an application-level object interface that exposes the functionality of OLE DB system-level functions. In fact, you do not even need to use ADO to use OLE DB technology because nearly every feature of ADO could be accessed directly from OLE DB via system API calls. Although this would provide a slightly better data access performance, doing so is not recommended because it would increase the complexity of your application and lengthen your development time.

In the fall of 1996, Microsoft released the first versions of OLE DB, ADO, and the Advanced Data Connector. The Advanced Data Connector, a set of remote data access components, has since been replaced by Remote Data Services (RDS). RDS, in conjunction with the Remote Provider for ADO (sometimes referred to as ADO/R), provides the capability to access remote data in a distributed environment. Along with ODBC, these are the core components of the Microsoft Data Access Components (MDAC) package.

In July 1998, Microsoft released version 2 of the MDAC package. Included in this release are new and enhanced versions of all the components: OLE DB 2, ADO 2 (including an updated RDS), and ODBC 3.5. All of the components in this release have been highly optimized for speed and reliability for both desktop and distributed data access scenarios. Together, these technologies provide the tools necessary to realize and implement Universal Data Access.

At the heart of Universal Data Access lies OLE DB; an interface technology based on Microsoft’s component object model (COM) architecture .The OLE DB specification model provides a standard definition of data access methods for data sources. For each supported data storage platform, there is a specific OLE DB provider that implements the standard data access methods defined in the OLE DB specification. Please see Figure 8-1 for more details about this architecture.

Currently, Microsoft is including three native OLE DB providers with the MDAC 2 package. These components provide native capabilities to Microsoft SQL Server, Microsoft Jet, and Oracle databases. A fourth OLE DB provider is also included in the MDAC 2 package, the OLE DB provider for ODBC (sometimes referred to as Kagera). This provider acts as a translator for ODBC, and it allows OLE DB to access relational databases through their corresponding ODBC drivers.

Due to its open architecture based on COM, ADO is much more extensible than any of the other data access method available today. COM provides several great advantages for ADO. COM interfaces are not restricted to only a baseline set of functions; each interface can provide additional functionality as needed. The use of COM and the distributed component object model (DCOM) allows ADO and OLE DB components to operate across multiple processes and even across multiple machines. This allows you to upsize the various data access components without affecting the actual program code. For example, a program originally configured to access a local Access database using a local ActiveX EXE could easily be upsized to use SQL Server through Microsoft Transaction Server. This location independence gives ADO a great amount of flexibility. It provides the capability to access data from any location, such as the local desktop, across a LAN or WAN, n-tiered client/server, mainframe, and data sources accessed across the Internet.

Another benefit derived from the COM architecture of OLE DB is that you can write you own OLE DB Providers. Microsoft has released the MDAC Software Development Kit (SDK), which includes the OLE DB 2 specification. By using this specification, you can use VB 6 (or other COM-enabled development language) to write your very own OLE DB providers for any data source that you wish.

For the most part, ADO now provides better performance than DAO and RDO. In addition to better performance, ADO provides a very compact object model with only seven objects (DAO has 17 objects and RDO has 10 objects). See Figure 8-2 for a diagram of the ADO object model. The primary objects in the ADO object model are the Connection, Recordset, and Command objects. The Connection object establishes a connection with a data source, the Command object executes commands, and the Recordset object manages result sets.

Both DAO and RDO use a hierarchical object model, which requires you to use the top-level object as the source for the lower-level objects. There is also a structured relationship between all ADO objects; however, you can create most ADO objects independent from other objects. For example, you can independently create a Command object and then execute the same Command object against several different Connection objects. This allows ADO objects to be very flexible and ultimately facilitates object reuse.

The Connection object represents a session between your application and a data source. Using this object, you can configure various attributes before establishing the connection with the data source. You can also choose to use the client-side cursor library, which, depending on the OLE DB provider, may give support for special features such as batch updates.

You can open a connection asynchronously and use the State property to determine when the connection is completed. Once the connection is established, you can directly execute simple queries using the Execute method. The Execute method may also create a Recordset object. Connection objects manage transactions using the familiar BeginTrans, CommitTrans, and RollbackTrans methods, and Connection objects support different transaction isolation levels.

Connection objects contain an Errors collection, which is a collection of Error objects .You can use these objects to determine errors that have occurred at the OLE DB level. Errors that occur at the ADO object level do not produce Error objects; rather, they trigger the standard VB runtime error handler. With properties such as Description, Number, and Source, the Error object is very similar to the standard VB Err object. Two additional properties are available, SQLState and NativeError, which provide specific error details when using SQL data sources.

A Command object represents the definition for an instruction that you wish to run against a certain data source. Typically, this object is a SQL statement, query, or stored procedure name along with any parameters necessary to execute it. In the ADO model, Command objects are optional because some OLE DB providers may not include support for the ICommand COM interface.

If the command requires parameters, the parameter definitions are contained in the Parameters collection, a collection of Parameter objects. You can manually define the parameters for the command or you can use the Refresh method to force the OLE DB provider to create the Parameters collection. Using the OLE DB provider to refresh the Parameters collection may require the provider to execute several additional queries against the system catalog of the data source, so for best performance you should consider manually creating the Parameter objects.

Before you can execute a statement from a Command object, the Command object must be linked to a data source connection by setting the ActiveConnection property. By later changing the ActiveConnection property, you can link a Command object to a different Connection object. You can use the Execute method to execute the statement against the current data source connection. If it is a row-returning command, the Execute method will create a Recordset object.

Parameter objects represent the parameters and return values for stored procedures or parameterized queries. A Parameter object contains all the necessary details about a parameter, including the name, data type, value, and parameter direction (input, output, input/output or return value). You can use the OLE DB provider to automatically refresh the entire Parameters collection or, for increased performance, you can create these objects manually. You must use the AppendChunk method when setting parameter values for long binary or character fields with more than 255 bytes.

The Recordset object is by far the most complex ADO object. This object represents a set of records returned from a data source. At any given time, however, the Recordset object refers to only a single record within the entire set. You can create Recordset objects from the Execute method of Command and Connection objects, or you can use the Open method of the Recordset object itself to create the contents of the object.

One of the most important properties of a record set is the CursorType property, which determines the behavior of the record set. This property must be set prior to opening the record set and the possible values include dynamic, keyset-driven, read-only, and forward-only cursors. The CursorType property determines if the record set is updateable and scrollable, and it determines how the record set handles records that have been concurrently created or modified by other users. You must use static cursor types when using a connection with client-side cursors.

Once you have created a Recordset object, you can navigate through the records using the familiar MoveFirst, MovePrevious, MoveNext, and MoveLast methods, as well as the BOF and EOF properties for location indicators. Using the Resync method, you can dynamically and nondestructively refresh a record set with the current values from the database. With record sets based on static or keyset-driven cursor types, you can use the BatchUpdate method to commit multiple updates to the database in one operation.

If you are using client-side cursors in your Connection object, you may be able to perform find, sort, and filter operations on the record set for additional data manipulation. Client-side cursors give you the capability to manually create record sets without database interaction and add records to it without using a data source, and you can save, or persist, a client-side record set to your hard drive for later use.

Each Recordset object will have a Fields collection, composed of Field objects. Each Field object represents one column, or field, of data in the record set. In addition to the actual field value, you can also retrieve and update additional properties for the field such as Name, Type, Size and Precision.

The Fields collection is the default collection of a Recordset object, so you can reference the individual fields in the following ways:

• Recordset.Fields("Last_Name")
• Recordset.Fields
• Recordset("Last_Name")
• Recordset!Last_Name

In addition to the built-in properties that all ADO objects have, some objects have an additional set of dynamic properties as determined by the OLE DB provider. The Connection, Command, Recordset, and Field objects all contain a Properties collection, which is a collection of dynamic Property objects. A Property object contains Name, Type, Value, and Attributes properties, which are all are read-only except for the Value property, which may be writable. To reference a Property object, you must be use its name or ordinal index (for example, object.Properties("Name") or Object.Properties(0)) .

Currently, there are several limitations to this new technology. You may have noticed that the ADO object model does not have an equivalent object to the dbEngine or the rdoEngine. Without this type of top-level object, there is no way to create or manipulate the ODBC data sources on the local machine .Also, ADO 2 does not support parameterized queries for Microsoft Access databases, nor does it support the Access security model of users and groups. Microsoft has stated that this functionality will be included in future versions of ADO.

There are two different libraries supplied by ADO for use in your projects. To add either of these libraries, you must select Project | References from the VB IDE. In the list of libraries, you will find two libraries relating to ADO, the Microsoft ActiveX Data Objects 2.0 Library (ADODB) and the Microsoft ActiveX Data Object Recordset 2.0 Library (ADOR). The ADODB library contains the complete ADO object model and full ADO functionality; you will use this library for most of your full-scale applications .The ADOR library contains only the ADO Recordset object; this library is most useful for browser applications.

All of the examples in this chapter use the full ADODB library. You will also notice in the examples that the library name is included in the object declarations (for example, Dim rst as New ADODB.Recordset). This is a recommended precaution because many of the ADO objects have the same name as DAO objects.

The ADO library is free-threaded, however, if you examine the Windows Registry you will find the library listed as apartment threaded .Because not all OLE DB Providers or ODBC drivers may be able to run free-threaded, ADO will normally run apartment threaded. You should run ADO as apartment threaded unless all of the OLE DB components that you are using are capable of running free-threaded.

Start Q & A

When using ADO, you must deal with two main types of errors, application-level runtime exception errors and low-level provider errors. Application-level errors occurring within an ADO object initiate the regular VB runtime error trapping routines. These ADO-specific errors trigger the On Error event and the error details appear in the VB Err object. These errors do not populate the Errors collection for a Connection object .For example, you would generate an application-level error if you attempted to execute a Command object without setting its ActiveConnection property to a valid Connection object.

Low-level provider errors are errors that occur below the ADO objects. These errors may occur in OLE DB or within a specific OLE DB provider. In addition to triggering the runtime error trapping routine, these errors generate Error objects to populate the Errors collection for the associated Connection object. Usually when these low-level errors occur, the Errors collection is automatically cleared and repopulated with objects relating to the current error condition only.

In your program code, every time that you encounter a possible ADO error, you should check the Errors collection belonging to the current Connection object. If the Errors collection is not empty, then you should examine each Error object and take appropriate action to deal with the error. You should also note that unlike most other collections, the Errors collection is zero-based (the first item has an index of zero).The following code demonstrates how to loop through the Errors collection:

Dim cnn As New ADODB.Connection
Dim i as long
On Error GoTo Error_Handler

‘some error-causing operation on the cnn object.

Exit Sub

Error_Handler:

‘Print out the VB Err Object
Debug.Print Err.Number
Debug.Print Err.Description
Debug.Print Err.Source

‘Loop through the ADO Errors Collection
For i = 0 To cnn.Errors.Count – 1
Debug.Print cnn.Errors(i).Number
Debug.Print cnn.Errors(i).Description
Debug.Print cnn.Errors(i).Source
Next I

End Sub

For a list of frequent OLE DB error codes, please consult article Q168354 in the Microsoft Knowledge Base (http://support.microsoft.com/support/kb/articles/q168/3/54.asp)

Some ADO operations will generate warning messages and place them in the Errors collection. A Recordset object can create warning messages when you set the Filter property, or when you call the Resync, UpdateBatch, or CancelBatch methods. A Connection object can also create warning messages when you call the Open method. These warning messages do not trigger the runtime error trapping routine and they do not clear out the Errors collection before placing the warning message into the Errors collection. Before executing any of these operations, you should first call the Clear method on the Errors collection to clear out any previous errors. Then after completing the operation, you should check the Count property of the Errors collection to see if the operation returned any warning messages. If the Errors collection is not empty, you should examine each warning message and take appropriate action.

In ADO, the Connection object establishes and maintains connections with data sources. The basic steps necessary to establish a connection are:

1. Select which OLE DB provider you want to use.
2. Modify the characteristics of the Connection object (including the ConnectionString and CursorLocation properties).
3. Call the Open method of the Connection object to establish the connection.

The Provider and ConnectionString properties can be set as properties of the Connection object, or they can be included as parameters of the Open method.

By default, ADO will attempt use the OLE DB Provider for ODBC as the provider for new Connection objects .You can override this property by setting the Provider property of the Connection object to a different provider. For example, the following code demonstrates how to select the Microsoft Jet OLE DB Provider:

Dim cnn as New ADODB.Connection

cnn.Provider = "Microsoft.Jet.OLEDB.3.51"

See Table 8-1 for a list of possible values for the Provider property. You can also specify the provider as part of the ConnectionString property as described in the following section of this chapter. In the above example, the complete ProgID for the provider was specified ("Microsoft.Jet.OLEDB.3.51"). You may use the shorter VersionIndependentProgID to specify the provider; however, for some providers it may be best to supply the complete ProgID to avoid potential version conflicts.

The ConnectionString property is a string value that contains connection-specific information relating to the target data source. There are four ADO-specific arguments, listed in Table 8-2, that the ADO Connection object supports and processes. ADO considers all other arguments to be provider-specific arguments and passes those arguments directly through to the selected OLE DB provider. The syntax for the ConnectionString argument list is based on the ODBC connection string format. For each argument, you must place an equals sign (=) after the keyword and a semicolon (;) must be used to separate each argument.

If you include a Provider argument in the ConnectionString, the value for that argument will override the current Provider property setting of the object. If you use the File Name argument to specify a file containing the connection details, you should not also set the Provider property because the file specification will cause the appropriate provider to be loaded.

This is the default provider for ADO and it allows ADO to connect to any ODBC data source. This provider does not support any ConnectionString arguments; rather, it acts as a wrapper for ODBC and it passes all non-ADO arguments to the ODBC driver manager .The ODBC driver manager is then responsible for establishing the connection with the data source.

Similar to RDO, this provider allows you to connect to an ODBC data source with or without a predefined ODBC Data Source Name (DSN). In your ADO connection string, you can use any argument supported by the ODBC driver. See Table 8-3 for a list of common ODBC ConnectionString arguments. For example, to connect to a predefined DSN your ConnectionString would look similar to this:

"Provider=MSDASQL;DSN=YourDSN;UID=sa;PWD=sa"

Alternatively, to create a DSN-less connection, your ConnectionString would look similar to this:

"Provider=MSDASQL;Driver={SQL Server};" _
& "Server=YourServer;Database=YourDatabase;UID=sa;PWD=sa"

For ODBC data sources, you can specify the user account and password in the ConnectionString property or you can specify this information as parameters for the Open method. If you provide this information in both places, the parameters for the Open method will override the settings in the ConnectionString.

The Connection object also provides a DefaultDatabase property that you can use to specify the default database for a connection. This can be set after you have opened the Connection, and it will override the Database setting in the ConnectionString. For certain databases, this property may need to be established to allow unqualified naming syntax (not having to specify the database or owner name) to access objects from the default database. Not all databases support this property, and setting it may return an error.

This provider gives high-performance access to Microsoft SQL Server databases. It supports several additional ConnectionString arguments, as listed in Table 8-4. These properties can be set as part of the ConnectionString, or you can manipulate them through the Properties collection of the Connection object. In order to access these settings through the Properties collection, you must first set the Provider property of the Connection object to SQLOLEDB.

For SQL Server connections, you can specify the user account and password in three different locations: in the ConnectionString property, in the Properties collection of the Connection object, or as parameters for the Open method. If you provide this information as parameters for the Open method, the parameter values will override any User ID or Password settings in the ConnectionString or Properties collection. If you do not supply any parameters for the Open method, the ConnectionString arguments will override the settings in the Properties collection .

Using this provider, you can access Microsoft Jet desktop database files. This provider supports several additional ConnectionString arguments, as listed in Table 8-5. These properties can be set as part of the ConnectionString, or you can manipulate them through the Properties collection of the Connection object. In order to access these settings through the Properties collection, you must first set the Provider property of the Connection object to the Jet OLE DB Provider.

By default, this provider will open Jet databases in read/write mode .You must change the Mode property of the Connection object to adModeRead if you want to open a database for read-only access.

Before you open a connection, you can choose the location for the cursor engine. By default, ADO will use server-side cursors from the data source–provided cursor library. You can choose to use the local cursor library to create client-side cursors with added functionality. To use client-side cursors, you must set the CursorLocation property of the Connection object to adUseClient .To use server-side cursors, the CursorLocation property must have a value of adUseServer.

Once you have configured the Connection object, all you need to do in order to create the connection is to call the Open method. The syntax for the Open method is

Connection.Open ConnectionString, UserID, Password, OpenOptions

All of the parameters for the Open method are optional. If you supply the ConnectionString parameter, it will overwrite the ConnectionString property of the Connection object. The UserID and Password parameters will override any values specified in the ConnectionString property. If you set the OpenOptions parameter to adConnectAsync, the connection will be opened asynchronously. The following example illustrates three different techniques to open a connection to the same database:

‘Example 1 uses the Provider property.
cnn1.Provider = "Microsoft.Jet.OLEDB.3.51"
cnn1.CursorLocation = adUseClient
cnn1.Open "C:\test.mdb", "admin", ""

‘Example 2 specifies everything in the Open method.
cnn2.CursorLocation = adUseClient
cnn2.Open "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=c:\test.mdb"

‘Example 3 uses the default provider for ODBC (MSDASQL).
‘This example also sets the ConnectionString before the Open Method.
cnn3.CursorLocation = adUseClient
cnn3.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};" _
& "DBQ=c:\test.mdb;UID=admin;PWD=;"
cnn3.Open

There are many situations where the database connection may take a considerable amount of time to establish. For these situations, ADO provides the option of performing an asynchronous connection. This feature allows your program to continue executing while the connection is being established. This functionality is contained entirely within ADO and it is not dependent on the OLE DB provider, so this feature is available for all ADO data sources. Although you can establish data source connections using the ADO Recordset and Command objects, you can only establish data source connections asynchronously using the Connection object.

To execute an asynchronous connection, you must set the OpenOptions parameter of the Open method to adConnectAsync. Following the Open method, you can check the State property of the Connection object to find out the status of the asynchronous operation .Depending on the status of the operation, the value of the State property is a combination of the constants defined in Table 8-6. If an error has occurred during the asynchronous connection, subsequent checks of the State property may generate a trappable runtime error. The following code illustrates the use of the State property:

cnn.Open , , ,adConnectAsync

‘Do a bit-wise comparison of the values
Do While (cnn.State AND adStateConnecting)
Debug.Print "Connection is still in progress."
Loop

The Connection object also supports event notification for asynchronous operations. Therefore, if you are working inside a class module, you may be able to use the WithEvents option to establish programmatic event handlers that correspond to the events of the Connection object. You can establish two different event handlers for the asynchronous connection operation, obj_WillConnect and obj_ConnectComplete (where obj is the name of the Connection object). The obj_WillConnect event is triggered immediately before the object begins to connect and the obj_ConnectComplete event occurs after the operation has completed. You can examine the adStatus parameter of the obj_ConnectComplete event to find out the outcome of the operation. For more information on these events, please consult the VB online documentation.

If you wish to cancel an asynchronous connection operation before it has completed, all that you need to do is call the Cancel method on the Connection object:

cnn.Open , , ,adConnectAsync

‘to cancel the asynchronous Open command
cnn.Cancel

When you are ready to close a connection with a data source, you need to call the Close method of the Connection object. This will terminate the connection, but it does not remove the Connection object from memory. After closing a Connection object, you can alter its properties and open it again. If you wish to completely remove the object from memory, you must set it equal to Nothing. For example:

‘Close the connection
cnn.Close

‘Remove the object from memory
Set cnn = Nothing

If you close a Connection object while there are open record sets associated with the connection, all of the open record sets will be closed and any pending changes will be canceled. If there is a transaction in progress when the connection is closed, a runtime error will occur .If a transaction is in progress and the Connection object falls out of scope, the transaction will be rolled back without an error. Any Command object that was linked to the closed Connection object will persist in memory; however, its ActiveConnection property will be set to Nothing. In addition, all provider-specific properties will be removed from the Properties collection of the newly orphaned Command object.

Once you have established a connection with a data source, you can use three different methods to create a record set containing data from that source. For simple commands, you can use the Execute method of the Connection object to create a record set. For more complex commands and commands that you want to use repeatedly, you should define the command using a Command object and then use the Execute method of the Command object to create a record set. You can also use the Open method of a Recordset object to retrieve data into the record set.

You can use the Execute method of a Connection object to execute commands directly against the connection. This method is best suited for simple and infrequent commands that do not use any parameters. The syntax for this command is:

Set Recordset = Connection.Execute (CommandText, RecordsAffected, Options)

The CommandText parameter is a string value that contains the statement that you wish to execute. This string may be an SQL statement, a stored procedure, a table name, or any other provider-supported command. The optional RecordsAffected parameter is a long value returned from the provider to indicate the number of rows affected by the execution of this command. The optional Options parameter is a long value that describes the command type; this allows the OLE DB provider to optimize the execution of this command. See Table 8-7 for a listing of the possible values for the Options parameter.

All Recordset objects created by this operation use a read-only, forward-scrolling cursor .If you wish to create a record set with a different cursor type, you must use the Open method of the Recordset object as detailed later in this section. If the command does not return any rows, you may omit the "Set Recordset =" portion of the statement.

The following listing show several examples of the Execute method:

Dim cnn as New ADODB.Connection
Dim rst as New ADODB.Recordset
Dim lngDelRowCount as long

‘Open a connection to the Pubs database
cnn.Open "Provider=sqloledb;Data Source=your_server;" _
& "Initial Catalog=pubs;User ID=SA;Password=;"

‘Execute a row-returning SQL statement
Set rst = cnn.Execute("SELECT * FROM Titles " & _
"WHERE Type = ‘psychology’", , adCmdText)

‘Execute a non-row-returning SQL Statement
cnn.Execute "DELETE FROM Titles " & _
"WHERE Type = ‘psychology’", lngDelRowCount, adCmdText

MsgBox lngDelRowCount & " record were deleted."

You can also use the Command object to query a data source and retrieve a record set. In order to use the Command object, you must first define the object by setting several properties for it, including the CommandText and ActiveConnection properties. Once you have defined the object, you may call the Execute method to issue the command against a data source. Because this is a persistent object, once you have defined the command, you can execute it repeatedly without having to redefine the basic properties of it.

The CommandText property is a string value that indicates the main executable text for the command. In most situations, the CommandText property will be an SQL statement or stored procedure name. The ActiveConnection property links the command to a specific data source connection .You can set the ActiveConnection property to any valid Connection object or you can create an independent connection for this Command by setting the ActiveConnection property to a valid connection string.

Additional properties that you can set on a Command object include the CommandType property, the CommandTimeout property, and the Prepared property. The CommandType property is equivalent to the Options argument of the Connection Execute method and this allows the OLE DB provider to optimize the execution of this command. Refer back to Table 8-7 for a listing of the possible values for the CommandType parameter. The CommandTimeout property can be set to indicate the number of seconds to wait before a timeout occurs. You can use the Prepared property to cause the provider to save a compiled, or prepared, version of the command, which can create faster execution of the command in the future. Refer to Table 8-8 for a summary of the Command object Properties.

Command objects also have a Parameters collection, which is a collection of Parameter objects. For commands that use or require parameters, you can use this collection to create a detailed definition of the parameters .

Once you have set the properties for the Command object, you can call the Execute method to run the command against the current connection. The syntax for the Execute method is:

Set Recordset = Command.Execute (RecordsAffected, Parameters, Options)

The optional RecordsAffected parameter is a long value returned from the provider to indicate the number of rows affected by the execution of this command. The Parameters argument is an optional variant array of parameter values for the command. You can use the Parameters argument if the command requires parameters and you have not defined the Parameters collection or if you want to override the values for some or all of the Parameter objects. The optional Options parameter is a long value that describes the command type and this allows the OLE DB provider to optimize the execution of this command. This parameter will override the value of the CommandType property. Refer back to Table 8-7 for a listing of the possible values for the Options parameter. If the command does not return any rows you can omit the "Set Recordset =" portion of the function call.

The following code demonstrates how to use Command object to create a record set:

Dim cnn as New ADODB.Connection
Dim cmd as New ADODB.Command
Dim rst as New ADODB.Recordset

‘Open a connection to the Pubs database
cnn.Open "Provider=sqloledb;Data Source=your_server;" _
& "Initial Catalog=pubs;User ID=SA;Password=;"

‘Execute a row-returning SQL statement
cmd.CommandText = "SELECT * FROM Titles WHERE Type = ‘psychology’"
set cmd.ActiveConnection = cnn
Set rst = cmd.Execute

The ADO Command object provides support for parameters in two different ways. All Command objects have a Parameters collection, which you can use to define the parameters for the command. Alternately, you can include a variant array of parameters as an argument for the Command Execute method.

The Parameters collection is a collection of Parameter objects. You can use ADO to populate this collection by calling the Refresh method of the Parameters collection .The Parameters Refresh method will require the ActiveConnection property to be set and it may provide poor system performance because it may need to query the database several times to gather the complete parameter specifications. For better performance, use the CreateParameter method to manually create the Parameter objects to fill this collection. You can use all types of parameters with Command object (input, output, input/output, and return values). For input parameters, you simply need to set the Value property for the parameter before opening or executing the command. For output parameters and return values, you can check the Value property after completing the command to find out what values the command returned.

As part of the command Execute method, you can also supply a variant array as the parameters for the command execution. If you have previously assigned values to the objects in the Parameters collection, any parameter values that are included in the Parameters argument will override the corresponding values in the Parameters collection. Also, you cannot use the Parameters argument to gain access to output parameters or return values.

The following code demonstrates several ways to use parameters with commands. This code uses a fictitious stored procedure in the pubs database called sp_AuthorSearchByName, which uses two input parameters, FirstName and LastName, and returns a record set containing the names of authors whose names match the parameters.

Dim cnn as New ADODB.Connection
Dim cmd1 as New ADODB.Command
Dim cmd2 as New ADODB.Command
Dim prm as New ADODB.Parameter
Dim rst1 as New ADODB.Recordset
Dim rst2 as New ADODB.Recordset

cnn.Open "DSN=pubs;UID=sa;PWD="

cmd1.ActiveConnection = cnn
cmd1.CommandText = "sp_AuthorSearchByName"
cmd1.CommandType = adCmdStoredProc
‘Use ADO to refresh the Parameters collection
cmd1.Parameters.Refresh

cmd2.CommandText = "sp_AuthorSearchByName"
cmd2.CommandType = adCmdStoredProc

‘Manually create a parameter object and append it to the collection
Set prm = cmd2.CreateParameter("FirstName",adVarChar, adParamInput, 25)
cmd2.Parameters.Append prm

‘Or, create and append the parameter in one command
cmd2.Parameters.Append cmd2.CreateParameter("LastName", _
adVarChar, adParamInput, 25)

‘Use a variant array for the parameters
Set rst1 = cmd1.Execute (,Array("Clark","Smith"))

‘Define the parameter object values
cmd2.Parameters("FirstName").Value = "Doris"
cmd2.Parameters("LastName").Value = "Jones"
set rst2 = cmd2.Execute

Once you have associated a Command object with a Connection object, you can execute the Command object as if it were a built-in method of the Connection object. In order for this functionality to be available, you must first set the Name property of the Command object. This property then becomes the method name for the Connection object. The syntax for this operation is:

Connection.CommandName Parameters, Recordset

The Parameters argument is an optional variant array of parameter values. The Recordset argument is an optional Recordset object that is only required for row-returning commands. You can also use this technique to execute stored procedures that are available to the Connection object, and, if necessary, ADO will attempt to determine the parameter types. The following code demonstrates calling a Command object as a method of a Connection object:

Dim cnn as New ADODB.Connection
Dim cmd as New ADODB.Command
Dim rst as New ADODB.Recordset

‘Open a connection to the Pubs database
cnn.Open "Provider=sqloledb;Data Source=your_server;" _
& "Initial Catalog=pubs;User ID=SA;Password=;"

cmd.CommandText = "SELECT * FROM Titles WHERE Type = ‘psychology’"
cmd.Name = "GetPsychologyTitles"
set cmd.ActiveConnection = cnn

‘now call the command as a method
cnn.GetPsychologyTitles ,rst

Of all the methods that ADO provides for opening a record set, the Recordset object itself provides the most flexible method for creating record sets. By using the Recordset object, you have full control over the cursor type and record locking technique used by the record set, and the Recordset object provides the capability to independently create data source connections.

To use a Recordset object to open a record set you must use the Open method. The syntax for the Open method is:

Recordset.Open Source, ActiveConnection, CursorType, LockType, Options

The Source argument defines the text of the command. For this argument, you may provide a string variable containing the command text, or you can supply a Command object to use as the source. The ActiveConnection argument links the record set to a specific data source connection. You can set the ActiveConnection argument to any valid Connection object, or you can create an independent connection for this record set by setting the ActiveConnection parameter equal to a valid connection string.

The LockType argument is an optional parameter that indicates the record locking, or concurrency, technique used by the record set. See Table 8-10 for a listing of the possible values for the LockType argument. When using client-side cursors, you cannot use the adLockPessimistic setting.

The final argument for the Open method is the Options parameter. This is an optional parameter that specifies the type of command being executed. Refer back to Table 8-7 for a list of possible values for this argument.

Several of the arguments supplied to the Open method can also be set as properties of the Recordset object (Source, CursorType, and LockType). For these settings, the values supplied in the Open method will override any previous property settings. In addition, if the source of the record set is a Command object and you set the ActiveConnection property in the Open method to any Connection object, you will generate a runtime error. In this scenario, the Command object must have its ActiveConnection property set to a valid connection before using the Recordset Open method.

The following code demonstrates the use of the Recordset Open method:

Dim cnn as New ADODB.Connection
Dim cmd as New ADODB.Command
Dim rst1 as New ADODB.Recordset
Dim rst2 as New ADODB.Recordset

‘Open a connection to the Pubs database
cnn.Open "Provider=sqloledb;Data Source=your_server;" _
& "Initial Catalog=pubs;User ID=SA;Password=;"

‘Define a command object
cmd.CommandText = "SELECT * FROM Titles WHERE Type = ‘psychology’"
cmd.CommandTimeout = 30
set cmd.ActiveConnection = cnn

‘Open rst1 based on the Command object
rst1.Open cmd, , adOpenDynamic, adLockOptimistic, adCmdText

‘open rst2 based on a table
rst2.Open "Titles", cnn, adOpenKeyset, adLockOptimistic, adCmdTable

When you have finished using the record set, you can use the Close method to close it and free the system resources that were in use by the object. To completely remove the object from memory, you must set the Recordset object equal to Nothing.

Start Q&A

Typically, when dealing with record sets from a data source in the past, the only method available to sort and search through the data was to use the ORDER BY and WHERE clauses of the SQL SELECT statement. ADO now provides several other methods that you can use to manipulate record sets.

The ADO client-side cursor library provides the capability to dynamically create indexes on fields in local record sets. An index can dramatically improve performance when finding or sorting data in a local record set. Client-side Field objects include a dynamic property called Optimize, and if you set the Optimize property to True an index will be created on the field. Once you have created an index, you can delete the index by setting the Optimize property to False. Because it is a dynamic property, you can only access this property through the Properties collection of the Field object .

You can use the Find method to locate a particular row within a record set. Using this method, you can quickly search through a record set without having to requery the data source. The syntax for the Find method is:

Recordset.Find (Criteria, SkipRecords, SearchDirection, Start)

The Criteria argument is a string value that contains the search statement. The syntax for the Criteria argument is "field operator value" where field is one of the record set field names, operator is the comparison operator (=, <, >, or "like"), and value is the actual value being sought. When searching for dates, the target value must be enclosed by the "#" character (for example, "date_of_birth > #12/31/1997#"). When searching for string values the target value must be enclosed by single quotes (for example, "Name=‘Brooke’"). The "like" operator is used for string pattern matching and you may use "*" or "_" wildcard characters in the string expression being sought where "*" will match any string of characters and "_" will match any single character. Also, the Criteria argument can only be used to search for a single value (or string pattern) within a single field; it does not support multicolumn or multivalue searches .

You can optionally include the SkipRecords argument to specify an offset number of rows to use as the starting point for the search. The SkipRecords argument is a long value and it can be positive or negative. The offset direction is dependent on the direction of the search. For example, if the SkipRecords value is 1 and the search direction is forward, the Find method will start at the record immediately following the current record, and it will search through the end of the record set. If the SkipRecords value is 1 and the search direction is backward, the Find method will start at the record immediately preceding the current record and it will search back to the start of the record set. If you specify a bookmark location to use as the starting point for the search, the offset is calculated from the supplied bookmark location.

By default the Find method will search forward through the record set; however, you can use the SearchDirection argument to specify the direction of the search. The values for the SearchDirection argument can be adSearchForward or adSearchBackward.

You can also use the Start argument to specify a bookmark location to use as the starting point for the Find method. Possible values for the Start argument include adBookmarkFirst (start at the first record) and adBookmarkLast (start at the last record).

If the Find operation is successful, it will set the record set position to the first record that satisfied the search criteria. If the operation did not find a record that met the search criteria, it will set the record set position to the end (EOF) or beginning (BOF) of the record set depending on the direction of the search. The following code illustrates the use of the Find method:

Dim cnn as New ADODB.Connection
Dim rst as New ADODB.Recordset

‘Open a connection to the Pubs database using client-side cursors
cnn.CursorLocation = adUseClient
cnn.Open "Provider=sqloledb;Data Source=your_server;" _
& "Initial Catalog=pubs;User ID=SA;Password=;"

‘Open a recordset based on the Authors table
rst.Open "Authors", cnn, adOpenStatic, adLockOptimistic, adCmdTable

‘To speed up the search, create an index on the last name field
rst.Fields("au_lname").Properties("Optimize") = true

‘Find the first record with a last name that begins with S
rst.Find "au_lname like ‘S*’"

if rst.EOF then
msgbox "No authors were found with a last name starting with S."
end if

After opening or creating a record set, you can dynamically sort the records by setting the Sort property of the record set. You can use this property to temporarily rearrange the records based on the values contained in one or more fields. The Sort property is a string value containing a comma-separated list of field names .The default sort order for each field is ascending; however, you can optionally include the ASCENDING or DESCENDING keywords after each field name to specify the sort order for the field.

After setting the Sort property, the record set will automatically reposition itself to the start of the record set. When using client-side record sets, ADO will automatically create a temporary index on the sorted fields. If you set the Sort property to an empty string, the records will be returned to their original sort order and any temporary indexes created will be deleted. The following code illustrates how to use the Sort property to sort a record set:

Dim cnn as New ADODB.Connection
Dim rst as New ADODB.Recordset

‘Open a connection to the Pubs database using client-side cursors
cnn.CursorLocation = adUseClient
cnn.Open "Provider=sqloledb;Data Source=your_server;" _
& "Initial Catalog=pubs;User ID=SA;Password=;"

‘Open a recordset based on the Authors table
rst.Open "Authors", cnn, adOpenStatic, adLockOptimistic, adCmdTable

‘sort last name and first name in ascending order
rst.Sort = "au_lname, au_fname"

‘sort last name in descending order and first name in ascending order
rst.Sort = "au_lname DESCENDING, au_fname"

‘cancel the sorting to return the records to their original order
rst.Sort = ""

ADO record sets also contain a Filter property that you can use to temporarily hide certain records from a record set. After setting the Filter property, you can later clear it and all of the original records will again be visible in the record set. The Filter property is a variant value that can contain several different types of values.

You can set the Filter property to a string value containing multiple search clauses. These clauses are similar to criteria for the Find method where each clause must have the syntax of "field operator value". For the Filter property, the operator can include: =, <, >, <=, >=, or "like" for string pattern matching. You must use single quotes to delimit string values and use "#" characters to delimit date values. The "like" operator supports the "*" and "_" wildcards also. The Filter property can contain multiple clauses joined together using the AND or OR operators. For example:

‘Find all authors where first name is Summer or Heather
rst.Filter = "au_fname = ‘Summer’ OR au_fname = ‘Heather’"

You can also group clauses together using parentheses. However, there are some restrictions on the use of parentheses to group expressions together. Groups of clauses that are contained within parentheses can only be joined to other groups by the OR operator.

You can also set the Filter property to a variant array of bookmark values. You can use this type of filter when you have a set of specific records that you want to be included in the filtered record set. Only the records referenced by the bookmarks in the array will be included in the filtered record set. For example,

Dim cnn as New ADODB.Connection
Dim rst as New ADODB.Recordset
Dim vaBookmark as Variant

‘Open a connection to the Pubs database using client-side cursors
cnn.CursorLocation = adUseClient
cnn.Open "Provider=sqloledb;Data Source=your_server;" _
& "Initial Catalog=pubs;User ID=SA;Password=;"

‘Open a recordset based on the Authors table
rst.Open "Authors", cnn, adOpenStatic, adLockOptimistic, adCmdTable

‘Move to the second record
rst.MoveNext

‘Add a bookmark for the second record
vaBookmark = rst.Bookmark

‘Add a bookmark for the last record using a bookmark enum value
vaBookmark = adBookmarkLast

‘Apply the bookmark array as the filter
rst.Filter = vaBookmark

‘Only the second and last records remain from the original recordset.

You can use several other special values for the Filter property. These filter types can help you when attempting to resolve batch update conflicts. See Table 8-11 for a list of these special values .

You can clear the filter by setting the Filter property to adFilterNone or to an empty string. After removing the filter, all of the original records will be visible in the record set.

There are several methods that you can use to update records with ADO. From an ADO object perspective, the easiest way is to execute an action query (INSERT, UPDATE, or DELETE) to modify the records. Alternately, using an ADO Recordset object you can change field values and then update the server with the new values. You can update individual records on the server using the Update method, or you can use the UpdateBatch method to commit multiple record updates at once. If your application requires transactional support, you can use the transaction methods provided by the ADO Connection object.

If you are using an updateable record set, all that you need to do to modify the value of a field is to set the value of the field to a new value. Unlike previous data access methods, you do not need to issue an Edit command to place the record into Edit mode .In ADO, a record automatically enters Edit mode once you make changes to its value.

The Update method allows you to update the server with changes made to the current record of record set. After modifying a record or adding a new record, all that you need to do is call the Update method to save the changes to the server. If you reposition the record set to a different record, ADO will automatically call the Update method to save the changes .If you want to discard your changes, you must call the CancelUpdate method.

As part of the Update method, you have the option of including a variant array of field names and a variant array of new values. This provides an additional way to update specific field values. If you include these arguments, both arrays must contain the same number of elements.

The following code demonstrates the different ways to use the Update method:

Dim rst as New ADODB.Recordset
Dim vaField as Variant
Dim vaValue as Variant

‘Open a recordset based on the Authors table
rst.Open "Authors", "DSN=pubs;UID=sa", adOpenKeyset, _
adLockOptimistic, adCmdTableDirect

‘update several fields of the first record
rst.Fields("au_lname") = "Smith" ‘basic syntax
rst("au_fname") = "Tammy" ‘alternative syntax

if msgbox ("Do you wish to update the name?", vbYesNo) = vbYes then
rst.Update
else
rst.CancelUpdate
end if

‘move to a new record
rst.MoveNext

‘Alternative syntax for the Update Method
vaField = "au_lname"
vaField = "au_fname"
vaValue = "Westberg"
vaValue = "Eric"
rst.Update vaField, vaValue

ADO can also operate in a batch mode, in which multiple record changes are cached locally and then transmitted to the server as one combined batch. This can provide improved network performance by limiting the number of updates sent across the network. For this operation to be available, you must set the record set locking type to adLockBatchOptimistic. Many providers will also require that the cursor type be either keyset-driven or static for batch updates to work property.

You do not need to call the Update method after modifying records in batch mode. If you omit the Update method, ADO will automatically call it when the record set is repositioned. The Update method will place the record in the list of records that have pending changes. You must call the UpdateBatch method later to send a single update request to the server for the pending records. You can also call the CancelBatch method if you wish to cancel the changes for all the pending records .The following code demonstrates the UpdateBatch method:

Dim rst as New ADODB.Recordset

‘Open a recordset based on the Authors table
rst.Open "Authors", "DSN=pubs;UID=sa", adOpenKeyset, _
adLockBatchOptimistic, adCmdTableDirect

‘Change all the Authors’ last names to Frost
Do While (Not rst.EOF)
rst!au_lname = "Frost"
‘rst.Update is optional here
rst.MoveNext
Loop

if MsgBox ("Click Yes to save the batch.", vbYesNo) = vbYes then
rst.UpdateBatch
else
rst.CancelBatch
end if

To add a new record to a record set, you must use the AddNew method. If you call the AddNew method with no arguments, ADO will add a new blank record to the end of the record set. You can optionally supply a variant array of field names and a variant array of initial values .This provides a way to initialize specific field values; if you include these arguments, they must both contain the same number of elements.

To save the new record into the record set, you can call the Update method or you can reposition the record set to automatically trigger the Update method. To cancel the addition of a new record, you must call the CancelUpdate method before repositioning the record set. Depending on the cursor type of the record set, you may not always be able to see newly added records. For these situations, you may need to call the Requery method of the record set to refresh the record set.

You can use the Delete method to remove records from a record set. This method can be used to delete either the current record, or all the records that satisfy the current Filter property of the record set. To delete the current record from a record set, all you need to do is call the Delete method with no parameters. If you wish to delete all the records that satisfy the Filter property, you must include adAffectGroup as the only parameter for the Delete method. When using the adAffectGroup parameter, the Filter property must be set to one of the special filter values defined in Table 8-11.

In batch mode, ADO will cache record additions and deletions until you call the UpdateBatch or CancelBatch method. The following code demonstrates how to add and delete records using the Recordset object:

Dim rst as New ADODB.Recordset
Dim vaField as Variant
Dim vaValue as Variant

‘Open a recordset based on the Authors table
rst.Open "Authors", "DSN=pubs;UID=sa", adOpenKeyset, _
adLockOptimistic, adCmdTableDirect

rst.AddNew
rst!au_lname = "MacDonald"
rst!au_fname = "Jason"
rst.Update ‘Save the new record

‘Add a record using variant arrays for initial values
vaField = "au_lname"
vaField = "au_fname"
vaValue = "Sorenson"
vaValue = "Darryl"
rst.AddNew vaField, vaValue

rst.MoveFirst ‘Reposition triggers Update method

‘Remove the first record from the table
rst.Delete

You should not use any data-bound controls for the following exercise.

1. Create a new VB project and add the Microsoft ActiveX Data Objects 2.0 Library to the new project.
2. On the Load event for the Startup form, use a Connection object to establish a connection to the Northwinds Trader database (NWIND.MDB) using the OLE DB provider for Microsoft Access.
3. Also on the Load event, open an updateable record set based on the Categories table in the Northwinds Trader database.
4. Display the contents of the Categories table (Category Name and Description fields) in a listview control.
5. Create a button labeled "Delete" that gives the user the ability to delete from the database the category type that is currently selected in the listview.
6. Create a button labeled "Edit" that gives the user the ability to modify the values for the category type currently selected in the listview.
7. Create a button labeled "Add" that gives the user the ability to create a new category type.
8. On the form’s Unload event, close the Recordset and Connection objects.

You can use the ADO Connection object to manage transactions if the OLE DB provider supports transactions. To verify if a provider supports transactions, you can check for a provider-defined property called Transaction DDL in the Properties collection of the Connection object.

There are three methods that you can use to manage transactions: BeginTrans, CommitTrans, and RollbackTrans. The BeginTrans method will return a long value indicating the nesting level of the new transaction, with one being the top-most transaction level. The other two methods have no arguments and do not return any values.

To begin a new transaction, all you need to do is call the BeginTrans method on a Connection object. From that point on, all changes made to data from that Connection will be contained within a single transaction and those changes will not be written to the database until the CommitTrans method is called. All of the changes can be undone simultaneously by calling the RollbackTrans method. If the provider supports nested transactions, you can begin a transaction within another transaction. Each of these nested transactions can be committed or rolled back independently of the outer transactions. The following code demonstrates how to use transactions with ADO:

Dim cnn as New ADODB.Connection
Dim lngCount as Long

‘Open a connection to the Pubs database
cnn.Open "Provider=sqloledb;Data Source=your_server;" _
& "Initial Catalog=pubs;User ID=SA;Password=;"

‘Begin the transaction
cnn.BeginTrans

cnn.Execute "DELETE FROM Authors WHERE au_lname = ‘Smith’", _
lngCount, adCmdText

if msgbox (lngCount & " records will be deleted. " _
& "Are you sure you wish to continue?", vbYesNo) = vbYes Then
cnn.CommitTrans
else
cnn.RollbackTrans
end if

One of the key features of ADO and OLE DB is the support for remote data access and disconnected data manipulation. There are many situations in which it may not always be possible to have a consistent open connection with a data source. For example, consider a Web-based browser application that may have only an intermittent connection with the data source. Or, consider an application for a traveling salesperson who needs to use their laptop continually during the day but can only connect up to the network once per day. These are situations where methods must be available to retrieve data to the local client, manipulate the data while offline, and later update the data source with the modified records. These situations also require the capability to determine if the records have been changed by other users in the meantime, as well as providing the ability to resolve such conflicts. ADO provides methods for all of these operations.

ADO Recordset objects expose a Save method that you can call to save the record set, along with any pending changes, to a file. The syntax for this command is:

Recordset.Save FileName, PersistFormat

The FileName argument is the complete path and filename indicating where to save the record set. You should only specify FileName on the first call to the Save method, and if you include FileName on a subsequent call to the Save method you will cause a runtime error .The only time that you should include FileName on a subsequent Save is if you need to change the filename. For any call to the Save method that you include the FileName argument, if the file already exists, a runtime error will be generated.

You can use the optional PersistFormat argument to specify the format to use for the persistent file. Currently, ADO only supports one value for the PersistFormat argument, adPersistADTG. This value specifies the Advanced Data TableGram format, a proprietary data storage format from Microsoft.

Calling the Save method does not close the record set; both the record set and the persistent file will remain open until you call the Recordset Close method. Other applications will have read-only access to the persistent file while the record set is open in your application. If you call the Save method while the record set is in the process of completing an asynchronous operation, the Save function will execute after the asynchronous operation finishes.

To open a record set based on a persistent file later, you need to supply the filename as the source for the Recordset Open method. If the record set already has its ActiveConnection property assigned, you may also need to set the provider to MSPersist. The following code demonstrates how to save and reopen a record set using a persistent file resource:

Dim rst as New ADODB.Recordset

‘Open a recordset based on the Authors table
rst.Open "Authors", "DSN=pubs;UID=sa", adOpenKeyset, _
adLockOptimistic, adCmdTableDirect

‘Save the recordset to a file
rst.Save "c:\SQLAuthorsTable", adPersistADTG

‘Close the recordset and close the persistent file
rst.Close

‘To re-open the recordset from the file
rst.Open "c:\SQLAuthorsTable", "Provider=MSPersist",,,adCommandFile

One of the most interesting features of ADO is the capability to create a record set without a data source. You can manually create a client-side record set, append fields to it, open it, and create and modify records within the record set without any database interaction whatsoever .This newly fabricated record set has all the functionality of a regular record set and it provides a powerful new tool for data storage and manipulation within your applications. The following code demonstrates how to create a record set without any database interaction.

Dim rst as new ADODB.Recordset

rst.CursorLocation = adUseClient

‘Create the fields for the new recordset
rst.Fields.Append "ID", adInteger
rst.Fields.Append "Description", adVarChar, 50, adFldIsNullable
rst.Fields.Append "Effective_Date", adDate

‘Open the recordset without any source
rst.Open , , adOpenStatic, adLockBatchOptimistic

‘Now you have a fully functional recordset
rst.AddNew
rst!ID = 1
rst!Description = "Initial Status"
rst!Effective_Date = #1/1/1998#

rst.AddNew
rst!ID = 2
rst!Description = "Second Status"
rst!Effective_Date = #1/31/1998#

rst.UpdateBatch

rst.MoveLast
MsgBox rst.RecordCount

rst.Close

ADO provides the capability of determining if other users have modified records on the server since the current record set was retrieved and updated. ADO provides this ability through a combination of the OriginalValue property, the UnderlyingValue property, and the Resync method.

The OriginalValue property is a property belonging to Field objects. This property contains the original value of the field prior to any user modification .This value may not be consistent with the current value in the database if another user has made changes to the record.

The UnderlyingValue property is also a property of Field objects. This property contains the current database value for the field .If other users have made changes to the record since the current record set was retrieved, the UnderlyingValue property may be different from the OriginalValue property.

The Resync method is a Recordset method that you can use to refresh the fields in the current record set with the latest values from the database .This method is not the same as the Requery method, which refreshes the record set by re-executing the underlying command for the record set. The record set created by the Requery method will include new records, and it will not include deleted records or other records that no longer fit the selection criteria.

In contrast to the Requery method, the Resync method will only attempt to update the values for the records contained in the current record set. The syntax for the Resync method is

Recordset.Resync AffectedRecords, ResyncValues

You can limit the records that the Resync method will affect by setting the AffectedRecords argument to one of the values defined in Table 8-12.

The Resync method also give you the option to completely refresh the field values (thus canceling any pending changes) or to only update the UnderlyingValue properties. To select the field update method, set the ResyncValues argument to one of the values defined in Table 8-13.

There are two basic ways that you can use these techniques in your program to determine conflicting records. Before issuing a record Update command, you can compare the OriginalValue property to the UnderlyingValue property for each field to quickly determine if another user has changed the field since the record set was initially retrieved from the data source. Alternately, you can execute the UpdateBatch method and attempt to update all the pending records and then apply the adFilterConflictingRecords filter to the record set and you can find the records that failed in the UpdateBatch. Once you have identified conflicting records, you need to take appropriate action to deal with the conflicts. Depending on your program, you may choose to inform the user of the conflicts, perform internal processing to resolve the conflicts, or simply overwrite the database values with values from the current record set.

ADO record sets are not limited to containing only a single set of records. An ADO record set can contain multiple independent record sets, or it can contain a hierarchical structure in which record sets are embedded as fields within other record sets.

You can create ADO Recordset objects that contain several independent record sets. You can create these dynamic Recordset objects by executing stored procedures that return multiple result sets or by issuing a compound statement that includes several selection statements.

Newly created Recordset objects containing multiple record sets will initially reference only the first record set. To gain access to the subsequent record sets, you must use the NextRecordset method to return a new Recordset object .The syntax for this method is:

Set NewRecordset = Recordset.NextRecordset (RecordsAffected)

This method will set the NewRecordset object to the next record set contained in the original Recordset object. The NewRecordset object can be the same object as the original record set. RecordsAffected is an optional long value that returns the number of records affected by the current operation. If there are no more Recordset objects to be returned, the NewRecordset object will be set to Nothing.

The following code demonstrates how to create and work with Recordset objects containing multiple record sets:

Dim rst as New ADODB.Recordset

‘Open a recordset based on the Authors table
rst.Open "Select * FROM Authors; Select * from Titles", _
"DSN=pubs;UID=sa", , , adCmdText

Do Until rst is Nothing
Do While Not rst.EOF
Debug.Print rst.Fields(0)
rst.MoveNext
Loop
Set rst = rst.NextRecordset
Loop

One of the most interesting new features of ADO 2 is the Hierarchical Cursor feature, which allows you to create record sets that contain child record sets embedded as fields within the parent records. You can also create multilevel record set hierarchies by nesting record sets within other child record sets. There is no specified maximum depth of record set nesting; however, if you have large parent record sets you probably will notice severe performance degradation as you increase the number of embedded nesting levels.

To provide this "data shaping" functionality, ADO 2 includes a new Shape command language. These commands are a part of the MSDataShape provider, which is only available when using the client-side cursor engine .These commands allow you to define a hierarchy based on record relations, parameters, or aggregate function groupings. The syntax used by the Shape language is relatively complicated and you should refer to the online help for more information about the Shape syntax. You can use the Data Environment Designer to assist you in creating Shape commands, and you can use the Microsoft Hierarchical Flexgrid Control to display the hierarchical record sets created by the Shape command.

By default, fields that contain child record sets will have a field name of "chapter" and the data type for the field will be adChapter .To work with the record set contained in a chapter field, you must set a Recordset object to the Value property of the chapter field. You can also set hierarchical record sets to always synchronize with changes made the underlying child records on the data source. By setting the StayInSync property of the record set to True, the parent record set will always be updated when the underlying child record set, or chapter, is changed. If this property is False, the parent record set will never be updated to reflect changes to the child record sets.

The following code demonstrates how to use data shaping:

Dim cnn as New ADODB.Connection
Dim rstAuthors as New ADODB.Recordset
Dim rstTitles as New ADODB.Recordset

‘Open a connection using the Shape Provider
cnn.Provider = "MSDataShape"
cnn.Open "Shape Provider=MSDASQL;DSN=pubs;UID=sa;PWD="

‘Open a hierarchical recordset
rstAuthors.StayInSync = true
rstAuthors.Open "SHAPE {SELECT * FROM authors} " _
& "APPEND ({SELECT * FROM titleauthor} AS chapter" _
& "RELATE au_id TO au_id)", cnn

do while not rstAuthors.EOF
Debug.Print rstAuthors!au_lname & ", " & rstAuthors!au_fname

‘Assign a recordset to the child recordset
Set rstTitles = rstAuthors!chapter

‘Loop through all the child records
do while not rstTitles.EOF
Debug.Print rstTitles!title_id
rstTitles.MoveNext
loop
set rstTitles = Nothing

‘Move to the next parent record
rstAuthors.MoveNext
loop

You can also create multilevel record set hierarchies by nesting record sets within other child record sets. There is no specified maximum depth of record set nesting; however, if you have large parent record sets you probably will notice severe performance degradation as you increase the number of embedded nesting levels. As with all client/server or distributed applications, it is always prudent to limit the amount of data that must flow across the network. The client should only request the data that it truly needs, and the server should attempt to do as much of the work as possible.

Beyond the physical limitations of the network and the basic architecture of the system, ADO provides several additional ways to optimize performance. When you execute ADO commands, you should always specify the command type. Otherwise, the OLE DB provider may need to make several additional requests to the server to determine what type of command you have issued. In addition, when you are executing commands that do not return any records, you should always use the adExecuteNoRecords value as part of the command type .

You should also attempt to avoid using the Refresh method for the Parameters collection of Command objects. While it may be a great convenience to have the application automatically build the parameter list, it comes at relatively high cost, especially in distributed environments. Although the manual parameter definition may require a great deal of coding, it will offer improved performance because the OLE DB provider does not need to make additional calls to the server to retrieve the parameter definitions .

Another method to reduce network traffic is to use should use hierarchical record sets and stored procedures that return multiple result sets. This can result in less network traffic for both data requests and the data retrievals. You should also use the UpdateBatch method to combine multiple record update requests into a single call.

Whenever possible, you should attempt to perform operations asynchronously. ADO makes it very simple to connect asynchronously and to execute asynchronous commands. It only makes sense that your programs should take advantage of this because it can increase the overall performance your system and provide additional control for time-consuming events.

ADO is a language-independent application-level object interface to OLE DB, Microsoft’s newest and most powerful data access paradigm. ADO is a lightweight interface designed to be flexible and powerful enough to handle any data access requirements. Using the OLE DB provider architecture, ADO supports connections to many different data sources.

ADO is made up of three main objects (Connection, Command, and Recordset), which can all be instantiated independently from one another. The Connection object establishes connections with data sources, manages transactions, and reports errors from the underlying OLE DB components. The Command object defines commands and parameters and can execute commands against Connection objects. The Recordset object provides methods to manage and manipulate result sets of information in both immediate and batch modes. Record sets can use server-side cursors for better performance, or they can use client-side cursors, which provide added functionality. ADO also supports the creation of dynamic record sets containing multiple result sets or hierarchical record sets.

ADO record sets provide several unique features designed to improve network performance and to aid in distributed and disconnected environments. Record sets provide methods to sort, filter, and search the contents of the Record set without requiring a requery of the database. Record set fabrication allows you to create a record set without using a data source and record set persistence allows you to save a record set to a file for later use. Record sets also provide additional methods to help identify and resolve conflicts that may arise due to batch or disconnected record updates.

For the latest information on Universal Data Access and ADO, please refer to the following Microsoft Web sites:

http://www.microsoft.com/data

http://www.microsoft.com/data/ado

The following Self Test questions will help you measure your understanding of the material presented in this chapter. Read all the choices carefully, as there may be more than one correct answer. Choose all correct answers for each question.