vba 配置 Access 报表以使用 SQL Server 存储过程作为其记录源
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20262266/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Configuring an Access report to use a SQL Server stored procedure as its record source
提问by ihorko
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:
我正在尝试在 MS Access 2010 中创建一个报告，其中包含 MS SQL Server 存储过程的结果。在我的 VBA 代码中，我尝试：
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.
运行时错误“32585”
此功能仅在 ADP 中可用。
What I'm doing wrong or how to fix that?
我做错了什么或如何解决？
回答by Gord Thompson
Create a saved pass-through query in Access that executes your stored procedure. In this example I'll call the named query [myPassThroughQuery].
在 Access 中创建一个保存的传递查询来执行您的存储过程。在本例中，我将调用命名查询 [myPassThroughQuery]。
Edit your report to make myPassThroughQuerythe Record Sourcefor the report.
编辑您的报告做出myPassThroughQuery的Record Source报表。
Now you can tweak the SP call before opening the report:
现在您可以在打开报告之前调整 SP 调用：
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
