title: ms access - return records from sql Stored procedure
body: <p>I have an access 2010 and I want to call a sql stored proc on a sql 2008 server and return the results to a recordset or bind directly to a report.</p>
  
  <p>The method below returns the error "feture not available in ADP" or something like that - It works with a Form but not with a Report.</p>
  
  <p>How do I go about doing this?</p>
  
  <pre><code>Dim cn As New ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim cm As New ADODB.Command
  
  'Use the ADO connection that Access uses
  Set cn = CurrentProject.AccessConnection
  With cn
      .Provider = "Microsoft.Access.OLEDB.10.0"
      .Properties("Data Provider").Value = "SQLOLEDB"
      .Properties("Data Source").Value = "dsidsw923"
      .Properties("Integrated Security").Value = "SSPI"
      .Properties("Initial Catalog").Value = "Promotions_Dev_DSI"
      .Open
  End With
  
  'Create an instance of the ADO Recordset class, and
  'set its properties
  Set rs = New ADODB.Recordset
  
  With cm
      .ActiveConnection = cn
      .CommandText = "spCheckProdWkConsist"
      .CommandType = adCmdStoredProc
  
      Set rs = .Execute()
  End With
  
  'Set the form's Recordset property to the ADO recordset
  Set Me.Recordset = rs
  
  Set rs = Nothing
  Set cn = Nothing
  Set cm = Nothing
  </code></pre>