MS Access调用SQL Server存储过程

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].

我有一个MS Access应用程序，它包含与SQL Server相关的所有表，因此在MS Access VBA代码或查询中，我使用这些表非常简单，我通过名称访问它们，比如[客户]。

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)?

我还在SQL Server中有一个名为sp_CopyData的存储过程，我需要从VBA代码中调用它。如何在不创建到SQL Server的新连接的情况下做到这一点呢?因为我可以使用表格)?

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

或者是不可能的吗?感谢任何帮助。谢谢!

4 个解决方案

#1

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

#2

Create a pass-though query, and you can then use this throught the WHOLE application anytime you need to execute some T-SQL.

创建一个传递查询，然后您可以在需要执行某些T-SQL时使用整个应用程序。

The code this becomes:

这个代码就变成:

With CurrentDb.QueryDefs("qPass")
  .SQL = "exec sp_copydata"
  .ReturnsRecords = False  ''avoid 3065 error
  .Execute
End With

#3

Try:

试一试:

CurrentProject.Connection.Execute "EXEC sp_CopyData"

References: http://msdn.microsoft.com/en-us/library/office/ff821478(v=office.14).aspx

引用:http://msdn.microsoft.com/en-us/library/office/ff821478(v = office.14). aspx

#4

The code in MS Access works for me:

Access的代码为我工作:

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.ActiveConnection = "Provider=SQLOLEDB.1;Persist Security Info=False;Initial Catalog=[DB];Data Source=[PC];Integrated Security=SSPI;"
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_CopyData"
cmd.Parameters.Append cmd.CreateParameter("@param", adVarChar, adParamInput, 255, param)
cmd.Execute

#1