Search code examples
vbams-accessoffice365

Type mismatch when trying to read results of passthrough query

Why do I get a type mismatch error when I try to read the results of this passthrough query? The goal is to read the results of a stored proc. For speed, I'd like to execute it as a passthrough so that it runs faster. I made sure that WOActivityStates exists and when I double click it in the pane on the left it runs just fine.

Private Function GetActivityStates()

   Dim strSQL As String
   Dim con As ADODB.Connection
   Dim rs As New ADODB.Recordset
   Dim intPauseState As Integer
   
   CreatePassThruQuery "WOActivityStates", "exec spGetActivityStates '" & strWO & "' "
   
   Set rs = CurrentDb.OpenRecordset("WOActivityStates") 'ERROR IS HERE

   Do While Not rs.EOF
      'code...
      rs.MoveNext
   Loop
   
ExitLabel:
   Exit Function
   
ErrLabel:
   ErrHandler Err.Number, Err.Description
   Resume ExitLabel

End Function
Solution

  • CurrentDb.OpenRecordset opens a DAO recordset, not ADODB.

    So your code will work with

    Dim rs As DAO.Recordset