學習筆記 ADO數據庫訪問技術

3.1.1ADO的對象（Connection对象，Command对象，Recordset对象）
3.1.2了解記錄集
3.1.3記錄集的主要屬性方法（Open,Close,Update,BOF,EOF,MoveFirst,MoveaLast,MoveNext,MovePrevious,AddNew,Delete,LockType,Fields,RecordCount）
myres.Open "資料表" myconnect, adOpenDynamic, adLockOptimistic,adCmdTable
myres.Close
3.2在Excel中引用ADO对象

Public Sub excacc()
    Dim mydata As String, mytable As String, n As Integer
    
    mydata = "C:\Users\Administrator\Documents\Database1.accdb"
    mytable = "表1"
    
    Dim myconnect As ADODB.Connection
    Set myconnect = New ADODB.Connection
    myconnect.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;data source=" & mydata
    myconnect.Open
    
    Dim myres As ADODB.Recordset
    Set myres = New ADODB.Recordset
    myres.Open mytable, myconnect, adOpenKeyset, adLockOptimistic
    
    n = myres.RecordCount
    MsgBox "數據庫：" & mydata & "表：" & mytable & "共有" & n & "條記錄"
    For i = 0 To n        
        MsgBox myres.Fields("字段1") & vbCrLf & myres.Fields("字段3")    
    Next i
    myres.Close
    myconnect.Close
    Set myres = Nothing
    Set myconnect = Nothing
    
End Sub