ADO - ADO Field Object and Fields Collection

The ADO Field Object represents a single column of data within an ADO Recordset. When a Recordset is retrieved from a database, each column returned by the query is represented as a Field object. For example, if a query returns EmployeeID, EmployeeName, Department, and Salary, each of these columns becomes a separate Field object. The collection of all these Field objects is called the Fields Collection. This allows developers to access database column values dynamically rather than having to work with each column individually.

1. Understanding the Field Object

A Field object provides information about a particular column in a Recordset. It contains both the actual value stored in that column and metadata describing the column.

For example:

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

rs.Open "SELECT EmployeeID, EmployeeName, Salary FROM Employees", _
        conn, adOpenStatic, adLockReadOnly

MsgBox rs.Fields("EmployeeName").Value

Here:

  • rs is the Recordset.

  • Fields represents the Fields Collection.

  • "EmployeeName" identifies a particular Field.

  • .Value retrieves the value stored in that field for the current record.

If the current record contains the employee name "John", the expression:

rs.Fields("EmployeeName").Value

returns:

John

The .Value property is commonly used when retrieving the actual data.

2. Fields Collection

The Fields Collection contains all Field objects belonging to the current Recordset.

Suppose the query is:

SELECT EmployeeID, EmployeeName, Department, Salary
FROM Employees;

The Recordset's Fields Collection contains four Field objects:

Fields(0) → EmployeeID
Fields(1) → EmployeeName
Fields(2) → Department
Fields(3) → Salary

You can access fields either by their numerical position or by their name.

Using an index:

rs.Fields(0).Value

Using a field name:

rs.Fields("EmployeeName").Value

Using the shorter syntax:

rs("EmployeeName")

Using the field name is generally easier to understand because the code clearly identifies the column being accessed.

3. Accessing Fields by Index

Every Field in the Fields Collection has an index beginning with zero.

For example:

For i = 0 To rs.Fields.Count - 1
    Debug.Print rs.Fields(i).Name
Next

This code displays the names of all columns returned by the query.

If the query returns:

EmployeeID
EmployeeName
Department
Salary

the output will be:

EmployeeID
EmployeeName
Department
Salary

The Count property tells you how many Field objects are contained in the collection.

rs.Fields.Count

If the Recordset contains five columns, this expression returns:

5

4. Accessing Fields by Name

A Field can also be accessed using its column name.

Dim employeeName As String

employeeName = rs.Fields("EmployeeName").Value

This approach is particularly useful when the structure of the Recordset is known.

You can also use:

employeeName = rs("EmployeeName")

The shorter syntax is essentially a convenient way of accessing the Field through the Recordset.

5. Important Properties of the Field Object

The Field object contains several properties that provide information about a database column.

Name

The Name property returns the name of the field.

Debug.Print rs.Fields(0).Name

For example:

EmployeeID

This is useful when you need to discover the structure of a Recordset dynamically.

Value

The Value property contains the actual value of the field for the current Recordset row.

Debug.Print rs.Fields("Salary").Value

If the current employee's salary is 50000, the result is:

50000

Value is the default property of the Field object, so this is also possible:

Debug.Print rs.Fields("Salary")

However, explicitly using .Value often makes the code clearer.

Type

The Type property identifies the data type of the Field.

Debug.Print rs.Fields("Salary").Type

The returned value is an ADO data-type constant or corresponding numeric value.

The type can indicate whether the column contains values such as:

  • Integer

  • String

  • Date

  • Decimal

  • Boolean

  • Binary data

This is useful when developing applications that need to process database columns dynamically.

DefinedSize

The DefinedSize property indicates the defined size of a field.

For example, a database column defined as:

VARCHAR(100)

may have a corresponding defined size of 100.

Debug.Print rs.Fields("EmployeeName").DefinedSize

This can be useful when examining database metadata.

ActualSize

The ActualSize property indicates the actual size of the data contained in the Field.

For example, if the field contains:

Robert

the actual size is based on the stored value rather than the maximum size defined for the column.

This is different from DefinedSize.

6. Difference Between DefinedSize and ActualSize

Consider the following database column:

EmployeeName VARCHAR(100)

If the current value is:

David

then:

DefinedSize = 100
ActualSize  = 5

The defined size represents the maximum or declared size of the column, while the actual size relates to the data currently stored in the Field.

This distinction becomes useful when handling variable-length data.

7. Handling NULL Values

One important consideration when working with Field objects is the possibility of a database value being NULL.

For example:

Debug.Print rs.Fields("PhoneNumber").Value

If PhoneNumber contains a database NULL, directly assigning it to certain variables can cause problems.

You can use the IsNull function:

If IsNull(rs.Fields("PhoneNumber").Value) Then
    Debug.Print "Phone number is not available"
Else
    Debug.Print rs.Fields("PhoneNumber").Value
End If

This allows the application to handle missing database values safely.

Another common approach is:

Dim phone As String

If IsNull(rs.Fields("PhoneNumber").Value) Then
    phone = ""
Else
    phone = rs.Fields("PhoneNumber").Value
End If

This converts a database NULL into an empty string for application processing.

8. Iterating Through the Fields Collection

The Fields Collection becomes particularly useful when the application does not know the column names in advance.

For example:

Dim i As Integer

For i = 0 To rs.Fields.Count - 1
    Debug.Print rs.Fields(i).Name
    Debug.Print rs.Fields(i).Value
Next

This code processes every field in the current record.

If the Recordset contains:

