我试图运行下面的code插入一大堆的记录（从一个奇怪的文件格式的文件）到我从VBA Access 2003数据库。经过很多很多的实验，这code是最快的我已经能够拿出：它10000条记录在大约15秒在我的机器上。至少有14.5那些秒（即几乎所有的时间）是中的UpdateBatch单一的电话。

I'm trying to run the code below to insert a whole lot of records (from a file with a weird file format) into my Access 2003 database from VBA. After many, many experiments, this code is the fastest I've been able to come up with: it does 10000 records in about 15 seconds on my machine. At least 14.5 of those seconds (ie. almost all the time) is in the single call to UpdateBatch.

我在其他地方读到，JET引擎不支持的UpdateBatch。因此，也许有更好的方法来做到这一点。

I've read elsewhere that the JET engine doesn't support UpdateBatch. So maybe there's a better way to do it.

现在，我只是觉得JET引擎是平原缓慢的，但不能吧。产生的暴躁表下方的code后，我右键点击它，拿起出口，并将其保存为XML。然后，我右键点击，拿起进口，并重新加载的XML。总时间导入XML文件？超过一秒，即少。至少15倍快。

Now, I would just think the JET engine is plain slow, but that can't be it. After generating the 'testy' table with the code below, I right clicked it, picked Export, and saved it as XML. Then I right clicked, picked Import, and reloaded the XML. Total time to import the XML file? Less than one second, ie. at least 15x faster.

当然，有将数据插入访问，不需要写一个临时文件的有效方式？

Surely there's an efficient way to insert data into Access that doesn't require writing a temp file?

Sub TestBatchUpdate()
    CurrentDb.Execute "create table testy (x int, y int)"

    Dim rs As New ADODB.Recordset
    rs.CursorLocation = adUseServer
    rs.Open "testy", CurrentProject.AccessConnection, _
        adOpenStatic, adLockBatchOptimistic, adCmdTableDirect

    Dim n, v
    n = Array(0, 1)
    v = Array(50, 55)

    Debug.Print "starting loop", Time
    For i = 1 To 10000
        rs.AddNew n, v
    Next i
    Debug.Print "done loop", Time

    rs.UpdateBatch
    Debug.Print "done update", Time

    CurrentDb.Execute "drop table testy"
End Sub

我愿意诉诸C / C ++，如果有一些API，它可以让我做快速插入的方式。但我似乎无法找到它。这不能说Application.ImportXML使用无证的API，可以吗？

I would be willing to resort to C/C++ if there's some API that would let me do fast inserts that way. But I can't seem to find it. It can't be that Application.ImportXML is using undocumented APIs, can it?

推荐答案

除非你必须这样做与ADO，尽量DAO代替。这是时代对我的笔记本电脑，你的程序和DAO版本：

Unless you must do this with ADO, try DAO instead. Here are the times on my laptop with your procedure and a DAO version:

ADO:
starting loop 9:51:59 PM
done loop     9:52:00 PM
done update   9:52:54 PM

DAO:
starting loop 9:58:29 PM
done loop     9:58:31 PM
done update   9:58:31 PM

这是我用过的DAO版本。

This is the DAO version I used.

Sub TestBatchUpdateDAO()

    CurrentDb.Execute "create table testy (x int, y int)"

    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("testy", dbOpenTable, dbAppendOnly)
    Dim i As Long

    Debug.Print "starting loop", Time
    For i = 1 To 10000
        rs.AddNew
        rs!x = 50
        rs!y = 55
        rs.Update
    Next i
    Debug.Print "done loop", Time

    'rs.UpdateBatch '
    Debug.Print "done update", Time

    rs.Close
    Set rs = Nothing
    CurrentDb.Execute "drop table testy"
End Sub