- 需求目标:将存在于Excel表格中的数据导入已经创建好的Access数据库中
实现方式
方式一:逐行扫描工作表,往数组里面存数据时,从单元格中获取值【rs.Fields(j - 1) = Cells(i, j).Value】
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
方式二:将工作表中的数据读到数组里面,在往数据库中存数据时,从数组里面获取值【rs.Fields(j - 1) = arr(i, j)】
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
不同数据量比较
Sub GetRunTime() Dim i As Long Dim dteStart As Date Dim strTime As String '关闭屏幕刷新 'Application.ScreenUpdating = False dteStart = Timer '---------运行过程主体------- addDate1 strTime = Format((Timer - dteStart), "0.00000") MsgBox "运行过程: " & strTime & "秒" '打开屏幕刷新 'Application.ScreenUpdating = True End Sub
处理11条数据,耗费了0.05469s
处理28339条数据,数组的方式快
处理1048576 条数据 ,显然数组会发生溢出。
总结
一般来讲,当所处理的数据量较少,无论采取哪种方式,差别不大。但是当数据量达到数万条时,数组方式处理显然较快。当数据量达到百万条时,以上两种方式,均会发生“溢出”,此时我们应该考虑数据分割,将百万条数据分成几个部分,分批进行处理。
更多推荐
VBA批量将Excel数据导入Access数据库
发布评论