VB数据库连接之四（控件连接）

1.添加Micrsoft DataGrid Control 6.0（OLEDB）控件

2.设计窗体

   

3.代码实现

  Option Explicit
Dim objCn As New Connection, objRs As New Recordset, intPage As Integer

Public Sub ShowData(ByVal intPage As Integer)
    '实现分页显示记录功能
    Dim intPageCount As Integer, intRecord As Integer
    '创建一个局部Recordset对象保存objRs当前记录
    Dim objDataSource As New Recordset
    For intRecord = 0 To objRs.Fields.Count - 1
        objDataSource.Fields.Append objRs.Fields(intRecord).Name, adVarChar, _
                                    objRs.Fields(intRecord).DefinedSize
    Next
    objDataSource.Open                            '打开局部Recordset对象
    objRs.PageSize = Val(txtPageSize)             '重新设置objRs每页显示的记录条数
    objRs.AbsolutePage = intPage                  '重新设置objRs的当前记录页
    For intRecord = 1 To objRs.PageSize           '将当前记录页中数据写入到objDataSource中
        objDataSource.AddNew
        objDataSource!用户名 = objRs!用户名
        objDataSource!口令 = objRs!口令
        objDataSource!身份 = objRs!身份
        objRs.MoveNext
        If objRs.EOF Then Exit For
    Next
    Set DataGrid1.DataSource = objDataSource        '设置为数据网格的数据源
    txtPageMsg = intPage & "/" & objRs.PageCount    '刷新当前记录页信息
End Sub


Private Sub cmdNext_Click()
    If intPage <> objRs.PageCount Then
        intPage = intPage + 1
        ShowData (intPage)
    End If
End Sub

Private Sub cmdPre_Click()
    If intPage <> 1 Then
        intPage = intPage - 1
        ShowData (intPage)
    End If
End Sub

Private Sub Form_Load()
    Dim strSQL As String, strCn As String
    txtPageSize = "5"
    intPage = 1
    '建立数据库连接
    strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;" & _
            "Data Source=" & App.Path & "\db1.mdb"
    objCn.ConnectionString = strCn
    objCn.Open
    With objRs                             '创建客户端的记录集
        .CursorLocation = adUseClient      '指定使用客户端光标
        .CursorType = adOpenStatic         '指定使用静态光标
        .Open "系统用户", objCn, adOpenStatic, adLockReadOnly
    End With
    ShowData (intPage)                     '显示当前记录页
End Sub

Private Sub Form_Unload(Cancel As Integer)
    objCn.Close            '关闭数据库连接
    Set objRs = Nothing    '释放recordset对象
    Set objCn = Nothing    '释放connection对象
End Sub

Private Sub txtPageSize_KeyPress(KeyAscii As Integer)
    Dim bOK As Boolean
    '如果按的是回车键，并且记录页大小不是空值，则刷新当前记录
    If KeyAscii = vbKeyReturn And Trim(txtPageSize) <> "" Then
        intPage = 1
        ShowData (intPage)
    ElseIf KeyAscii >= Asc("0") And KeyAscii <= Asc("9") Then
        '检验当前设置的记录页大小是否在有效范围之内
        bOK = Val(txtPageSize & Chr(KeyAscii)) < 1 Or Val(txtPageSize & Chr(KeyAscii)) > 10
        If bOK Then
            MsgBox "每页显示记录范围为[1,10]"
            KeyAscii = 0    '设置keyascii为0，取消等钱输入数字
        End If
    End If
End Sub