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

Материал из VB Эксперт
Перейти к: навигация, поиск

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
            I = I + 1
    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)
    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)
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
   Set rst = Nothing
   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.Close: conn.Close
End Sub