VB6操作ACCESS文件

VB6操作ACCESS文件

Option Explicit

Const Dict_DB = "adatabase.mdb"

'先要引用  Microsoft ActiveX Data Objects 2.8 Library

Public cnn As ADODB.Connection
Public rst As ADODB.Recordset, rst2 As ADODB.Recordset
Public cmd As ADODB.Command


Private Sub Form_Load()

'创建对数据库的连接
    Dim mdbA As String

    Set cnn = New ADODB.Connection
    mdbA = App.Path & "\" & Dict_DB
    With cnn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Open mdbA
    End With

    '创建记录集   adCmdTable
    Dim sheetName As String

    Set rst = New ADODB.Recordset
    sheetName = "texts"
    rst.CursorLocation = adUseClient    '或者adUseServer
    rst.Open Source:=sheetName, ActiveConnection:=cnn, CursorType:=adOpenDynamic, LockType:=adLockOptimistic, Options:=adCmdTable    '类型adCmdTable

    '记录集的一些数据
    Dim i%, n As Long

    Debug.Print "列的数目：", rst.Fields.Count
    Debug.Print "行的数目：", rst.RecordCount

    For i = 0 To rst.Fields.Count - 1
        Debug.Print "第" & i & "列名称：", rst.Fields(i).Name
    Next

    '关闭记录集
    rst.Close

    '用SQL的方法创建记录集   adCmdText
    Dim sSQL$
    sSQL = "SELECT count(*) FROM texts "
    rst.Open Source:=sSQL, ActiveConnection:=cnn, CursorType:=adOpenDynamic, LockType:=adLockOptimistic, Options:=adCmdText    'adCmdText

    Debug.Print "Sql count方法，记录总数：", rst(0)
    rst.Close

    sSQL = "SELECT * FROM texts "

    rst.Open Source:=sSQL, ActiveConnection:=cnn, CursorType:=adOpenDynamic, LockType:=adLockOptimistic, Options:=adCmdText    'adCmdText

    Debug.Print "Sql 方法，列的数目：", rst.Fields.Count
    Debug.Print "Sql 方法，行的数目：", rst.RecordCount

    '浏览数据
    rst.MoveFirst
    Debug.Print "第" & rst.AbsolutePosition & "条：", rst.Fields(0)
    While Not rst.EOF
        rst.MoveNext
    Wend
    rst.MovePrevious
    Debug.Print "第" & rst.AbsolutePosition & "条：", rst.Fields(0)

    '添加新数据
    rst.AddNew
    rst.Fields(3) = "This is new record."
    rst.Update

    rst.MoveLast
    Debug.Print rst.RecordCount, rst.Fields(3)

    '修改数据
    rst.Fields(3) = "This is new record---edited."
    Debug.Print rst.RecordCount, rst.Fields(3)

    '删除数据
    rst.Delete
    rst.Update
    rst.MoveLast
    Debug.Print rst.RecordCount, rst.Fields(3)

    rst.Close
    
    '执行sql的2个方法，注意：得到的数据集，都不能使用RecordCount和AbsolutePosition等属性
    
    sSQL = "SELECT * FROM texts"
    '1，ADODB.Connection 的成员 Function Execute(CommandText As String, [RecordsAffected], [Options As Long = -1]) As Recordset
    '但是用rs的效果没有直接connection.execute的效率来的高?connection.execute一般用来执行update insert into delete 语句不返回记录集，不返回结果的。

    Set rst = cnn.Execute(sSQL)

    Debug.Print rst.Fields.Count, rst.RecordCount
    rst.MoveFirst
    Debug.Print rst.Fields(0), Left(rst.Fields(3), 20)
    rst.Move 12
    Debug.Print rst.Fields(0), Left(rst.Fields(3), 20)

    rst.Close

    '2，ADODB.Command 的成员 Function Execute([RecordsAffected], [Parameters], [Options As Long = -1]) As Recordset

    Set cmd = New ADODB.Command
    cmd.ActiveConnection = cnn
    cmd.CommandType = adCmdText
    cmd.CommandText = sSQL
    Set rst = cmd.Execute
    
    Debug.Print rst.Fields.Count, rst.RecordCount
    rst.MoveFirst
    Debug.Print rst.Fields(0), Left(rst.Fields(3), 20)
    rst.Move 12
    Debug.Print rst.Fields(0), Left(rst.Fields(3), 20)
    
    rst.Close
    
    Set cmd = Nothing
    
    
    cnn.Close
    Set cnn = Nothing
End Sub