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

ms-access, ms-access-2010, sql-server, stored-procedures, vba

Solution

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

Problem

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?

Original source