ADO Fields Collection

[Previous] [Next]

The ADO Fields collection contains the Field objects for your Recordset. Like all collections, the Fields collection exposes a Count property and an Item property (the default). With the Item property, you can return a particular Field by name or index.

ADO 2.5 has introduced some new methods to the collection: CancelUpdate, Resync, and Update. These methods provide functionality that's similar to that of the methods with the same names on the Recordset object. They're designed to be used on the Fields collection of the Record object because it directly exposes such methods. Calling these methods—or entering certain parameters when calling some of the methods—on the Fields collection of the Recordset object will generate a run-time error.

Let's take a look at the methods exposed by the Fields collection and listed in this next table.

Fields Collection Methods
Method Name	Description
Append	Adds a new Field to the collection
CancelUpdate	Cancels the pending changes for a record
Delete	Deletes a Field from the collection
Refresh	Refreshes the Fields collection
Resync	Resynchronizes the current record
Update	Submits the pending changes in a record

Append Method

You can use the Append method on the Fields collection to create your own Recordset object without using a database. You can populate the Fields collection with Field objects in this fashion and then call the Recordset.Open method to start adding records to your Recordset. If you're not trying to communicate with a database but would like to maintain your data in a Recordset object, this method is for you. Its parameters are as follows:

• Name This parameter accepts a string that represents the name of the new field.
• Type This parameter can take a byte value from DataTypeEnum and designates the data type for the new field.
• DefinedSize This parameter accepts long type data and is optional. It is the defined size for the new field.
• Attributes This optional parameter accepts long type data. It sets attributes for the new field.
• FieldValue This optional parameter accepts a Variant containing the value of the new field.

The DefinedSize parameter is helpful when using variable length strings and binary data. You don't need to use this parameter with data types that have a predefined size, such as integers. If you're using a numeric field, you can set the NumericScale and Precision properties on the Field object after creating the field with the Append method but prior to opening the Recordset by calling the Open method.

You can set attributes for the field you're creating by using the Attributes parameter on the Append method. You can use a combination of values from FieldAttributeEnum (shown in "Attributes Property") in this parameter.

Although you can use any entry in DataTypeEnum (see ADO Help) for the data type for your new field, you should refrain from using certain data types in this fashion. You should avoid types such as adVariant, adIUnknown, and adIDispatch in creatable Recordsets. I won't go into too much detail about why here. Suffice it to say that these data types could be used to store COM objects, but knowing how and when to decrement the reference counter for these objects can be tricky. To simplify this dilemma, ADO never decrements the reference counter. Thus, if you're trying to use a creatable Recordset to maintain COM objects, you're liable to encounter behavior that looks like a memory leak. In other words, don't do it. Your best bet is to stick with the data types that ADO uses to store data from your database.

You can also call the Append method on the Fields collection of a Record object, if the Record allows it. The FieldValue parameter accepts a Variant for the value of the new field. This parameter was added in ADO 2.5 and is designed for the Fields collection on the Record object. As mentioned before, if you use this parameter on the Fields collection of a Recordset object, you'll generate a run-time error.

CancelUpdate Method

This method is similar to the CancelUpdate method on the Recordset object. You can use it to cancel the pending changes in the Fields collection of a Record object. Calling this method on the Fields collection of a Recordset object will generate a run-time error.

Delete Method

You can use the Delete method to remove a Field object from the Fields collection. The Delete method accepts a single required parameter, as follows:

• Index This parameter specifies the Field you want to delete.

The Index parameter accepts a long integer or a string so that you can specify the Field you want to delete by name or by number.

You can call the Delete method on the Fields collection of a creatable Recordset object that you have yet to open. You can also call the Delete method on the Fields collection of a Record object to delete a record-specific field, if you have permission to do so.

Refresh Method

Call the Refresh method to refresh the Fields collection. This method has no visible effect on the Fields collection of the Record or Recordset object.

Resync Method

The Fields collection exposes a Resync method similar to the Resync method on the Recordset object and is designed to supply similar functionality for the Record object. This method forces ADO to refetch the contents of the Record from your data store. Calling the Resync method on the Fields collection of a Recordset object will cause a run-time error.

The Resync method accepts one optional parameter, as follows:

• ResyncValues This optional parameter is of type ResyncEnum and defaults to adResyncAllValues. It can be used to control where the newly fetched data is stored—in the Value property or in the UnderlyingValue property of each Field.

For more information, see the discussion of the Resync method on the Recordset object in the first "Resync Method" section in this chapter.

Update Method

The Update method on the Fields collection is designed to let you submit the changes stored in the Fields collection of a Record object to your data store. This method is similar to the Update method on the Recordset object. Calling this method on the Fields collection of the Recordset object will generate a run-time error.