VBA/Excel/Access/Word/Access/Recordset Seek — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
|
Версия 19:33, 26 мая 2010
Finding Records Using the Seek Method (Seek constants)
<source lang="vb">
Constant Value Description adSeekFirstEQ 1 Seeks the first key equal to KeyValues. adSeekLastEQ 2 Seeks the last key equal to KeyValues. adSeekAfterEQ 4 Seeks either a key equal to KeyValues or just after where that match would have occurred. adSeekAfter 8 Seeks a key just after where a match with KeyValues would have occurred. adSeekBeforeEQ 16 Seeks either a key equal to KeyValues or just before where that match would have occurred. adSeekBefore 32 Seeks a key just before where a match with KeyValues would have occurred.
Sub Find_WithSeek()
Dim conn As ADODB.Connection Dim rst As ADODB.Recordset Set conn = New ADODB.Connection conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentProject.Path & _ "\mydb.mdb" Set rst = New ADODB.Recordset With rst .Index = "Region" .Open "Customers", conn, adOpenKeyset, adLockOptimistic, _ adCmdTableDirect MsgBox rst.Supports(adSeek) .Seek "SP", adSeekFirstEQ End With If Not rst.EOF Then Debug.Print rst.Fields("CompanyName").Value End If rst.Close Set rst = Nothing conn.Close Set conn = Nothing
End Sub
</source>
Use Do while to loop through until EOF
<source lang="vb">
Private Sub RunningSumDAO()
Dim db As Database Dim rs As Recordset Dim lRunningSum As Long DBEngine.SetOption dbMaxLocksPerFile, 1000000 Set db = CurrentDb lRunningSum = 0 Set rs = db.OpenRecordset("SELECT * FROM Employees ORDER BY FirstName") Do While Not rs.EOF rs.Edit rs!RunningSum = lRunningSum rs.Update lRunningSum = lRunningSum + rs!Duration rs.MoveNext Loop rs.Close
End Sub
</source>
Use seek method in Recordset
<source lang="vb">
Sub UsingSeek()
Dim rs As ADODB.Recordset Dim conn As ADODB.Connection Dim strSQL As String Set rs = New ADODB.Recordset Set conn = CurrentProject.Connection With rs .Open Source:="Customers", ActiveConnection:=conn, _ CursorType:=adOpenKeyset, LockType:=adLockOptimistic, _ Options:=adCmdTableDirect .Index = "CompanyName" .Seek ("T") End With Debug.Print rs!CompanyName & " " & rs!ContactTitle & " " & rs!ContactName
End Sub
</source>