title: Writing Large Amounts of Records to Access using VB
body: <p>I am currently writing some software in visual studio to analyse large amounts of data from an Access database using SQL. I have code to make a new calculated variable but am struggling with the amount of time it takes to write the data back into Access.</p>
  
  <p>I am currently using some vb com code to communicate with my Access Database which is running in 2002/3 comparability mode. The following is my current code which runs a function in a loop to write to the database.</p>
  
  <pre><code>cnnOLEDB = New OleDbConnection
      cnnOLEDB.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &amp; DataDirectoryName &amp; DatabaseFileName
      cnnOLEDB.Open()
  
      'cmdOLEDB = New OleDbCommand
      cmdOLEDB.Connection = cnnOLEDB
  
      ColumnString = "ID_VAR, ID_PAR, TimeValue, strValue, ID_UPL"
      For RecordCounter = 0 To CalcData.GetLength(1) - 1
          Var_ID = Var_ID + 1
          ValueString = Format(Var_ID, "0") &amp; ", " &amp; Format(Parameter, "0") &amp; ", #" &amp; Date2String(CDate(CalcData(0, RecordCounter))) &amp; "#, " &amp; CalcData(CalcData.GetLength(0) - 1, RecordCounter) &amp; ", " &amp; Format(AsUpload, "0")
          If DatabaseConnectionInsert("INSERT INTO " &amp; TableName &amp; " (" &amp; ColumnString &amp; ") VALUES (" &amp; ValueString &amp; ")", "Non-Query") = "Error" Then GoTo Close
      Next
  
      cnnOLEDB.Close()
  </code></pre>
  
  <p>Here is the Function:</p>
  
  <pre><code>Public Function DatabaseConnectioninsert(ByVal Query As String, ByVal Task As String) As String
          'On Error GoTo Err
  
          'If cnnOLEDB.State = ConnectionState.Open Then cnnOLEDB.Close()
          cmdOLEDB.CommandText = Query
  
          Select Case Task
              Case "Read Recordset"
                  rdrOLEDB = cmdOLEDB.ExecuteReader()
                  DatabaseConnectioninsert = "Read Recordset"
              Case "Read Scalar"
                  DatabaseConnectioninsert = cmdOLEDB.ExecuteScalar
              Case "Non-Query"
                  cmdOLEDB.ExecuteNonQuery()
                  DatabaseConnectioninsert = "Non-Query"
          End Select
  
          Exit Function
  Err:
          MsgBox("Database connection error.")
          DatabaseConnectioninsert = "Error"
  
  
      End Function
  </code></pre>
  
  <p>I am currently trying to insert ~4500 records into the Access Database for each Parameter which takes ~3minutes. However when the project goes live it will have to deal with over 100000 records per Parameter so it is no where near fast enough.</p>
  
  <p>To solve this issue I am thinking of either updating my code to .net or creating a record set, so I can move all of the data in Access at once. Can anyone give me some advice as to which will have the greatest impact to improving the speed of the inserts. I am running visual studio 2005 and Access 2007, updating the database to 2007 rather than compatibility mode is possible but not ideal , however my current code can't access it.</p>
  
  <p>Thank you for your help</p>
  
  <p>Josh</p>