今天经过试验,总结了一条向ACCESS大批量插入数据时比较快捷的方法.
先前VC程序中使用connection执行insert语句向ACCESS中插入数据,效率奇慢无比.今天测试结果表明:
同等数据量时, rs.addnew比connection.execute(insert)要高N倍.
另,建议使用事务机制处理数据.
将connection.BeginTrans和connection.CommitTrans配合rs.addnew使用,
VB+ACCESS试验数据表明,一百万条新数据插入,仅需要一分多钟,使用connection.execute更新十万条数据时需要三五分钟.不使用事务应该会更久(根据先前经验,未测试).
需要注意的是,使用rs.addnew时,rs的LockType不能使用adLockBatchOptimistic或adLockReadOnly,否则会报错
=========================================================
测试代码如下:
- Private Sub Command1_Click()
- Dim cnn1 As ADODB.Connection
- Dim rstTitles As ADODB.Recordset
- Dim strCnn As String
- Dim strTitle As String
- Dim strMessage As String
- ' 打开连接。
- strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/Documents and Settings/桌面/新建 Microsoft Office Access 应用程序.mdb;Persist Security Info=False"
- Set cnn1 = New ADODB.Connection
- cnn1.Open strCnn
- Dim i As Long
- Dim t1 As String, t2 As String
- t1 = Time$()
- cnn1.BeginTrans
- Set rstTitles = New ADODB.Recordset
- rstTitles.LockType = adLockPessimistic '不能使用adLockBatchOptimistic或adLockReadOnly
- rstTitles.CursorType = adOpenDynamic
- rstTitles.Open "tab", cnn1
- For i = 1 To 10
- rstTitles.AddNew
- rstTitles("a") = i
- rstTitles("b") = i
- rstTitles.Update
- Next i
- cnn1.CommitTrans
- rstTitles.Close
- cnn1.Close
- t2 = Time$()
- MsgBox t1 & " " & t2
- End Sub