MS Access 调用 SQL Server 存储过程 [英] MS Access call SQL Server stored procedure

问题描述

我有一个 MS Access 应用程序，其中包含链接到 SQL Server 的所有表，因此在 MS Access VBA 代码或查询中，我使用这些表非常简单，我通过名称访问它们，例如 [Customers].

I have an MS Access application that contains all tables linked to SQL Server, so in MS Access VBA code or query I work with those tables very simple, I access them via name, like [Customers].

此外，我在 SQL Server 中有一个名为 sp_CopyData 的存储过程，我需要从我的 VBA 代码中调用它.如何在不创建到 SQL Server 的新连接的情况下做到这一点(我已经在某处拥有它！?因为我可以访问表)?

Also I have a stored procedure in SQL Server called sp_CopyData which I need to call from my VBA code. How can I do that without creating new connection to SQL Server (I already have it somewhere!? because I have access to tables)?

还是不可能?感谢任何帮助.谢谢！

Or it's impossible? Appreciate any help. Thanks!

推荐答案

找到了正确答案，应该是这样的:

The right answer found out, it should be like:

Dim qdef As DAO.QueryDef
Set qdef = CurrentDb.CreateQueryDef("")
qdef.Connect = CurrentDb.TableDefs("[ANY LINKED TABLE TO MS SQL SERVER]").Connect
qdef.SQL = "EXEC sp_CopyData"
qdef.ReturnsRecords = False  ''avoid 3065 error
qdef.Execute

这篇关于MS Access 调用 SQL Server 存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！