title: Automation error (vba)
body: <p>I have an issue with Automation error when I try to run my code in VBA. My ADODB connection is declared as public in a module so that I could use the same connection for several SQL statements.</p>
  
  <p>My 1st SQL statement and recordset('rs1') works fine but my 2nd recordset('mainRS') gave me an Automation error and I'm not sure which part went wrong.</p>
  
  <p>Can anyone help, please? Thank you!!</p>
  
  <pre><code>Public Sub MainConDef(DBPath As String)                  'DBPath as global
      Set dsConnect = CreateObject("ADODB.Connection")     'dsConnect, cmdCommand, rs declared as global
      Set cmdCommand = CreateObject("ADODB.Command")
      Set rs1 = CreateObject("ADODB.Recordset")
  
      dsConnect.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" &amp; DBPath &amp; _
        ";JET OLEDB:Database Password=xxxxxxxxxxxx" &amp; ";Mode=Read|Write"
  
      dsConnect.CursorLocation = adUseClient
  
      dsConnect.Open
  
      With cmdCommand
          .ActiveConnection = dsConnect
          .CommandText = "SELECT * FROM tblManhours;"
          .CommandType = adCmdText
      End With
  
      With rs1
          .CursorType = adOpenStatic
          .CursorLocation = adUseClient
          .LockType = adLockOptimistic
          .Open cmdCommand
      End With
  End Sub
  </code></pre>
  
  <hr>
  
  <pre><code>Private Sub Workbook_Open()
      Dim i, j, k As Integer
  
      DBPath = "D:\DbManHours.accdb"
      MainConDef (DBPath)
  
      Sheet1.Range("A2").CopyFromRecordset rs1
  
      OverallData (DBPath)
  
      Sheets("Sheet2").Activate
      Sheet2.Range("B2").CopyFromRecordset mainRS
  
      mainRS.Close
  
  End Sub
  </code></pre>
  
  <hr>
  
  <pre><code>Public Sub OverallData(DBPath As String)
      Dim cmd1 As Object
  
      Set cmd1 = CreateObject("ADODB.Command")
      Set mainRS = CreateObject("ADODB.Recordset")       'mainRS declared as global
  
      dsConnect.CursorLocation = adUseClient
  
      If Not (dsConnect Is Nothing) Then
          If (dsConnect.State = adStateClosed) Then dsConnect.Open
      End If
  
      cmd1.ActiveConnection = dsConnect
      cmd1.CommandText = "SELECT * FROM tblManhours" &amp; _
          "WHERE tblManhours[Dept No]=OPS" &amp; _
          "AND tblManhours[Dept No]=FIN" &amp; _
          "AND (tblManhours[Type]) NOT IN ('F', 'N')"
  
     Set mainRS = cmd1.Execute    '&lt;-- Automation error occurs here
  
     Sheet2.Range("B2").CopyFromRecordset mainRS
  
     mainRS.Close
  End Sub
  </code></pre>