VBA/Excel/Access/Word/Access/Recordset Field — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
|
Текущая версия на 15:46, 26 мая 2010
Содержание
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>