title: Bulk insert records into Access using Vbscript
body: <p>I'm really pulling my hair out with this one. I've got a vbscript and I'm trying to insert a few hundred thousand records into an Access database.</p>
  
  <p>Obviously, it's really slow if I do them one at a time, so I thought I might be able to bulk insert them with some sort of transaction. So I tried writing this:</p>
  
  <pre><code>set rs = CreateObject("ADODB.recordset")
   rs.Open "table", objConn,, 4
  
  For counter = 1 to 100000
    rs.AddNew
    rs("username") = "Value"
  Next
  
    rs.UpdateBatch
  </code></pre>
  
  <p>(objConn is the database connection).</p>
  
  <p>The problem is I get an error saying: </p>
  
  <blockquote>
    <p>"Number of rows with pending changes exceeded the limit" </p>
  </blockquote>
  
  <p>and I get that when there's more than one pending change.</p>
  
  <p>I guess I haven't set my transaction up correctly, but I'm a bit stuck. Don't suppose someone could point out the error of my ways? Thanks very much.</p>