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

Accessing Data with the Fields Collection and Field Objects

 
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

Get rows from Recordset

 
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

Retrieving Field Values

 
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

Use "!" to reference column in a recordset

 
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