Field Value
EmployeeID 101
EmployeeName John
Department Sales
Salary 45000

the loop can retrieve both the column name and its corresponding value.

This approach is useful for generic database applications, reporting tools, administrative utilities, and data-export programs.

9. Processing Multiple Records

The Fields Collection represents the columns of the current record. To process all records, you need to combine Field iteration with Recordset navigation.

Do Until rs.EOF

    For i = 0 To rs.Fields.Count - 1
        Debug.Print rs.Fields(i).Name & ": " & _
                    rs.Fields(i).Value
    Next

    rs.MoveNext
Loop

The process works as follows:

  1. The application starts at the first record.

  2. The Fields Collection is examined.

  3. Each Field is processed.

  4. MoveNext moves to the next record.

  5. The process continues until EOF is reached.

This provides a generic mechanism for processing an entire Recordset.

10. Using Field Metadata

The Field object is not limited to retrieving values. It can also provide information about the database column.

For example:

For Each fld In rs.Fields
    Debug.Print "Name: " & fld.Name
    Debug.Print "Type: " & fld.Type
    Debug.Print "Size: " & fld.DefinedSize
Next

This can be useful when building applications that dynamically inspect database structures.

A reporting program, for example, could use this information to determine which columns exist before generating a report.

11. Field Attributes

ADO Field objects can also expose attributes describing how a field behaves.

For example:

Debug.Print rs.Fields("EmployeeID").Attributes

The attributes can provide information about characteristics such as whether the field is:

  • Read-only

  • Updatable

  • Nullable

  • Auto-incrementing

  • A key field

The exact attributes depend on the provider and database system.

12. Using Fields Collection for Dynamic Applications

One of the biggest advantages of the Fields Collection is that it allows applications to work with database results without hard-coding every column.

For example, instead of writing:

Debug.Print rs("EmployeeID")
Debug.Print rs("EmployeeName")
Debug.Print rs("Department")
Debug.Print rs("Salary")

you can write:

Dim fld As ADODB.Field

For Each fld In rs.Fields
    Debug.Print fld.Name & ": " & fld.Value
Next

This means the same code can work with different queries that return different numbers of columns.

For example, a query returning five fields and another query returning ten fields can both be processed using the same loop.

13. Updating a Field Value

If the Recordset and Field support updates, a Field's value can be changed.

rs.Fields("Salary").Value = 55000

After modifying the value, the Recordset can be updated:

rs.Update

For example:

rs.Fields("Department").Value = "Finance"
rs.Update

The ability to update a Field depends on factors such as the Recordset's cursor type, lock type, query, provider, and whether the underlying data source is updatable.

14. Field Object vs Fields Collection

These two terms should not be confused.

A Field Object represents one column.

For example:

rs.Fields("EmployeeName")

represents the EmployeeName field.

The Fields Collection represents all fields in the Recordset.

rs.Fields

Conceptually:

Recordset
   |
   +-- Fields Collection
          |
          +-- Field: EmployeeID
          +-- Field: EmployeeName
          +-- Field: Department
          +-- Field: Salary

Therefore, the relationship is:

Recordset → Fields Collection → Individual Field Objects

15. Practical Example

Consider a database table named Employees:

EmployeeID
EmployeeName
Department
Salary

An ADO program might retrieve the records using:

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

Set rs = New ADODB.Recordset

rs.Open "SELECT EmployeeID, EmployeeName, Department, Salary FROM Employees", _
        conn, adOpenStatic, adLockReadOnly

Do Until rs.EOF

    For Each fld In rs.Fields
        Debug.Print fld.Name & " = " & fld.Value
    Next

    rs.MoveNext

Loop

rs.Close
Set rs = Nothing

This example demonstrates the main purpose of the Fields Collection. The application does not need to know the number of columns beforehand. It can discover and process each Field dynamically.

16. Advantages of the Field Object and Fields Collection

The Field Object and Fields Collection provide several advantages:

  1. They provide direct access to individual database columns.

  2. They allow applications to inspect column metadata.

  3. They support dynamic processing of Recordset structures.

  4. They make generic database utilities easier to develop.

  5. They allow programs to process an unknown number of columns.

  6. They provide access to field names, values, types, and sizes.

  7. They can be used for data display, reporting, and export operations.

  8. They can support modification of database values when the Recordset is updateable.

17. Common Mistakes

A common mistake is attempting to access a field that does not exist:

rs.Fields("UnknownColumn").Value

This can generate an error.

Another common mistake is ignoring NULL values:

Dim name As String
name = rs.Fields("EmployeeName").Value

If the database contains NULL, the assignment may fail depending on the context.

It is also important to remember that a Field value belongs to the current Recordset row. Moving to another record changes the values returned by the same Field object reference.

18. Summary

The ADO Field Object represents an individual column in a Recordset, while the Fields Collection contains all the Field objects associated with that Recordset. Through these objects, an application can retrieve database values, examine column names and data types, determine field sizes, handle NULL values, and dynamically process database results.

The basic relationship can be remembered as:

Recordset
    ↓
Fields Collection
    ↓
Field Object
    ↓
Field Value

For example:

rs.Fields("EmployeeName").Value

means that the application is accessing the EmployeeName Field through the Recordset's Fields Collection and retrieving its value for the current record.

Understanding the Field Object and Fields Collection is particularly important when developing dynamic ADO applications, because it allows programs to work with database structures programmatically rather than depending entirely on fixed column names and positions.