Constructing Recordset Objects

Team-Fly

Depending on the technique you choose to execute the query, ADO will either manufacture a default Recordset object for you or will expect you to have one constructed and ready to go prior to executing the query. Frankly, I prefer the latter, because building my own Recordset gives me the greatest flexibility in how the rowset is managed once it arrives. However, because ADO's default Recordset is extremely efficient, having ADO manufacture it for me is not that big a deal. The default Recordset object is set up as RO/FO (read-only/forward-only) with its CacheSize property set to 1. This is generates a "firehose" or cursorless result set to hold the rowset returned from the query.

Before we start discussing the benefits of cursorless result sets, let's visit the techniques used to declare the Recordset in the first place. It's really pretty simple.

 Dim rs As ADODB.Recordset 

And somewhat later in your code (just before you need the Recordset):

 set rs = New ADODB.Recordset 

No, I don't recommend use of the seemingly easier:

 Dim rs As New ADODB.Recordset 

If you use the "Dim xx as New yyy" syntax, you won't face the wrath of God. However, you will pay a performance penalty each time the Recordset object is referenced. You'll also discover that the NextRecordset method no longer returns Nothing when there are no further result sets. But I talked about this in Chapter 2.

Understanding the Field Object

A Recordset contains from one to (almost) any number of Field objects—one for each column in the row. The answer to your question (your query) is stored in the Field object—more specifically, in the Value property of the Field object. This is also where you place new or changed data to be transmitted back to the data provider. Basically, the Field object corresponds to the term "column" in relational database terms, rows being composed of one or more columns, just as Recordset objects are composed of one or more Field objects.

The Field object itself describes the data's size, origin, precision, numeric scale, source table, and column, and includes a whole series of bit flags that are stored in the Attributes property. These bit flags are all read-only unless you are constructing a Field object using the Recordset Append method. Table 6-1 shows the enumerated values for the Attributes property. I discuss the remaining Field object properties later in this chapter.

Table 6-1: Field Attributes Property enumerations

ATTRIBUTES PROPERTYENUMERATION

BIT

DESCRIPTION

adFldCacheDeferred

0×1000

Indicates that the provider caches field values and that subsequent reads are done from the cache.

adFldFixed

0×10

Indicates that the field contains fixed-length data.

adFldIsChapter

0×2000

Indicates that the field contains a chapter value, which specifies a specific child Recordset related to this parent field. Typically, chapter fields are used with data shaping or filters.

adFldIsCollection

0×40000

Indicates that the field specifies that the resource represented by the record is a collection of other resources, such as a folder, rather than a simple resource, such as a text file.

adFldIsDefaultStream

0×20000

Indicates that the field contains the default stream for the resource represented by the record. For example, the default stream can be the HTML content of a root folder on a Web site, which is automatically served when the root URL is specified.

adFldIsNullable

0×20

Indicates that the field accepts null values.

adFldIsRowURL

0×10000

Indicates that the field contains the URL that names the resource from the data store represented by the record.

adFldKeyColumn

0×8000

Indicates that the field is the primary key of the underlying rowset. Also can indicate that the field is part of a compound primary key.

adFldLong

0×80

Indicates that the field is a long binary field. Also indicates that you can use the AppendChunk and GetChunk methods.

adFldMayBeNull

0×40

Indicates that you can read null values from the field.

adFldMayDefer

0×2

Indicates that the field is deferred, that is, the field values are not retrieved from the data source with the whole record, but only when you explicitly access them.

adFldNegativeScale

0×4000

Indicates that the field represents a numeric value from a column that supports negative scale values. The scale is specified by the NumericScale property.

adFldRowID

0×100

Indicates that the field contains a persistent row identifier that cannot be written to and that has no meaningful value except to identify the row (such as a record number, unique identifier, and so forth).

adFldRowVersion

0×200

Indicates that the field contains some kind of time or date stamp used to track updates.

adFldUnknownUpdatable

0×8

Indicates that the provider cannot determine whether you can write to the field.

adFldUnspecified

−1

Indicates that the provider does not specify the field attributes.

adFldUpdatable

0×4

Indicates that you can write to the field.

Tip 

ADO does not really understand one of the primary reasons for creating Views—to protect the database. That is, we construct a View with permissions granted to access a subset of the underlying tables. However, if you use client-side cursors, ADO bypasses the View and attempts to access the root tables directly when you attempt to update the data. This is because when ADO constructs the meta-data to perform an UPDATE, DELETE, or INSERT, it has no idea that it's really accessing a View. So, when it comes time to perform the action query, it knows just where to go—to the root table.


Team-Fly


ADO Examples and Best Practices
Ado Examples and Best Practices
ISBN: 189311516X
EAN: 2147483647
Year: 2000
Pages: 106

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net