ADO - Attributes property

In ADO (ActiveX Data Objects), the Attributes property describes certain characteristics or behaviors of a Field, Parameter, or Property object.

It is a bitmask value made up of one or more attribute constants combined together, which control or indicate how that object behaves (for example, whether a field is nullable, updatable, fixed-length, etc.).

 Purpose

The Attributes property tells you what rules or restrictions apply to a Field, Parameter, or Property.
It can also be used to set those attributes (for example, when defining parameters in a command).

 Applies To

  • Field.Attributes – describes characteristics of a recordset field.

  • Parameter.Attributes – describes characteristics of a command parameter.

  • Property.Attributes – describes characteristics of a dynamic property (rarely used).

 1. Field.Attributes

Indicates what properties a field (column) has — such as whether it’s updatable, nullable, or fixed-length.

Example:

Dim rs As ADODB.Recordset
Dim fld As ADODB.Field

rs.Open "SELECT * FROM Employees", conn, adOpenStatic, adLockOptimistic

For Each fld In rs.Fields
    Debug.Print fld.Name & " - " & fld.Attributes
Next

Each field’s Attributes value is a combination of constants (bitwise), so it might show values like 90, 2, etc., representing combined properties.

Common Field Attribute Constants

Constant Value Meaning
adFldMayDefer 2 Field data may be fetched later (lazy loading).
adFldUpdatable 4 Field can be updated.
adFldUnknownUpdatable 8 Update capability is unknown.
adFldFixed 16 Field has a fixed length (e.g., CHAR(10)).
adFldIsNullable 32 Field can contain NULL values.
adFldMayBeNull 64 Field may contain NULL (same as above; sometimes combined).
adFldLong 128 Field contains long binary or text data (e.g., Memo/BLOB).
adFldRowID 256 Field is a row-identity field (unique ID).
adFldRowVersion 512 Field is automatically updated with version info.
adFldCacheDeferred 4096 Field is cached for deferred updates.

 You can test individual attributes using the bitwise AND operator:

If (fld.Attributes And adFldIsNullable) <> 0 Then
    Debug.Print fld.Name & " allows NULL values."
End If

2. Parameter.Attributes

Defines the behavior of a Parameter in a Command (for stored procedures or parameterized queries).

Example:

Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter

Set cmd = New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandText = "sp_AddEmployee"
cmd.CommandType = adCmdStoredProc

Set prm = cmd.CreateParameter("EmpName", adVarChar, adParamInput, 50)
prm.Attributes = adParamNullable
cmd.Parameters.Append prm

This marks the parameter as nullable.

Common Parameter Attribute Constants

Constant Value Description
adParamSigned 16 Parameter accepts signed numbers.
adParamNullable 64 Parameter can contain NULL values.
adParamLong 128 Parameter can hold long binary or text data (e.g., BLOB, Memo).

 3. Property.Attributes

For Property objects, this defines characteristics like whether the property is read-only or append-only.

Constant Value Description
adPropNotSupported 0 Property not supported by provider.
adPropOptional 1 Property is optional.
adPropRead 2 Property is read-only.
adPropWrite 4 Property can be written.

Example:

Dim prop As ADODB.Property
For Each prop In conn.Properties
    Debug.Print prop.Name & " - " & prop.Attributes
Next

Understanding the Bitmask

Attributes is a bit field, meaning several flags can be combined using bitwise addition.

For example:

If fld.Attributes = adFldUpdatable + adFldIsNullable Then
    ' Field can be updated and allows NULLs
End If

Or to test:

If (fld.Attributes And adFldUpdatable) <> 0 Then
    Debug.Print "This field can be updated."
End If

 Summary

Object Purpose of Attributes Example
Field Describes column properties (nullable, updatable, etc.) rs.Fields("Name").Attributes
Parameter Describes parameter properties (nullable, signed, long) cmd.Parameters("EmpName").Attributes = adParamNullable
Property Describes property accessibility (read/write) prop.Attributes

Key Points

  • Attributes is a bitmask made up of constants.

  • Use bitwise AND (And) to check for specific attributes.

  • Read-only for fields in open recordsets, but settable for parameters before execution.

  • Helps determine data capabilities and limitations at runtime.

 Example: Checking Field Attributes

Dim fld As ADODB.Field
Set fld = rs.Fields("Salary")

If (fld.Attributes And adFldUpdatable) <> 0 Then
    Debug.Print "Salary field is updatable."
End If

If (fld.Attributes And adFldIsNullable) <> 0 Then
    Debug.Print "Salary field allows NULLs."
End If