BOF、EOF 與 Book 屬性範例 此範例使用 BOF 與 EOF 屬性,在使用者嘗試移動超過 Recordset 的第一筆或最後一筆紀錄時,顯示訊息。其使用 Book 屬性讓使用者可以對 Recordset 中的紀錄標示旗標,並於稍後將其傳回。Public Sub BOFX() Dim rstPublishers As ADODB.Recordset Dim strCnn As String Dim strMessage As String Dim intCommand As Integer Dim varBook As Variant ' Open recordset with data from Publishers table. strCnn = "Provider=sqloledb;" & _ "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; " Set rstPublishers = New ADODB.Recordset rstPublishers.CursorType = adOpenStatic ' Use client cursor to enable AbsolutePosition property. rstPublishers.CursorLocation = adUseClient rstPublishers.Open "SELECT pub_id, pub_name FROM publishers " & _ "ORDER BY pub_name", strCnn, , , adCmdText rstPublishers.MoveFirst Do While True ' Display information about current record ' and get user input. strMessage = "Publisher: " & rstPublishers!pub_name & _ vbCr & "(record " & rstPublishers.AbsolutePosition & _ " of " & rstPublishers.RecordCount & ")" & vbCr & vbCr & _ "Enter command:" & vbCr & _ "[1 - next / 2 - previous /" & vbCr & _ "3 - set book / 4 - go to book]" intCommand = Val(InputBox(strMessage)) Select Case intCommand ' Move forward or backward, trapping for BOF ' or EOF. Case 1 rstPublishers.MoveNext If rstPublishers.EOF Then MsgBox "Moving past the last record." & _ vbCr & "Try again." rstPublishers.MoveLast End If Case 2 rstPublishers.MovePrevious If rstPublishers.BOF Then MsgBox "Moving past the first record." & _ vbCr & "Try again." rstPublishers.MoveFirst End If ' Store the book of the current record. Case 3 varBook = rstPublishers.Book ' Go to the record indicated by the stored ' book. Case 4 If IsEmpty(varBook) Then MsgBox "No Book set!" Else rstPublishers.Book = varBook End If Case Else Exit Do End Select Loop rstPublishers.CloseEnd Sub此範例使用 Book 與 Filter 屬性來建立 Recordset 的有限檢視。只能存取書籤陣列所參照的記錄。Public Sub BOFX2()Dim rs As New ADODB.Recordset Dim bmk(10)rs.CursorLocation = adUseClient rs.ActiveConnection = "Provider=sqloledb;" & _ "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=;" rs.Open "select * from authors", , adOpenStatic, adLockBatchOptimistic Debug.Print "Number of records before filtering: ", rs.RecordCountii = 0 While rs.EOF <> True And ii < 11 bmk(ii) = rs.Book ii = ii + 1 rs.Move 2 Wend rs.Filter = bmk Debug.Print "Number of records after filtering: ", rs.RecordCountrs.MoveFirst While rs.EOF <> True Debug.Print rs.AbsolutePosition, rs("au_lname") rs.MoveNext Wend
此範例使用 BOF 與 EOF 屬性,在使用者嘗試移動超過 Recordset 的第一筆或最後一筆紀錄時,顯示訊息。其使用 Book 屬性讓使用者可以對 Recordset 中的紀錄標示旗標,並於稍後將其傳回。Public Sub BOFX() Dim rstPublishers As ADODB.Recordset
Dim strCnn As String
Dim strMessage As String
Dim intCommand As Integer
Dim varBook As Variant ' Open recordset with data from Publishers table.
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
Set rstPublishers = New ADODB.Recordset
rstPublishers.CursorType = adOpenStatic
' Use client cursor to enable AbsolutePosition property.
rstPublishers.CursorLocation = adUseClient
rstPublishers.Open "SELECT pub_id, pub_name FROM publishers " & _
"ORDER BY pub_name", strCnn, , , adCmdText rstPublishers.MoveFirst Do While True
' Display information about current record
' and get user input.
strMessage = "Publisher: " & rstPublishers!pub_name & _
vbCr & "(record " & rstPublishers.AbsolutePosition & _
" of " & rstPublishers.RecordCount & ")" & vbCr & vbCr & _
"Enter command:" & vbCr & _
"[1 - next / 2 - previous /" & vbCr & _
"3 - set book / 4 - go to book]"
intCommand = Val(InputBox(strMessage)) Select Case intCommand
' Move forward or backward, trapping for BOF
' or EOF.
Case 1
rstPublishers.MoveNext
If rstPublishers.EOF Then
MsgBox "Moving past the last record." & _
vbCr & "Try again."
rstPublishers.MoveLast
End If
Case 2
rstPublishers.MovePrevious
If rstPublishers.BOF Then
MsgBox "Moving past the first record." & _
vbCr & "Try again."
rstPublishers.MoveFirst
End If ' Store the book of the current record.
Case 3
varBook = rstPublishers.Book ' Go to the record indicated by the stored
' book.
Case 4
If IsEmpty(varBook) Then
MsgBox "No Book set!"
Else
rstPublishers.Book = varBook
End If Case Else
Exit Do
End Select Loop rstPublishers.CloseEnd Sub此範例使用 Book 與 Filter 屬性來建立 Recordset 的有限檢視。只能存取書籤陣列所參照的記錄。Public Sub BOFX2()Dim rs As New ADODB.Recordset
Dim bmk(10)rs.CursorLocation = adUseClient
rs.ActiveConnection = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=;" rs.Open "select * from authors", , adOpenStatic, adLockBatchOptimistic
Debug.Print "Number of records before filtering: ", rs.RecordCountii = 0
While rs.EOF <> True And ii < 11
bmk(ii) = rs.Book
ii = ii + 1
rs.Move 2
Wend
rs.Filter = bmk
Debug.Print "Number of records after filtering: ", rs.RecordCountrs.MoveFirst
While rs.EOF <> True
Debug.Print rs.AbsolutePosition, rs("au_lname")
rs.MoveNext
Wend
End Sub