ms access - 从sql存储过程返回记录
我有一个 access 2010，我想在 sql 2008 服务器上调用 sql 存储过程，并将结果返回到记录集或直接绑定到报表。
下面的方法返回错误“ADP 中不可用的功能”或类似内容 - 它适用于表单，但不适用于报告。
我该怎么做呢？
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
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.
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.
How do I go about doing this?
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
考虑创建一个视图来包装存储过程 (
exec usp_whatever) 的结果，然后创建一个指向新视图的链接表。此外，您还可以创建一个直通查询 (
exec usp_whatever) 来执行存储过程并直接返回结果。然后只需将您的报告或表单绑定到查询即可。Consider creating a view to wrap the results of the stored procedure (
exec usp_whatever) and then create a linked table to the new view.Also, you may be able to create a passthrough query (
exec usp_whatever) that executes your stored procedure and returns your results directly. Then just bind your report or form to the query.