Using the Fields Collection and Field Object

The Fields collection and Field object allow you to access each data column of the current row. The Fields collection can be accessed through the Recordset object and the Field object can be accessed through the Fields collection by using the default indexing method. You can use the Field object to create a new row or change existing data, and use the AddNew, Update, or UpdateBatch method of the Recordset object to apply the new or changed data. An explicit Edit method does not need to specified.

This code fragment shows how to use the Field object to retrieve the name, type, and values for each data column of the current row. This code assumes you have made a connection and passed an SQL command string to the cmdText variable. After the Recordset object is created, the Fields collection can be retrieved. The example loops through the Fields collection to retrieve each Field object. The Name, Type, and Value property of each Field object is printed.

Dim rs As New ADODB.Recordset
Dim fld As ADODB.Field
Dim cn As ADODB.Connection
Dim cmdText As String

cn.Provider = "sqloledb"
cn.Properties("Data Source").Value = "MyServerName"
cn.Properties("Initial Catalog").Value = "northwind"
cn.Properties("Integrated Security").Value = "SSPI"
cn.Open

cmdText = "select * from authors"

rs.Open cmdText, cn
Set Flds = rs.Fields
Dim TotalCount As Integer
TotalCount = Flds.Count

For Each fld In Flds
    Debug.Print fld.Name
    Debug.Print fld.Type
    Debug.Print fld.Value
Next
rs.Close