The Fields collection and Field object allow you to access each data column of the current record. 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 compose a new record or change existing data, and use the AddNew, Update, or UpdateBatch method of the Recordset object to apply the new or changed data.
Unlike Remote Data Objects (RDO), there is no explicit Edit method that must be specified. Updating on the Field object is as simple as changing the data.
This code fragment shows how to use the Field object to retrieve the name, type, and values for each data field of the current record:
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 = "pubs"
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
This code presumes you have already made a connection, and passed in an SQL command string into the variable cmdText. 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.