Configuring an Access report to use a SQL Server stored procedure as its record source

Question

I'm trying to create a report in MS Access 2010 with results of MS SQL Server Stored Procedure. In my VBA code I try:

Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.CreateQueryDef("")
qdf.Connect = CurrentDb.TableDefs("[MyTable]").Connect
qdf.SQL = "exec spMyProc @ID = " & "1"
qdf.ReturnsRecords = True

Set rs = qdf.OpenRecordset(dbOpenSnapshot)

Set Me.Recordset = rs

But it throws an error:

Run-time error '32585'
This feature is only availabe in an ADP.

What I'm doing wrong or how to fix that?

Answer 1

Create a saved pass-through query in Access that executes your stored procedure. In this example I'll call the named query [myPassThroughQuery].

Edit your report to make myPassThroughQuery the Record Source for the report.

Now you can tweak the SP call before opening the report:

Dim cdb As DAO.Database, qdf As DAO.QueryDef
Set cdb = CurrentDb
Set qdf = cdb.QueryDefs("myPassThroughQuery")
qdf.SQL = "EXEC spMyProc @ID = " & "1"
Set qdf = Nothing
Set cdb = Nothing
DoCmd.OpenReport "mySpReport", acViewPreview