配置访问报表以将SQL Server存储过程用作其记录源 [英] Configuring an Access report to use a SQL Server stored procedure as its record source

问题描述

我正在尝试在MS Access 2010中创建一个报告，其中包含MS SQL Server存储过程的结果.在我的VBA代码中，我尝试:

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

但是它会引发错误:

运行时错误'32585'
此功能仅在ADP中可用.

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

我在做什么错或如何解决?

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

推荐答案

在Access中创建一个保存的传递查询，以执行您的存储过程.在此示例中，我将命名查询称为[myPassThroughQuery].

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

编辑报告以使myPassThroughQuery成为报告的Record Source.

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

现在您可以在打开报告之前调整SP调用:

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

这篇关于配置访问报表以将SQL Server存储过程用作其记录源的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！