by Kevin D. Runnels and Eric Brown
The Remote Data Objects (RDO) library has been the mainstay of Visual Basic programmers for traditional client/server development since it premiered with VB4. This robust object model is still appropriate for many client/server projects for many reasons (see Chapter 22, "Building Client Front Ends with Visual Basic and RDO"). Unfortunately, the underlying architecture of RDO 2.0 (RDO2) is too closely tied to accessing relational database management systems (RDBMSs) and doesn't provide the flexibility needed to access the many different data stores in use in today's corporate environment.
To address this problem, Microsoft has debuted the Active Data Objects, or ActiveX Data Objects (ADO) library, as the VB programmer's gateway into the world of Universal Data Access and OLE DB. ADO doesn't ship with Visual Basic 5.0 but is part of Visual Studio by virtue of its inclusion with Visual InterDev and Visual C++. That ADO didn't ship with VB 5.0 was a result of the timing of the releases of the two products, not a comment by Microsoft that the library didn't apply to VB programming. In fact, ADO can be used by VB, Visual C++, Visual J++, and Visual Basic for Applications (VBA). This chapter introduces you to ADO's features and capabilities. Along the way you'll learn about the ADO object model and how to use its properties and methods in your applications. According to Microsoft, ADO is the preferred method of accessing data from any arbitrary data source. Due to the relative immaturity of ADO, however, there are some caveats. Guidance to help you decide between RDO and ADO during this maturation period is offered at the end of the chapter. n
One of the first tasks that computers were given was the management of large amounts of data. It's a natural application for a computer system, with its rapid information retrieval and sorting and searching capabilities. It's understandable, then, that a primary area of research and development in the computing community has been the development of powerful yet easy-to-use tools for data management and retrieval.
The development of the relational database management system (RDBMS) has had a huge impact on the software development industry and has spawned the growth of many tools and related technologies. In Chapter 22 you learned about using Visual Basic with RDO2, a Microsoft technology providing object-based mechanisms for handling relational data. But data isn't always stored in a relational system. The growth of the Internet, in particular, has dramatically increased the need to manage a wide array of unstructured data types stored in many different formats on myriad devices. Clearly, a mechanism is needed that addresses this problem.
OLE DB is Microsoft's technology to provide what Microsoft terms as Universal Data Access. This technology provides a common data interface to many different data sources. Users frequently need access to data that might exist in spreadsheets, phone lists, email, and so on. Programmatic access to this wide range of data types, formats, architectures, APIs, and so forth has been complex and error-prone. However, the OLE DB API was designed to address how different types of data are accessed and used, and emphasizes the commonality in data access and manipulation such as opening the data source, requesting information, and navigating through the information.
For example, on any typical project, users will store some information in a database. The database is ideally suited for well structured, easily classified things such as parts lists and accounting information. Not all data fits well in a database, however, and users will also have phone lists or to-do lists in Microsoft Exchange or Outlook. They might use Microsoft Project to keep track of complex projects and subprojects. They might need to keep track of certain information that other users are entering into Microsoft Excel spreadsheets on the corporate network. OLE DB was designed to allow access to all this information stored in disparate file types and by different applications through a single API.
OLE DB, at its most basic level, is organized into an architecture described by data providers, service providers, and data consumers and their roles as users or providers of the OLE DB interface (see Figure 23.1):
FIG. 23.1
The data provider/service provider/data consumer architecture provides the flexibility
to extend the capabilities of a data access scheme by enhancing the capabilities
of any of these components.
Now that these different applications and different data types can all be accessed the same way through OLE DB, seemingly impossible things--such as relating joins between a spreadsheet, a database, and an email system--are now possible.
NOTE: Remember that virtually any application--spreadsheets, word processors, mail systems, and even the upcoming Windows NT 5.0 file system--can be an OLE DB Data Provider. Specialized service providers can then be developed to creatively extend the accessibility and usability of these data providers.
Unfortunately for VB programmers, OLE DB is implemented as a set of low-level C/C++ functions. This means that the calling language needs to be able to handle pointers, memory- management routines, specialized data structures, and other details that most VB programmers would rather avoid. To open this technology to a larger audience, Microsoft has wrapped the OLE DB API in a pointerless, language-neutral COM interface called Active Data Objects (ADO).
NOTE: Remember that the ADO library must be added to your project before you can address it from within your VB code. Choose Project, References, and select Microsoft ActiveX Data Objects 1.5 Library from the list.
ADO encapsulates OLE DB much as RDO encapsulates ODBC. ADO provides the access point for relational and ISAM-based databases, as well as access to data sources that aren't even considered databases but can provide row/column (tabular) results to queries. In ADO 1.5's current incarnation, several important features available for database access using RDO 2.0 haven't yet been implemented, including the use of asynchronous operations, queries as methods, and events. Chapter 22 provides for more information regarding RDO2 and synchronous versus asynchronous operations.
As you can see in Figure 23.2, the ADO object model is smaller than RDO2's, and its objects are generally more self-contained than those in RDO2. This deemphasis of the hierarchical approach allows greater reuse of the individual objects. Microsoft has taken what was good from the Data Access Objects (DAO) library and the RDO library and put them in ADO. As a result, VB programmers instantly feel at home with this new data access method.
NOTE: One feature lacking in the ADO model that programmers will miss is events. This is likely to change with the next release of ADO.
FIG. 23.2
The ADO object model is much more modular and free-standing than RDO, where the hierarchical
structure of the object library is much more prevalent.
Because OLE DB and ADO are so new, a limited number of data providers and service providers can be accessed. This will no doubt change in the near future; many companies are working feverishly to get into this new paradigm first and establish a presence. Microsoft has eased the situation for ODBC database access by supplying a provider code-named Kagera, which translates OLE DB to ODBC when communicating to an ODBC driver. Kagera works with any ODBC 2.0 driver that provides level-1 conformance.
Three main objects in the ADO model do most of the work: Connection, Command, and Recordset. These objects encompass more functionality than the single objects of the RDO2 model in that they provide enough features and functions to operate by themselves in many cases. The RDO2 objects rely heavily on other objects to provide some functionality, and the hierarchical structure enforces this interdependency between the objects. By contrast, with ADO, the Recordset object alone can connect to a database, retrieve results, manipulate the results, and save the data back to the server, all without relying on other objects to handle part of the task. Many times ADO will create objects for you, such as Connection objects, and use the object without you ever having to address the object yourself. However, for maximum flexibility in defining transactions, managing cursor types, and so on, you should become familiar with all the ADO objects and the functionality each of them encapsulates.
The key to using an object-based or object-oriented library is to understand the exposed object model. The details of implementation are encapsulated so that you don't need to worry about them (most of the time, at least). Although investigating the inner workings of the library is appropriate on some occasions, most of the time you need nothing more than a solid understanding of the exposed objects, including their properties and methods.
The following sections discuss the various ADO objects, along with suggestions on how best to use them in your applications. Several code samples are provided demonstrating their usage.
Methods: Open, BeginTrans, RollbackTrans, CommitTrans, Execute, Close
Properties: Attributes, CommandTimeout, ConnectionString, ConnectionTimeout, DefaultDatabase, IsolationLevel, Mode, Provider, Version
The Connection object represents a session with a data source and is used primarily to execute commands and manage transactions. This object is analogous to the rdoConnection object in the RDO2 model.
Connection objects can be created and then shared by different Recordset objects, resulting in large performance gains, as opposed to creating and closing connections individually for each Recordset object you might require. Also, your DBA won't bug you about the impact your code is having on the server at large and the other users of the database.
The code in Listing 23.1 uses the Connection object and many of its properties. The Error object provides detailed information about error conditions that might arise during execution.
Attribute VB_Name = "Module1"
Option Explicit
Sub ConnectionTest()
Dim oDB As New ADODB.Connection
Dim oError As ADODB.Error, strTemp As String
oDB.Open "TimeBill"
Debug.Print "ConnectionString = " & oDB.ConnectionString
Debug.Print "ConnectionTimeOut = " & oDB.ConnectionTimeout
Debug.Print "CommandTimeOut = " & oDB.CommandTimeout
Debug.Print "DefaultDatabase = " & oDB.DefaultDatabase
Debug.Print "Attributes = " & oDB.Attributes
Debug.Print "IsolationLevel = " & oDB.IsolationLevel
Debug.Print "Mode = " & oDB.Mode
Debug.Print "Properties = " & oDB.Properties.Count
Debug.Print "Provider = " & oDB.Provider
Debug.Print "State = " & oDB.State
Debug.Print "Version = " & oDB.Version
On Error Resume Next
oDB.BeginTrans ` begin a transaction
oDB.Execute ("INSERT INTO Employee (FirstName,LastName) VALUES(`D.', `Veloper')")
If Err.Number > 0 Or oError.Number > 0 Then
oDB.RollbackTrans
For Each oError In oDB.Errors
strTemp = oError.Number & Space(3) & oError.Description & Space(3) &
ÂoError.NativeError
Next
MsgBox strTemp
Else
oDB.CommitTrans
End If
oDB.Close
Set oDB = Nothing
Set oError = Nothing
End Sub
The Open Method You can invoke the Open method if the ConnectionString, UserID, and Password properties are set. If those properties aren't set, you can pass the values as parameters to the method.
The BeginTrans Method The BeginTrans method marks the starting point of a transaction that can later be committed or rolled back. This method has a return value that specifies the level of nesting. If a second BeginTrans method is invoked before CommitTrans or RollbackTrans is called, the level of nesting would cause the second invocation to return a value of 2. Calling CommitTrans or RollbackTrans affects all the data changes made since the last BeginTrans call.
The RollbackTrans Method The RollbackTrans method cancels the data-manipulation methods that have been invoked since the last BeginTrans.
The CommitTrans Method The CommitTrans method commits or finalizes the data- manipulation methods that have been invoked since the last BeginTrans.
The Execute Method The Execute method executes a statement passed in the CommandText argument. The CommandText argument is interpreted based on the value of the Options parameter. The CommandText argument is specific to the provider, but can be standard SQL if the data source is a relational database.
TIP: Specifying the Options parameter speeds up the call to the method because the method doesn't need to determine the type of the CommandText argument.
If the data source is a relational database, use this method to execute SQL strings and stored procedures that don't return a recordset or other return value. If the query returns rows, the results are stored in a new recordset object. If the query doesn't return rows, a closed recordset object is returned. For row-returning queries, using the Open method of the Recordset object might be easier.
The Close Method The Close method closes the connection to the data source. Any associated Recordset objects are also rolled back (if edits haven't been committed) and closed.
CAUTION: Be careful of scoping issues involving Connection objects. If a Connection object falls out of scope, any non-committed transactions are rolled back automatically.
The Attributes Property You can set the Attributes property to adXactCommitRetaining or adXactAbortRetaining. adXactCommitRetaining causes a new transaction to begin anytime a CommitTrans method is invoked. adXactAbortRetaining causes a new transaction to begin anytime a RollbackTrans method is invoked. Both values can be set by summing the two values.
The CommandTimeout Property The CommandTimeout property specifies how long the Connection object will wait, in seconds, before a timeout error is generated. A 0 value causes Connection to wait forever. An error is generated if the object times out. A timeout doesn't mean the server is unreachable, just that the command didn't finish in less time than the CommandTimeout period.
The ConnectionString Property The ConnectionString property specifies the information required to connect to a data source. This can be a standard data source name (DSN) or a fully qualified connection string consisting of argument = value separated by semicolons. Five reserved arguments are processed by ADO:
Any arguments other than these aren't processed by ADO and are sent directly to the provider.
NOTE: The Provider and FileName arguments in a ConnectionString property are mutually exclusive. The provider-specific FileName overrides any manually specified Provider argument.
TIP: Use ConnectionString in the open method to not require a user to have previously set up an ODBC data source.
The ConnectionTimeout Property The ConnectionTimeout property specifies how long, in seconds, the Connection object waits after attempting to establish a connection before a timeout error is generated. A timeout error doesn't mean that the server isn't available; it means only that a connection couldn't be established before a connection timeout occurred.
The DefaultDatabase Property The DefaultDatabase property specifies the default database name. A provider populates this property when a connection is established. Any data access methods are automatically applied against this default database, unless object names are fully qualified with another available database name.
The IsolationLevel Property The IsolationLevel property specifies the visibility of data during transactions. The default value, adXactCursorStability, indicates that pending changes aren't visible to other transactions until after they're committed. The other values for this property include the following:
The Mode Property The Mode property specifies the permissions available in modifying data. The default value, adModeUnknown, indicates that ADO can't determine the permissions. Other values include the following:
The Provider Property The Provider property specifies the name of the provider. The default provider is MSDASQL (Microsoft ODBC Provider for OLE DB). You can set this property through the ConnectString property or the ConnectionString argument to Connection.Open.
The Version Property The Version property returns the ADO version implementation number.
Methods: CreateParameter, Execute
Properties: ActiveConnection, CommandText, CommandTimeout, CommandType, Prepared
The Command object is a description of a command that's executed against a data source. It defines the text and type of the command, manages command arguments, executes the command, and creates Recordset objects. It contains a Parameters collection of Parameter objects and a Properties collection of Property objects. Use this object if you want to execute a query or stored procedure with input and output parameters. This object is similar to the rdoQuery object in the RDO2 library.
The code in Listing 23.2 is an example of using the Command object.
Attribute VB_Name = "Module4"
Option Explicit
Sub TestCommand()
Dim oCommand As ADODB.Command
Dim oParameterID As ADODB.Parameter
oCommand.ActiveConnection = "TimeBill"
oCommand.CommandText = "sp_GetEmployee"
oCommand.CommandTimeout = 0
oCommand.CommandType = adCmdStoredProc
oCommand.Prepared = True
oCommand.Name = "sp_GetEmployeeFromID"
` Add a parameter this way
oParameterID.Direction = adParamInput
oParameterID.Name = "ID"
oParameterID.NumericScale = 0
oParameterID.Type = adInteger
oParameterID.Value = 15
oCommand.Parameters.Append oParameterID ` add this item to the list
` can also add a parameter this way
oCommand.CreateParameter "Name", adVarChar, adParamOutput, 255, ""
oCommand.Execute ` execute the command object
Debug.Print "Name of the person with this ID is: " & oCommand.Parameters("Name").Value
Set oCommand = Nothing
Set oParameterID = Nothing
End Sub
The CreateParameter Method The CreateParameter method instantiates a new Parameter object that's populated with properties specified by the arguments to the CreateParameter method:
The Parameter object isn't automatically added to the Parameters collection but must be added by using the Parameters.Add method. This allows for data validation of the various parameter properties before the object is added to the collection.
The Execute Method The Execute method executes the command stored in the CommandText property. The RecordsAffected argument is a Long that the provider uses to return the number of records affected by the command. The Parameters argument is Variant array of parameter values passed with a SQL statement.
TIP: Don't rely on output parameters from a stored procedure contained in the Parameters array. Values from output parameters should be obtained from the Value property of Parameter objects.
The Options argument is a constant that helps the provider determine how to handle the CommandText property. The possible values are as follows:
The ActiveConnection Property The ActiveConnection property specifies to which Connection object the Command object belongs. The value can be a string containing the definition of the connection (as in ConnectString) or can be a Connection object. Setting the value to nothing disassociates the Command object from a connection. The property value can then be set to a different connection object, and the command can be re-executed.
The CommandText Property The CommandText property specifies the text of a command to be executed by a provider. The contents of this string are provider-specific and can be SQL or any special command the provider supports.
The CommandTimeout Property The CommandTimeout property specifies the length of time, in seconds, that the Command object waits for a provider to execute the command before a timeout error is generated.
The CommandType Property The CommandType property is the equivalent of and has the same values as the Options argument of the Execute method.
The Prepared Property The Prepared property specifies whether a temporary prepared statement is constructed from the command before it's executed. If the provider doesn't support the concept of precompiled prepared statements, this property is ignored.
Methods: AppendChunk
Properties: Attributes, Direction, Name, NumericScale, Precision, Size, Type, Value
The Parameter object represents a parameter or argument associated with a Command object. The parameter is typically used to represent input/output arguments of stored procedures. The Parameter object is a member of the Parameters collection and can be referenced as command.Parameters(index) or command.Parameters("name"). Because the Parameters collection is the default property of the Command object, Parameter objects can even more simply be referenced as command(index), command![name], or command("name"). (Refer to Listing 23.2 for an example of using the Parameter object.)
The AppendChunk Method The AppendChunk method appends data, typically BLOB (Binary Large Object) data, to a Parameter object. When memory is limited, you can use AppendChunk to assign data in chunks rather than all at once. This method can be used only if the adFldLong bit in the Attributes property is True.
The Attributes Property The Attributes property specifies one or more characteristics of the Parameter object. Individual values can be logically AND'ed to determine whether a certain attribute is present. Multiple values can be set by adding the values together. The possible values consist of the default value, adParamSigned, which specifies that the parameter accepts signed values, as well as adParamNullable and adParamLong, which indicates that the parameter accepts Null and Long binary data, respectively.
The Direction Property The Direction property specifies whether the parameter is used for input, output, or both. The parameter can also be a return value from a stored procedure. The default value, adParamInput, indicates an input parameter. adParamOutput indicates an output parameter. adParamInputOutput indicates an input/output parameter. adParamReturnValue specifies that the parameter is a return value.
The Name Property The Name property specifies the name used to identify the Parameter object.
The NumericScale Property The NumericScale property specifies the number of decimal places to which numeric values will be represented.
The Precision Property The Precision property specifies the maximum number of digits used to represent a value.
The Size Property The Size property specifies the maximum size of a Parameter object, in characters or bytes.
The Type Property The Type property specifies the data type of a Parameter object. Table 23.1 shows the possible values.
| Constant | Description |
| adBigInt | 8-byte signed integer |
| adBinary | Binary value |
| adBoolean | Boolean value |
| adBSTR | Null-terminated character string (Unicode B-String) |
| adChar | A string value |
| adCurrency | A currency value (8-byte signed integer scaled by 10.000) |
| adDate | A date value |
| adDBDate | A date value (yyymmdd) |
| adDBTime | A time value (hhmmss) |
| adDBTimeStamp | A date/time stamp (yyyymmddhhmmss plus a fraction in billionths) |
| adDecimal | An exact numeric value with a fixed precision and scale |
| adDouble | A double-precision floating-point value |
| adEmpty | No specified value |
| adError | 32-bit error code |
| adGUID | Globally Unique Identifier |
| adIDispatch | Pointer to OLE object IDispatch interface |
| adInteger | 4-byte signed integer |
| adIUnknown | Pointer to OLE object IUnknown interface |
| *adLongVarBinary | Long binary value |
| *adLongVarChar | Long string value |
| adLongVarWChar | Long string value |
| adNumeric | Exact numeric value with fixed precision and scale |
| adSingle | Single-precision floating-point value |
| adSmallInt | 2-byte signed integer |
| adTinyInt | 1-byte signed integer |
| adUnsignedbigInt | 8-byte unsigned integer |
| adUnsignedInt | 4-byte unsigned integer |
| adUnsignedSmallInt | 2-byte unsigned integer |
| adUnsignedTinyInt | 1-byte unsigned integer |
| adUserDefined | User-defined value |
| *adVarBinary | Binary value |
| *adVarChar | String value |
| adVariant | Variant value |
| *adVarWchar | Null-terminated Unicode character string |
| adWchar | Null-terminated Unicode character string |
The Value Property The Value property specifies the value of the Parameter object.
Methods: None
Properties: Attributes, Name, Type, Value
The Property object represents a provider-defined dynamic property. ADO predefines normal properties, such as Parameter.Precision or Connection.Timeout. Although these predefined properties can be accessed, they can't be changed or deleted. Also, some data sources will have unique data-source-specific properties associated with them. These dynamic properties will appear as Property objects in the Properties collection of associated Connection, Command, RecordSet, or Field objects.
The Attributes Property The Attributes property specifies one or more of the following values:
NOTE: Because the adPropNotSupported value is contained in a dynamic property created by the provider, it might seem strange that it's created in the first place if it isn't supported. It remains to be seen how this will be used, but it might be used for backward compatibility by newer versions of a data provider to indicate properties that are no longer supported.
The Name Property The Name property specifies the name to be used for the Property object.
The Type Property This property is the same as the Type property of the Parameter object. Table 23.1 in the earlier section "The Parameter Object" lists possible values.
The Value Property The Value property specifies the value assigned to the Property object.
Methods: AddNew, CancelBatch, CancelUpdate, Clone, Close, Delete, GetRows, Move, MoveFirst, MoveLast, MoveNext, MovePrevious, NextRecordset, Open, Requery, Resync, Supports, Update, UpdateBatch
Properties: AbsolutePage, AbsolutePosition, ActiveConnection, BOF, EOF, Bookmark, CacheSize, CursorType, EditMode, Filter, LockType, MaxRecords, PageCount, RecordCount, PageSize, Source, Status
The Recordset object represents your cursor of data that has been returned from a data source. This object allows iteration through the rows and allows data modification to occur. The Recordset object is very similar to the RDO rdoResultset object.
One interesting aspect of ADO is that any cursors created--such as keyset, static, dynamic, or forward-only cursors--are always built on the server. Another interesting and useful feature of ADO is that the Recordset object doesn't have an Edit method. Essentially, the recordset is always in "edit mode," and the Update method can be used to send any data modifications to the server. The Update method also can be skipped if you simply move the cursor to a new row. Any changes made to the data will be made at that time. This makes processing records in a While loop easy to write and to debug. The code in Listing 23.3 gives an example of using the Recordset object.
Attribute VB_Name = "Module2" Option Explicit Sub RecordSetTest() Dim oRS As New ADODB.Recordset Dim vntBookmark As Variant oRS.LockType = adLockOptimistic oRS.ActiveConnection = "TimeBill" oRS.CursorType = adOpenStatic oRS.Open "sp_GetEmployee (13)", Options:=adCmdStoredProc oRS.MoveLast oRS.MoveFirst Debug.Print "RecordCount = " & oRS.RecordCount Debug.Print "AbsolutePosition =" & oRS.AbsolutePosition Debug.Print "AbsolutePage = " & oRS.AbsolutePage Debug.Print "EditMode" & oRS.EditMode Debug.Print "MaxRecords = " & oRS.MaxRecords Debug.Print "State = " & oRS.State Debug.Print oRS.Filter Debug.Print oRS.Fields(0).Value oRS.Close Set oRS = Nothing End Sub
The AddNew Method The AddNew method creates new rows in the resultset. The Fields optional parameter is a variant or variant array containing the names of the fields to be added. The Values argument is a variant or variant array containing the values of the Fields that have been specified. The AddNew method works in immediate mode or batch update mode:
The CancelBatch Method The CancelBatch method cancels a pending batch update. The AffectRecords argument specifies the records that will be canceled. The possible values are adAffectCurrent, where only the current record is canceled; adAffectGroup, where only records that satisfy the Filter property are canceled; and the default, adAffectAll, where all data modifications since the last batch update method was invoked are canceled.
The CancelUpdate Method The CancelUpdate method discards any changes made to the current record since the last update method was invoked.
The Clone Method The Clone method creates a new instance of a recordset and assigns it to a variable. These recordsets still represent the same entity in terms of their row content, so changes made to one clone are visible to the other. However, the Close method must be called for each clone individually. Use this method if you want to be able to maintain more than one current record in a recordset. Cloning a recordset is faster than opening another recordset based on the recordset definition. The Close Method The Close method closes the cursor on the recordset and releases its resources.
The Delete Method This method deletes records in the recordset. Depending on the value of AffectRecords, it will delete all the records or just the current record. If this statement is located inside a transaction, the delete can be rolled back.
The GetRows Method The GetRows method retrieves and stores multiple rows of a Recordset object in a two-dimensional array. The optional Rows argument is a Long that specifies the number of records to retrieve. The optional Start argument is a String or Variant that specifies the bookmark at which the retrieval will begin. The optional Fields argument is a Variant that can be one of three values:
Use the GetRows method to convert the retrieved Recordset object into an array.
The Move Method The Move method moves the current record pointer in a Recordset. The NoOfRecords argument is a Long that specifies the number of records to move the current record pointer. The optional Start argument is a String or Variant that specifies a bookmark. If NoOfRecords is a positive number, the cursor moves forward by the specified number of rows. A negative number moves the cursor backward the specified number of rows. Moving beyond the first or last record in the Recordset sets the BOF or EOF property to True.
TIP: Using the CacheSize property with a value lower than the number of eligible rows of a Resultset provides a mechanism for locally buffering data. The Move method can be used to fetch additional rows from the provider by specifying a NoOfRecords that exceeds the CacheSize property.
The MoveFirst, MoveLast, MoveNext, and MovePrevious Methods This group of methods provides for moving the current pointer in a Resultset to the first record, the last record, the next record, or the previous record, respectively.
The NextRecordset Method The NextRecordset method provides a mechanism for moving through multiple recordsets generated from a single command. Each time the NextRecordset method is invoked, the next Recordset object available is returned. If no further Recordsets are available, the object returns nothing.
The Open Method The Open method opens a cursor of data. The data source doesn't already need to be open. The parameters to the open method are as follows:
The Requery Method The Requery method re-executes the query that generated the recordset. This method is equivalent to closing the recordset and reopening it.
The Resync Method Similar to the Requery method, the Resync method refreshes its data from the underlying data source. Unlike the Requery method, Resync won't retrieve any new records, but simply refreshes the rows that already exist. The optional AffectRecords argument determines how many records are affected by the method; the default value is adAffectAll. adAffectCurrent specifies that only the current record is refreshed; adAffectGroup specifies that records that satisfy the Filter property are affected.
Use the Resync method if you don't want to have a dynamic cursor defined but want to refresh data in your recordset, and your database exists in a multiuser environment.
The Supports Method The Supports method specifies whether a Recordset object can support specific types of functionality. The CursorOptions argument is a Long that specifies whether any one or more of the functionality options listed in Table 23.2 are available.
| Value | Functionality |
| adAddNew | The AddNew method is available. |
| adApproxPosition | AbsolutePosition and AbsolutePage properties are available. |
| adBookMark | The Bookmark property is available. |
| adDelete | The Delete method is available. |
| adHoldRecords | Pending changes can be held while more records are retrieved. |
| adMovePrevious | The recordset can be traversed backward without bookmarks. |
| adResync | The Resync method is available. |
| adUpdate | The Update method is available. |
| adUpdateBatch | The BatchUpdate method is available. |
NOTE: The Supports method isn't always correct; it simply indicates whether a given type of functionality is generally available. It doesn't guarantee that the function is always available under any circumstances.
The Update Method The Update method saves any changes made to a cursor. A single field can be updated by assigning a value to the Field object and invoking the Update method, or the Update method can be called and the Field name and value can be passed. Multiple fields can be updated at one time by passing a variant array of field names and a variant array of values.
The UpdateBatch Method The UpdateBatch method writes all pending batch updates to the data source. The optional AffectRecords argument has a default value of adAffectAll. adAffectCurrent, which specifies that only the current record is updated, and adAffectGroup, which specifies that all records that meet the current Filter property are affected, are also valid entries.
The AbsolutePage Property The Recordset object can be divided into a set of "pages" by using the PageSize property. The AbsolutePage property specifies which page to move for a new current record.
The AbsolutePosition Property The AbsolutePosition property specifies the ordinal position of the current pointer within a Recordset object.
The ActiveConnection Property The ActiveConnection property specifies the Connection object to which the Recordset belongs.
The BOF and EOF Properties The BOF and EOF properties indicate that the current pointer is before the first record or after the last record of a recordset.
The Bookmark Property The Bookmark property uniquely identifies the current record or sets the current record to the record identified by a bookmark. You can use bookmarks to position the current pointer in a resultset without using the Move methods. To save the bookmark for the current pointer, simply assign the value of the Bookmark property to a variable. The current pointer can be moved to the same record at a later time by setting the Bookmark property equal to the previously saved variable.
The CacheSize Property The CacheSize property specifies how many records are held in a local memory cache.
The CursorType Property The CursorType property indicates the type of cursor used by the Recordset object. The default value is adOpenForwardOnly. adOpenKeyset, adOpenDynamic, and adOpenStatic are also valid values.
The EditMode Property The EditMode property specifies the edit status of the current record. The property will return adEditNone when editing isn't being done. adEditInProgress specifies that the current record has been modified but the change hasn't yet been committed. The adEditAdd value indicates that the AddNew method has added a row to the recordset, but the new row hasn't yet been committed to the data source.
The Filter Property The Filter property specifies a filter for data contained in a recordset. This value can be configured several ways:
"FirstName = `Laurie' AND MiddleName = `Ann' AND Salary >= $100000"
The LockType Property The LockType property specifies the lock type to use when records are being edited. Applicable values consist of adLockReadOnly, a read-only setting; adLockPessimistic for pessimistic locks; adLockOptimistic for optimistic locks; and adLockBatchUpdate for batch updates. When optimistic locking is used, the record is locked in the database only when the Update method is called. When pessimistic locking is used, the record is locked whenever editing begins.
The MaxRecords Property The MaxRecords property puts a limit on the number of records returned from a query. The default value of 0 means that all records are returned. Use this property to limit the number of rows returned, especially in a low-memory situation.
The PageCount Property The PageCount property indicates how many pages of data the Recordset object contains. Pages are sized to equal the PageSize value. The number of rows in the Recordset divided by the PageSize property will equal the PageCount property.
The RecordCount Property The RecordCount property specifies the number of records in a Recordset object. If ADO can't determine the value, adUnknown is returned. In situations where the Recordset doesn't support bookmarks or approximate positioning, the entire recordset object has to be retrieved from the data source for the RecordCount property to be determined.
TIP: The RecordCount property isn't always reliable until all records in the recordset are accessed. To force this to occur, call MoveLast and then MoveFirst on the Recordset object.
The PageSize Property The PageSize property specifies how many rows of data make up a single page. The PageSize property multiplied by the PageCount property equals the number of rows in the recordset.
The Source Property The Source property indicates the source of the data contained in the recordset. This can be a Command object, SQL statement, stored procedure name, or table name.
The Status Property The Status property indicates the status of the current record. This examines the results of rows after batch updates. A value of adRecOK means the record was successfully saved. adRecNew indicates the record is new. adRecModified indicates that the record was modified. adRecDeleted indicates that the record was deleted. adRecUnmodified indicates that the record wasn't modified. Any other value indicates that the record wasn't saved for some reason. The values shown in Table 23.3 specify the different reasons the record wasn't saved.
| Value | Description |
| adRecInvalid | The record has an invalid bookmark. |
| adRecMultipleChanges | The record modification would affect multiple records. |
| adRecPendingChanges | The record refers to a pending insert. |
| adRecCanceled | The operation was canceled. |
| adRecCantRelease | The record lock is in contention. |
| adRecConcurrencyViolation | Optimistic concurrency was in use. |
| adRecIntegrityViolation | Integrity constraints were violated. |
| adRecMaxChangesExceeded | Too many changes are pending. |
| adRecObjectOpen | Conflict with an open storage object has occurred. |
| adRecOutOfMemory | Computer ran out of memory. |
| adRecPermissionDenied | User had insufficient permissions. |
| adRecSchemaViolation | Relational integrity or schema violation error has occurred. |
| adRecDBDeleted | Record was already deleted. |
Methods: AppendChunk, GetChunk
Properties: ActualSize, Attributes, DefinedSize, Name, NumericScale, OriginalValue, Precision, Type, Underlying Value, Value
The Field object contains information about the columns of a recordset and is analogous to the rdoColumn object in the RDO2 object library. The code in Listing 23.4 shows an example of using the Field object.
Attribute VB_Name = "Module2" Option Explicit Sub RecordSetTest() Dim oRS As New ADODB.Recordset Dim vntBookmark As Variant oRS.LockType = adLockOptimistic oRS.ActiveConnection = "TimeBill" oRS.CursorType = adOpenStatic oRS.Open "sp_GetEmployee (13)", Options:=adCmdStoredProc oRS.MoveLast oRS.MoveFirst Debug.Print "RecordCount = " & oRS.RecordCount Debug.Print "AbsolutePosition =" & oRS.AbsolutePosition Debug.Print "AbsolutePage = " & oRS.AbsolutePage Debug.Print "EditMode" & oRS.EditMode Debug.Print "MaxRecords = " & oRS.MaxRecords Debug.Print "State = " & oRS.State Debug.Print oRS.Filter Debug.Print oRS.Fields(0).Value oRS.Close Set oRS = Nothing End Sub
The AppendChunk Method The AppendChunk method adds large amounts of data (such as BLOB data) to a field object in small chunks. The first time AppendChunk is invoked, it overwrites any data in the field. Subsequent invocations append data to the field.
The GetChunk Method The GetChunk method returns large amounts of data (such as BLOB data or data in a text/memo field) from a field in small chunks. The adFldLong bit in the Attributes property must be set to True to use the GetChunk method.
The ActualSize Property The ActualSize property indicates size of the data contained in the field. The DefinedSize property specifies a maximum size for data, whereas the ActualSize property specifies the size of the data actually contained in the field. This is especially useful for variable-length character data.
The Attributes Property The Attributes property specifies the characteristics of the field and can be a sum of any of the following values:
The DefinedSize Property The DefinedSize property specifies the defined maximum size of a Field object and can be used to determine the capacity of a field.
The Name Property The Name property specifies the name that identifies the Field object.
The NumericScale Property The NumericScale property specifies how many decimal places numeric values are represented.
The OriginalValue Property The OriginalValue property specifies the value of the field since the last Update method. This acts as a buffer to allow retrieval of the original value of the field before data modification.
The Precision Property The Precision property specifies the maximum total number of digits used to represent numeric values.
The Type Property The Type property specifies the data type of Field object. Table 23.1 earlier in this chapter shows the possible values.
The Underlying Value Property (Variant) The Underlying Value property specifies the current value in the data store of a Field object's value. This value might be stale if another process has modified it since it was retrieved by the current transaction.
The Value Property The Value property indicates the current value of the field.
Methods: None
Properties: Description, HelpContext, HelpFile, NativeError, Number, Source, SQLState
The Error object contains information about errors encountered in a single operation. The Error object's properties contain specific details about each error, describing such things as where the error originated, help file information, and so on. Provider errors are placed in the Errors collection of the Connection object. If the errors are generated without the use of a Connection object, the error is located in Visual Basic's Err object. Multiple errors can be generated from a single ADO operation. (For an example of using the Error object, see Listing 23.1 earlier in this chapter.)
NOTE: Some Error objects can actually be warnings or informational messages returned from data sources.
The Description Property The Description property is a string that specifies a textual description of the error. The string can come from ADO or from a provider.
The HelpContext Property The HelpContext property is a Long value that's the help context ID for a topic in a standard Windows help file.
The HelpFile Property The HelpFile property is a string that specifies a fully qualified path name to a standard Windows help file.
The NativeError Property The NativeError property specifies the error code assigned by a provider-specific error.
The Number Property The Number property specifies the number that uniquely identifies an Error object.
The Source Property The Source property specifies the name of the object or application that generated the error. This value can be the object's class name or programmatic ID. ADODB errors are identified by the format ADODB.ObjectName.
The SQLState Property The SQLState property is a read-only string that's the five-character ANSI SQL standard error code.
This is a transition period for RDO2 and Active Data Objects. According to Microsoft, with the release of Visual Basic 5.0 Enterprise Edition and ADO 1.0, RDO2 is a superset of ADO. At the time of this writing, ADO 1.5 has been released but still doesn't offer all the features and functionality in RDO2. In the future, ADO will become a superset of RDO. Those features of RDO2 not currently found in ADO are asynchronous connections, asynchronous queries, events, integration with Transact-SQL Debugger and Connection Query Designer, and queries as methods.
At this time, if you're working with data that comes just from a database server and you need features not found in ADO and described earlier, RDO2 is the choice to make. If, however, you don't need the missing features that ADO doesn't yet implement, and you see the need to access data from multiple data providers, you should choose ADO. In today's I-net paradigm, it's also important to note that ADO is free-threaded whereas RDO2 isn't. This improves its scalability in high-performance, multiuser environments. You can easily use ADO to provide web access in a middle tier or server component, whereas you cannot with RDO.
If you choose to use RDO2 now, the later conversion to ADO will be a time-consuming activity. The object models are different in design and operation. Converting can even require changes in application design. One way to minimize the effect of changing from one library to another can be to write a COM object that hides the actual library being used. This buys you flexibility, but you might have some difficulty implementing functions available in RDO but not in ADO.
In this chapter, you learned about Microsoft's new architecture, Universal Data Access, that promises to provide a consistent, easy-to-use interface to many different data types. You were introduced to the ActiveX Data Objects library and walked through a breakdown of the objects and their associated properties and methods. You also evaluated when ADO is preferable over RDO2 for data access.
© Copyright, Macmillan Computer Publishing. All rights reserved.