使用VBA访问ACCESS，Excel文件的方法

Access中用VBA得到和修改数据库中表数据：

ADO:

Dim rstType As ADODB.Recordset
Dim strSQL as String

Set rstType = New ADODB.Recordset
strSQL =”Select F1,F2 From Table1”

rstType.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdText

Do While Not rstType.EOF
         rstType!F1=xxx
         rstType.MoveNext
Loop

 DAO

Dim dbTmp As Database
Dim rstType As Recordset

Set dbTmp = CurrentDb
Set rstType = dbTmp.OpenRecordset("Select F1,F2 >From Table1", dbOpenDynaset)

Do While Not rstType.EOF

rstType.Edit
rstType.Fields("F1").Value = xxx
rstType.Update
rstType.MoveNext

Loop

  读取Excel数据

ADO

Dim cnt As ADODB.Connection
Dim rsT As ADODB.Recordset
Dim sConnStrXls As String 

Set cnt = New ADODB.Connection
sConnStrXls = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & strFileName & ";Extended Properties=Excel 8.0;"
cnt.Open sConnStrXls 

Set rsT = New ADODB.Recordset

With rsT

    Set .ActiveConnection = cnt
    .Source = "Select * from [SheetName$A1:W6000]"
    .CursorLocation = adUseClient
    .Open

End With

 Do While Not rsD.EOF
          ‘访问某一字段
     Xxx=rsT.Fields(strfldMD).Value
Loop

 DAO

Dim dbTmp As DAO.Database, rsD As DAO.Recordset
Set dbTmp = OpenDatabase(strFileName, False, True, "Excel 8.0;HDR=Yes;IMEX=1;")
Set rsD = dbTmp.OpenRecordset(“Select * from [Sheet1$];”)

Do While Not rsD.EOF
         ‘访问某一个字段的值
         Xxx= rstD.fields(“Project Name”)  or xxx=rstD.fields(2)
Loop