vba-将excel中数据插入数据库

Sub addDate1()
    Dim i As Integer, j As Integer, n As Integer
    Dim sql As String
    Dim con As New ADODB.Connection
   
    With con
        .Provider = "microsoft.ace.oledb.12.0"
        .ConnectionString = ThisWorkbook.Path & "\test.accdb"
        .Open
    End With
    
    Set rs = con.OpenSchema(adSchemaTables)
    n = Range("A1").End(xlDown).Row
    
    For i = 1 To n
       Set rs = New ADODB.Recordset
       sql = "select * from m_check"
       rs.Open sql, con, adOpenKeyset, adLockOptimistic
 
        rs.AddNew
        For j = 1 To rs.Fields.Count
            rs.Fields(j - 1) = Cells(i, j).Value
        Next j
        rs.Update
    Next i
    MsgBox "success" + Str(n)
    rs.Close
    con.Close   '关闭连接
    Set con = Nothing '释放变量
    Set rs = Nothing
 
End Sub

方法二，转换成数组

Sub addDate()
    Dim arr, i As Integer, j As Integer
    Dim sql As String
    arr = Range("A2").CurrentRegion
    
    Dim con As New ADODB.Connection
    Dim rs As New ADODB.Recordset
 
    With con
        .Provider = "microsoft.ace.oledb.12.0"
        .ConnectionString = ThisWorkbook.Path & "\test.accdb"
        .Open
    End With
 
    sql = "select * from m_check"
    rs.Open sql, con, adOpenKeyset, adLockOptimistic
    
    For i = 2 To UBound(arr)
        rs.AddNew
        For j = 1 To rs.Fields.Count
            rs.Fields(j - 1) = arr(i, j)
        Next j
        rs.Update
    Next i
    MsgBox "success"
     
'    sql = "delete * from m_check"
'    con.Execute (sql)  
    rs.Close
    con.Close   '关闭连接
    Set con = Nothing '释放变量
    Set rs = Nothing
End Sub