1,216
社区成员
发帖
与我相关
我的任务
分享
Count 属性范例
该范例使用雇员数据库中的两个集合说明 Count 属性。该属性获得每个集合中的对象数并对枚举这些集合的循环设置上限。另一种不通过使用 Count 属性来枚举这些集合的方法是使用 For Each...Next 语句。
Public Sub CountX()
Dim rstEmployees As ADODB.Recordset
Dim strCnn As String
Dim intloop As Integer
' 使用雇员表中的数据打开记录集。
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
Set rstEmployees = New ADODB.Recordset
rstEmployees.Open "employee", strCnn, , , adCmdTable
' 打印有关字段集合的信息。
Debug.Print rstEmployees.Fields.Count & _
" Fields in Employee"
For intloop = 0 To rstEmployees.Fields.Count - 1
Debug.Print " " & rstEmployees.Fields(intloop).Name
Next intloop
' 打印属性集合的信息。
Debug.Print rstEmployees.Properties.Count & _
" Properties in Employee"
For intloop = 0 To rstEmployees.Properties.Count - 1
Debug.Print " " & rstEmployees.Properties(intloop).Name
Next intloop
rstEmployees.Close
End Sub
BOF、EOF 和 Bookmark 属性范例
该范例使用 BOF 和 EOF 属性,在用户试图移过 Recordset 的第一个和最后一个记录时显示一条信息。它通过 Bookmark 属性使用户对 Recordset 中的记录进行标记,稍后再返回给它。
Public Sub BOFX()
Dim rstPublishers As ADODB.Recordset
Dim strCnn As String
Dim strMessage As String
Dim intCommand As Integer
Dim varBookmark As Variant
' 使用来自出版商表的数据打开记录集。
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
Set rstPublishers = New ADODB.Recordset
rstPublishers.CursorType = adOpenStatic
' 使用客户端游标启用 AbsolutePosition 属性。
rstPublishers.CursorLocation = adUseClient
rstPublishers.Open "SELECT pub_id, pub_name FROM publishers " & _
"ORDER BY pub_name", strCnn, , , adCmdText
rstPublishers.MoveFirst
Do While True
' 显示关于当前记录的信息并让用户输入。
strMessage = "Publisher: " & rstPublishers!pub_name & _
vbCr & "(record " & rstPublishers.AbsolutePosition & _
" of " & rstPublishers.RecordCount & ")" & vbCr & vbCr & _
"Enter command:" & vbCr & _
"[1 - next / 2 - previous /" & vbCr & _
"3 - set bookmark / 4 - go to bookmark]"
intCommand = Val(InputBox(strMessage))
Select Case intCommand
' 向前或向后移动,捕获 BOF 或 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
' 保存当前记录的书签。
Case 3
varBookmark = rstPublishers.Bookmark
' 转到由存储的书签所指示的记录。
Case 4
If IsEmpty(varBookmark) Then
MsgBox "No Bookmark set!"
Else
rstPublishers.Bookmark = varBookmark
End If
Case Else
Exit Do
End Select
Loop
rstPublishers.Close
End Sub
该范例使用 Bookmark 和 Filter 属性创建记录集的限定视图,将只允许访问书签数组所引用的记录。
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.RecordCount
ii = 0
While rs.EOF <> True And ii < 11
bmk(ii) = rs.Bookmark
ii = ii + 1
rs.Move 2
Wend
rs.Filter = bmk
Debug.Print "Number of records after filtering: ", rs.RecordCount
rs.MoveFirst
While rs.EOF <> True
Debug.Print rs.AbsolutePosition, rs("au_lname")
rs.MoveNext
Wend
End Sub