VBA/Excel/Access/Word/Access/Recordset Seek

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

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>