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