в 16:33, 26 мая 2010

2010-05-26T16:33:00Z

Admin: 1 версия

2010-05-26T12:46:45Z

1 версия

Новая страница

==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>

← Предыдущая	Версия 16:33, 26 мая 2010
(нет различий)

VBA/Excel/Access/Word/Access/Recordset Seek - История изменений

в 16:33, 26 мая 2010

Admin: 1 версия