For a Field object on a Recordset, the Type property is read-only. However, you must be aware of the field type when copying data to or from a field in code or a “Type mismatch” error may occur. For example, you cannot copy Text data to an Integer field.

The Type property of a Field object on a Recordset is determined by the underlying table from which the record was retrieved. If you create the table and its fields by using DAO data-definition language (DDL) statements, you can easily determine the data type of the table’s fields.

If you’re accessing external data through an installable ISAM driver, the data types within external tables may be different from those defined within Microsoft Jet. The installable ISAM driver for the external data source converts external data types into their closest equivalent DAO data type.

See Also   For more information on data type conversion in external data sources, see Chapter 18, “Accessing External Data.”

The GUID Data Type

The GUID data type is used to store a globally unique identifier, a unique string of digits that identifies OLE objects, Microsoft SQL Server remote procedure calls, and other entities that need a unique reference identification.

Note   The GUID data type is also used in the Database object’s ReplicaID property to identify a replica. For information on replicas, see Chapter 20, “Using Replication in Your Application.”

The Text Data Type

For Field objects declared as type Text, you must set the Size property, which indicates the length of the longest string that can be stored in the field. All other types of Field objects have their Size property set automatically.

The Currency Data Type

If you need to store monetary values, use fields of type Currency. Don’t use any of the number data types (such as Single) for currency values, because numbers to the right of the decimal may be rounded during calculations. The Currency data type always maintains a fixed number of digits to the right of the decimal.

The Long Data Type

In some tables, you’ll want to store a series of sequential numbers to uniquely identify records. For example, you may want to start customer order records at order number 1 and begin counting upward.

Microsoft Access can automatically insert unique numbers in a field, saving your application the effort of generating unique identifiers to be used within a primary key field. To take advantage of this capability, define a field with the Long data type and set the dbAutoIncrField constant in the Field object’s Attributes property. Auto-incrementing fields start at 1 and increment sequentially. Fields of this type are also referred to as AutoNumber fields.

If you want to establish a primary key/foreign key relationship between two tables by using an AutoNumber field, make sure that the foreign key field is also defined as Long.

You can also set the DAO DefaultValue property of a Field object on a TableDef object to a special value called GenUniqueId( ). This causes a random number to be assigned to this field whenever a new record is added or created. The field’s Type property must be Long.

Note   A Field object’s data type is read/write before the field is appended to a table’s Fields collection, and read-only after it’s appended.

See Also   For more information on the DAO Attributes and DefaultValue properties, search the Help index for the name of the property.

The OLE Object and Memo Data Types

OLE Object and Memo fields are collectively referred to as large value fields because they are typically much larger than fields of other data types. OLE Object fields consist of binary data up to 1.2 gigabytes in size. This type is used to store pictures, files, or other raw binary data. Memo fields are used to store lengthy text and numbers, such as comments or explanations. The size of a Memo field is limited by the maximum size of the database.

Records within a Recordset object must fit on the 2K data pages supported by the Microsoft Jet database engine. Each Field object you include in the table definition counts toward this 2K total, including OLE Object and Memo fields. However, the amount stored for OLE Object and Memo fields is only 14 bytes per non-null field, and only 1 byte for null fields. The 14 bytes is used to store a pointer to the actual data for these fields, which is located on additional 2K pages. The amount of data committed to each text field isn’t set until you actually store data in the field. You can overcommit a data page by defining more text fields than there would be room for, but no more than about 2K of actual data can be stored in a record. For example, you can define fifteen 250-byte text fields in a record, but the total number of characters stored must be less than 2K.

When you query tables containing large value fields, don’t include those fields in the field list unless you need them, because returning large value fields takes time. Also, be aware that you can’t index large value fields.

A snapshot- or forward-only-type Recordset object opened against large value fields in an .mdb file doesn’t actually contain that data. Instead, the snapshot maintains references to the data in the original tables, the same way a dynaset refers to all data.

Handling Large Value Data

Sometimes you’ll need to read or copy data from a large value field where you don’t have sufficient memory to copy the entire field in a single statement. Instead, you have to break up the data into smaller units, or chunks, that will fit available memory. The FieldSize property tells you how large the field is, measured in bytes. Then you can use the GetChunk method to copy a specific number of bytes to a buffer, and use the AppendChunk method to copy the buffer to the final location. You then continue using GetChunk and AppendChunk until the entire field is copied.

See Also   For more information on DAO field data types, search the Help index for “data types, Microsoft Jet.”

Reading and Writing Data

