title: MS Access (ADODB connection) slow on .Close()
body: <p>Newb Question: I'm working on a PowerShell script that will store it's "working data" in a local MS Access database file using the method outlined <a href="http://www.joseph-streeter.com/?p=914" rel="nofollow">in this blog post.</a></p>
  
  <p>Everything seems to be functioning, but when adding around 500 records to the database file, the subsequent $connection.Close() command takes in excess of 20 seconds to complete.</p>
  
  <p>Is this normal?  Thanks in advance!</p>
  
  <p><strong>EDIT</strong> - Relevant code below:</p>
  
  <pre><code># setup &amp; open the internal database
  $dbCursor = 3
  $dbLock = 3
  $connection = New-Object -com "ADODB.Connection"
  $connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$PSScriptRoot\masterdb.accdb;Persist Security Info=True;"
  $recordSet = New-Object -com "ADODB.Recordset"
  $connection.Open($connectionString)
  $recordSet.Open("SELECT * FROM STUDENTS", $connection, $dbCursor, $dbLock)
  
  # presence check arrays
  $dbArray = @()
  
  # import the CSV file
  $csvFile = Import-Csv -path "$PSScriptRoot\$cognosDownload"
  
  # populate dbArray from database
  $recordSet.MoveFirst()
  do {
      $dbArray += $recordSet.Fields.Item("SID").Value;
      $recordSet.MoveNext()
  } until ($recordSet.EOF -eq $True)
  
  # add new records from CSV to database file
  foreach($csvLine in $csvFile)
  {
      # if CSV record is for a new student, add student to database.
      if ($dbArray -notcontains $csvLine.SID)
      {
          $recordSet.AddNew()
          $recordSet.Fields.Item("SID").value = $csvLine.SID
          $recordSet.Fields.Item("lastName").value = $csvLine.lastName
          $recordSet.Fields.Item("firstName").value = $csvLine.firstName
          $recordSet.Fields.Item("grade").value = $csvLine.grade
          $recordSet.Fields.Item("username").value = Format-Username $csvLine.firstName $csvLine.lastName
          $recordSet.Fields.Item("password").value = New-Password
          $recordSet.Fields.Item("status").value = "A"
          $recordSet.Update()
      }
  }
  
  # remove (rather large) arrays from memory
  Remove-Variable dbArray
  
  #close it all up
  $recordSet.Close()
  $connection.Close()
  </code></pre>