title: Using Excel VBA to SQL delete in MS Access
body: <p>I am using VBA in Excel to remove rows from a MS Access database.
  I am encountering </p>
  
  <blockquote>
    <p>"Error 3704 - Operation is not allowed when the object is open"</p>
  </blockquote>
  
  <p>although when using similar code I was able to add information to the DB.
  When trying to delete, its giving me errors. Please assist!</p>
  
  <pre><code>Sub DeleteOldValues()
  
  '--------------
  'DIM STATEMENTS
  
  Dim strMyPath As String, strDBName As String, strDB As String, strSQL As String, StrQuery As String
  
  'instantiate an ADO object using Dim with the New keyword:
  Dim adoRecSet As New ADODB.Recordset
  Dim connDB As New ADODB.Connection
  
  '--------------
  'THE CONNECTION OBJECT
  
  strDBName = "Test.accdb"
  strMyPath = "Y:"
  strDB = strMyPath &amp; "\" &amp; strDBName
  
   'Connect to a data source:
  connDB.Open ConnectionString:="Provider = Microsoft.ACE.OLEDB.12.0; data source=" &amp; strDB
  
  StrQuery = "DELETE * FROM Table WHERE ProjectName Like '*Project 1*'"
  
  'Performs the actual query
  adoRecSet.Open StrQuery, connDB
  
  '--------------
  'close the objects
  adoRecSet.Close
  connDB.Close
  
  'destroy the variables
  Set adoRecSet = Nothing  &lt;-error occurs at this point
  Set connDB = Nothing
  
  End Sub
  </code></pre>