When you read or write data to a field, you’re actually reading or setting the DAO Value property of a Field object. The DAO Value property is the default property of a Field object. Therefore, you can set the DAO Value property of the LastName field in the rstEmployees Recordset in any of the following ways:

rstEmployees!LastName.Value = strName
rstEmployees!LastName = strName
rstEmployees![LastName] = strName

Write Permission

The tables underlying a Recordset object may not permit you to modify data, even though the Recordset is of type dynaset or table, which are usually updatable. Check the Updatable property of the Recordset to determine whether its data can be changed. If the property is True, the Recordset object can be updated.

Individual fields within an updatable Recordset object may not be updatable, and trying to write to these fields generates a run-time error. To determine whether a given field is updatable, check the DataUpdatable property of the corresponding Field object in the Fields collection of the Recordset. The following example returns True if all fields in the dynaset created by strQuery are updatable and returns False otherwise.

Function blnUpdatable(strQuery As String) As Boolean
	Dim dbs As Database, rstDynaset As Recordset, intI As Integer

	On Error GoTo ErrorHandler

	' Initialize the function's return value to True.
	blnUpdatable = True

	Set dbs = CurrentDb
	Set rstDynaset = dbs.OpenRecordset(strQuery, dbOpenDynaset)

	' If the entire dynaset isn't updatable, return False.
	If rstDynaset.Updatable = False Then
		blnUpdatable = False
	Else
		' If the dynaset is updatable, check if all fields in the dynaset are updatable.
		' If one of the fields isn't updatable, return False.
		For intI = 0 To rstDynaset.Fields.Count - 1
			If rstDynaset.Fields(intI).DataUpdatable = False Then
				blnUpdatable = False
				Exit For
			End If
		Next intI
	End If

ErrorHandler:
	Select Case Err
		Case 0
			Exit Function
		Case Else
			MsgBox "Error " & Err & ": " & Error, vbOKOnly, "ERROR"
			Exit Function
	End Select

End Function

Criteria

Any single field can impose a number of criteria on data in that field when records are added or updated. These criteria are defined by a handful of properties. The DAO AllowZeroLength property on a Text or Memo field indicates whether or not the field will accept a zero-length string (""). The DAO Required property indicates whether or not some value must be entered in the field, or if it instead can accept a Null value. For a Field object on a Recordset, these properties are read-only; their state is determined by the underlying table.

See Also   For more information on the DAO AllowZeroLength and Required properties, search the Help index for the name of the property.

Field-Level Data Validation

Validation is the process of determining whether data entered into a field’s DAO Value property is within an acceptable range. A Field object on a Recordset may have the DAO ValidationRule and ValidationText properties set. The DAO ValidationRule property is simply a criteria expression, similar to the criteria of an SQL WHERE clause, without the WHERE keyword. The DAO ValidationText property is a string that Microsoft Access displays in an error message if you try to enter data in the field that’s outside the limits of the DAO ValidationRule property. If you’re using DAO in your code, then you can use the DAO ValidationText for a message that you want to display to the user.

Note   The DAO ValidationRule and ValidationText properties also exist at the Recordset level. These are read-only properties, reflecting the table-level validation scheme established on the table from which the current record is retrieved.

A Field object on a Recordset also features the ValidateOnSet property. When the ValidateOnSet property is set to True, Microsoft Access checks validation as soon as the field’s DAO Value property is set. When it’s set to False (the default), Microsoft Access checks validation only when the completed record is updated. For example, if you’re adding data to a record that contains a large Memo or OLE Object field and that has the DAO ValidationRule property set, you should determine whether the new data violates the validation rule before trying to write the data—you should write the data when the field value is set. To do so, set the ValidateOnSet property to True. If you wait to check validation until the entire record is written to disk, you may waste time trying to write an invalid record to disk.

See Also   For more information on the DAO ValidationRule, ValidationText, and ValidateOnSet properties, search the Help index for the name of the property.

Tracing the Origin of Dynaset Fields

A dynaset-type Recordset object can include records from more than one source table. Also, within a single record, fields from different tables can be joined into new records. Sometimes it’s useful to know the table from which a field originated. The SourceTable property of a Field object returns the name of the table from which the field’s current data was retrieved.

Within a query, a field can be renamed for display purposes. For example, in an SQL SELECT query, the AS operator in the select field list can create an alias for one of the returned fields. In a Recordset based on an SQL query, a field that has been aliased is represented by a Field object whose DAO Name property reflects the alias, not the original field name. To find out the original field name, check the Field object’s SourceField property.