VB 把excel中内容添加到access中

除了access可以从excel直接导入（有点不符合本人观点）外，

可以用VB来操作。

两个注意点：

1、先查询记录在access是否存在，存在了就不用添加。

2、打开时，用键集游标adOpenKeyset，不然那个查询的记录集的个数是-1，为判断是否存在记录造成困惑。

Private Sub Command1_Click()
    Dim cn    As New ADODB.Connection
    Dim rs    As New ADODB.Recordset
    Dim i     As Integer, j As Integer
    Dim xl    As New Excel.Application
    Dim book  As Excel.Workbook
    Dim sheet As Excel.Worksheet

    Set book = xl.Workbooks.Open("D:\etoa\2.xls") '打开excel,
    Set sheet = book.Worksheets(1)
    
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\eToa\Station.mdb;Persist Security Info=False"

    For i = 1 To 282
        '查询
        rs.Open "select * from station where StationNumber='" & Trim(sheet.Cells(i, 1)) & "'", cn, adOpenKeyset, adLockOptimistic
        '是否有记录，有记录就不添加
        If rs.RecordCount = 0 Then '没有记录，添加
            rs.AddNew
            rs.Fields(1) = sheet.Cells(i, 1) '注意fields索引是以0开始，excel是以1开始
            rs.Fields(2) = sheet.Cells(i, 2)
            rs.Fields(3) = sheet.Cells(i, 3)
            rs.Fields(4) = sheet.Cells(i, 4)
            rs.Fields(5) = sheet.Cells(i, 5)
            rs.Fields(6) = sheet.Cells(i, 6)
            rs.Fields(7) = sheet.Cells(i, 7)
            rs.Update
        Else
            List1.AddItem sheet.Cells(i, 1) '这是没有被添加的记录
        End If
        rs.Close
    Next i
    MsgBox "导入完成"
    book.Close '退出
    xl.Quit
    cn.Close
End Sub