VBA/Excel/Access/Word/Access/Recordset Field

Accessing Data with the Fields Collection and Field Objects

   <source lang="vb">

Sub field()

   Dim dbNorthwind As DAO.Database
   Dim dbPath As String
   DbPath = CurrentProject.Path & "\mydb.mdb"
   Set dbNorthwind = OpenDatabase(dbPath)
   
   Dim rsEmployees As DAO.Recordset
   Dim rsCustomers As DAO.Recordset
   Set rsEmployees = dbNorthwind.OpenRecordset("Employees", dbOpenTable)
   Set rsCustomers = dbNorthwind.OpenRecordset("Customers", dbOpenTable)
   I = 2
   With rsEmployees
       If Not .BOF Then .MoveFirst
       Do While Not .EOF
           Cells(I, 1).Value = .Fields(2)  "First name in third column
           Cells(I, 2).Value = .Fields(1)  "Last name in second column
           .MoveNext
           I = I + 1
       Loop
   End With

End Sub

</source>
   
  

Get rows from Recordset

   <source lang="vb">

Sub TestGetRows()

   Dim varValues As Variant
   Dim recSales As Recordset
   Dim intRowCount As Integer
   Dim intFieldCount As Integer
   Dim i As Integer
   Dim j As Integer
   Set recSales = CurrentDb().OpenRecordset("tblSales")
   varValues = recSales.GetRows(2)
   recSales.Close
   intFieldCount = UBound(varValues, 1)
   intRowCount = UBound(varValues, 2)
   For j = 0 To intRowCount
       For i = 0 To intFieldCount
           Debug.Print "Row " & j & ", Field " & i & ": ";
           Debug.Print varValues(i, j)
       Next
   Next

End Sub

</source>
   
  

Retrieving Field Values

   <source lang="vb">

Sub ReadField()

  Dim conn As ADODB.Connection
  Dim rst As ADODB.Recordset
  Set conn = CurrentProject.Connection
  Set rst = New ADODB.Recordset
  rst.Open "Select * from Employees", conn, adOpenStatic
  Do While Not rst.EOF
     Debug.Print rst.Fields("LastName").Value
     rst.MoveNext
  Loop
  rst.Close
  Set rst = Nothing
  conn.Close
  Set conn = Nothing

End Sub

</source>
   
  

Use "!" to reference column in a recordset

   <source lang="vb">

Option Explicit Sub intro()

 Dim conn As New Connection
 Dim rec As New Recordset
 Dim ws As Worksheet
 Dim sql$, i&
 Set ws = ThisWorkbook.Worksheets("intro")
 conn.Open "Provider=microsoft.jet.oledb.4.0;" + _
   "Data Source=" + ThisWorkbook.Path + "\nwind.mdb;"
 sql = "SELECT LastName, FirstName " & _
       "FROM employees ORDER BY LastName, FirstName"
 rec.Open sql, conn
 While Not rec.EOF
   i = i + 1
   ws.[a1].Cells(i) = rec!LastName + ", " + rec!FirstName
   rec.MoveNext
 Wend
 rec.Close: conn.Close

End Sub

</source>