title: Excel VBA and ADO - Error 3704 Operation is not allowed when object is closed
body: <p>Im attempting to add data stored on an excel spreadsheet to an access database. But, before each row is added, i want to check if that row of data is already present in the database, and if so, skip the row and move to the next one.Trying to avoid duplication of data..</p>
  
  <p>From a completely blank database, my code enters the first two rows of data and then throws up the error. </p>
  
  <blockquote>
    <p>Error 3704 Operation is not allowed when object is closed</p>
  </blockquote>
  
  <p>When I stop and then run the code again, the next two rows of data are entered and the error pops up again! and so on...</p>
  
  <p>Im very new to ADO and can't figure out what the issue is, so all assistance is appreciated.</p>
  
  <p>Here is the full block of code:</p>
  
  <pre class="lang-vb prettyprint-override"><code>Function GetDb() As ADODB.Connection
      Set GetDb = New ADODB.Connection
      GetDb.Open "Provider=Microsoft.ACE.OLEDB.12.0;DATA Source=" &amp; GetPath &amp; ";"
  End Function
  </code></pre>
  
  <pre class="lang-vb prettyprint-override"><code>Function GetPath() As String
      GetPath = ThisWorkbook.Path &amp; "\db.accdb"
  End Function
  </code></pre>
  
  <pre class="lang-vb prettyprint-override"><code>Sub AddToDBSTACKFB()
    'searchfirst try
  
    Dim adoConn As ADODB.Connection
    Dim adoComm As New ADODB.Command
    Dim adoRS As Recordset
  
    Dim RecordRow As Long, Lastrow As Long
    Dim OrderID As String, Site As String, ProductName As String
    Dim ShipTo As String, ShipVia As String, Quantity As Long, Email As String
  
    Set adoConn = GetDb
  
    Lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
  
    With adoComm
      Set .ActiveConnection = adoConn
  
      .CommandText = "select [OrderID],[Site],[Product Name],[Ship To],[Ship Via],[Quantity],[Email] " &amp; _
                     "FROM DATA " &amp; _
                     "where [OrderID] =? and [Site] = ? and [Product Name] = ? and [Ship To] = ? and [Ship Via] = ? and [Quantity] = ? and [Email]= ?"
  
      .Parameters.Append adoComm.CreateParameter(Name:="OrderID", Type:=adVarChar, Size:=255)
      .Parameters.Append adoComm.CreateParameter(Name:="Site", Type:=adVarChar, Size:=255)
      .Parameters.Append adoComm.CreateParameter(Name:="Product Name", Type:=adVarChar, Size:=255)
      .Parameters.Append adoComm.CreateParameter(Name:="Ship To", Type:=adVarChar, Size:=255)
      .Parameters.Append adoComm.CreateParameter(Name:="Ship via", Type:=adVarChar, Size:=255)
      .Parameters.Append adoComm.CreateParameter(Name:="Quantity", Type:=adDouble)
      .Parameters.Append adoComm.CreateParameter(Name:="Email", Type:=adVarChar, Size:=255)
  
  
      'Loop through all the records on the sheet one row at a time
      For RecordRow = 2 To Lastrow
        OrderID = Sheet1.Cells(RecordRow, 1).Value
        Site = Sheet1.Cells(RecordRow, 2).Value
        ProductName = Sheet1.Cells(RecordRow, 3).Value
        ShipTo = Sheet1.Cells(RecordRow, 4).Value
        ShipVia = Sheet1.Cells(RecordRow, 5).Value
        Quantity = Sheet1.Cells(RecordRow, 6).Value
        Email = Sheet1.Cells(RecordRow, 7).Value
  
        .Parameters(0).Value = OrderID
        .Parameters(1).Value = Site
        .Parameters(2).Value = ProductName
        .Parameters(3).Value = ShipTo
        .Parameters(4).Value = ShipVia
        .Parameters(5).Value = Quantity
        .Parameters(6).Value = Email
  
        'First, check if the record is already in the database
        'if its not, then enter that row of data into the database
        Set adoRS = adoComm.Execute
  
        If (adoRS.EOF And adoRS.BOF) Then '&lt;&lt; ERROR ON THIS LINE EACH TIME
          adoComm.CommandText = "INSERT INTO DATA([OrderID],[Site],[Product Name],[Ship To],[Ship Via],[Quantity],[Email]) " &amp; _
                             "VALUES(?,?,?,?,?,?,?)"
          adoComm.Execute
          Set adoRS = Nothing
        Else
          Set adoRS = Nothing
        End If
  
      Next RecordRow
  
    End With
  
    adoConn.Close
  End Sub
  </code></pre>