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.
I've read elsewhere that the JET engine doesn't support UpdateBatch. So maybe there's a better way to do it.
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
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?
