Fields Collection and Field Object

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.

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.