title: MS Access: Why is ADODB.Recordset.BatchUpdate so much slower than Application.ImportXML?
body: <p>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.</p>
  
  <p>I've read elsewhere that the JET engine doesn't support UpdateBatch.  So maybe there's a better way to do it.</p>
  
  <p>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.</p>
  
  <p>Surely there's an efficient way to insert data into Access that doesn't require writing a temp file?</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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?</p>