在 VBA 中执行存储过程的两种方式，哪一种更好？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24328669/
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
Two ways to execute a Stored procedure in VBA, Which one is better?
提问by VictoriaJay
Background:Work on frontend Ms-Access 2010 and backend SQL server 2008 Managment Studio
背景：在前端 Ms-Access 2010 和后端 SQL server 2008 Management Studio 上工作
For executing stored procedures I have been using a pretty lengthy process as seen here: in VBA
为了执行存储过程，我一直在使用一个相当冗长的过程，如下所示：在 VBA 中
Set Conn = New ADODB.connection
Conn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;....."
Conn.Open
Set cmd = New ADODB.Command
cmd.ActiveConnection = Conn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "upGetTestIdForAnalyte"
cmd.Parameters.Append cmd.CreateParameter("@WOID", adVarChar, adParamInput, 60, MySampleName)
cmd.Parameters.Append cmd.CreateParameter("@Analyte", adVarChar, adParamInput, 60, MyAnalyte)
cmd.Parameters.Append cmd.CreateParameter("@SampleID", adVarChar, adParamInput, 60, MyConcentration
cmd.Execute
Conn.Close
Someone told me there was a better way to execute a stored procedure and the correct way would be something like this: in VBA
有人告诉我有一种更好的方法来执行存储过程，正确的方法是这样的：在 VBA 中
strsql = "Exec upGetTestIdForAnalyte(WOID, Analyte, SampleID)"
test = ExecuteNonQuery(strsql)
But I got a lot of errors for this process and I looked up ExecuteNonQuery and it said it was only for VB (http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx). One of the advantages of this process would be its conciseness and the fact that it connects automatically to the current database. I would have to change the connection string in my current sp process because it is set to link to a test database not the actual one. Another advantage would be that it returns a value when executed. I would like my current process to do that because I want to somehow verify it ran and other stored procedures I need to make, need to return the number of records affected.
但是我在这个过程中遇到了很多错误，我查找了 ExecuteNonQuery，它说它仅适用于 VB（http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery。 aspx）。这个过程的优点之一是它的简洁性和自动连接到当前数据库的事实。我必须更改当前 sp 进程中的连接字符串，因为它设置为链接到测试数据库而不是实际数据库。另一个优点是它在执行时返回一个值。我希望我当前的进程这样做，因为我想以某种方式验证它运行和我需要制作的其他存储过程，需要返回受影响的记录数。
My questions are:Is the second method a legitimate process and is that the correct syntax? Also is there any difference in what each process accomplishes? Also is there a way for the first process to return a value when executed? Thank you for your time.
我的问题是：第二种方法是一个合法的过程吗，那是正确的语法吗？每个过程完成的内容也有什么不同吗？还有没有办法让第一个进程在执行时返回一个值？感谢您的时间。
UPDATE:This is my stored procedure I'm currently working on. My sp sees if a testID exists or not, I will continue with the program after calling the sp if variable ThisTestId >0 else I will raise an error testid not found
更新：这是我目前正在处理的存储过程。我的 sp 查看 testID 是否存在，我将在调用 sp 后继续执行程序，如果变量 ThisTestId >0 否则我将引发错误 testid not found
CREATE PROCEDURE upGetTestIDForAnalyte @WOID nvarchar(60), @SampleID nvarchar(60),@Analyte nvarchar(60), @Test var(20) output
AS
SELECT TestID = t1.TestID
FROM tblWOSampleTest t1
JOIN tblTest t2
ON t1.TestID=t2.TestID;
WHERE @WOID = t1.WOID AND @SampleID = t1.SampleID AND @Analyte = t2.Analyte
GO
采纳答案by Horaciux
I'll stay with first process and add an output parameter like this:
我将继续第一个进程并添加一个这样的输出参数：
ccmd.parameters.Append ccmd.CreateParameter("OutParam", adInteger, adParamOuput, , NULL) ' output parameter
You need to add this parameter in stored procedure as well like this:
您还需要在存储过程中添加此参数，如下所示：
@OutParam int OUTPUT
EDITAdded OUT parameter, changed to integer in VBA code. See how @@ROWCOUNTwork
编辑添加 OUT 参数，在 VBA 代码中更改为整数。看看@@ROWCOUNT 是如何工作的
CREATE PROCEDURE upGetTestIDForAnalyte @WOID nvarchar(60), @SampleID nvarchar(60),@Analyte nvarchar(60), @RecordsAfected int OUT
AS
SELECT TestID = t1.TestID
FROM tblWOSampleTest t1
JOIN tblTest t2
ON t1.TestID=t2.TestID;
WHERE @WOID = t1.WOID AND @SampleID = t1.SampleID AND @Analyte = t2.Analyte
set @recordsAfected = @@ROWCOUNT
GO
回答by Albert D. Kallal
The examples posted here have way too much code.
此处发布的示例代码太多。
The original question is the poster has seen some examples where only one or two lines of code is required.
最初的问题是发帖人看到了一些只需要一两行代码的例子。
In fact this code works and passes a parameter to a store procedure.
实际上，此代码有效并将参数传递给存储过程。
With CurrentDb.QueryDefs("MyPass")
.SQL = "exec MySproc" & ProducutID
.Execute
End If
The above is only two lines of code.
上面只有两行代码。
The advantages of this approach?
这种方法的优点是什么？
Note how we did not have to mess (or pass) with a connection string.
请注意我们如何不必弄乱（或通过）连接字符串。
Note how we did not have to declare any connection object in code.
请注意我们不必在代码中声明任何连接对象。
Note how we did not have to store or have the user name or password anywhere in the code.
请注意，我们不必在代码中的任何位置存储或拥有用户名或密码。
Note how we don't have to create a query def object in code either.
请注意，我们也不必在代码中创建查询定义对象。
In fact the whole thing works without declaring ANY variables.
事实上，整个过程无需声明任何变量即可工作。
And the above could have been written on two lines of code, but I decided to post a massive 4 lines of code for increased readability.
以上可以写在两行代码上，但我决定发布大量的 4 行代码以提高可读性。
The advantages of this setup are many, but the main reason is such an approach is MOST simple and easy to maintain.
这种设置的优点很多，但主要原因是这种方法最简单且易于维护。
The other posted solutions here serve only to force one to write many lines of code – this simply increases development costs for their employers.
此处发布的其他解决方案仅用于迫使人们编写多行代码——这只会增加其雇主的开发成本。
回答by Patrick Honorez
From Microsoft help site:
Function ExecuteSPT (sqltext As String, connectstring As String)
' Purpose: Run a temporary pass-through query.
' Accepts: sqltext: SQL string to run.
' connectstring: Connection string, which must be at least
' "ODBC;".
' Returns: nothing.
Dim mydb As Database, myq As QueryDef
Set mydb = DBEngine.Workspaces(0).Databases(0)
' Create a temporary QueryDef object that is not saved.
Set myq = mydb.CreateQueryDef("")
' Set the ReturnsRecords property to False in order to use the
' Execute method.
myq.returnsrecords = False
myq.connect = connectstring
myq.sql = sqltext
myq.Execute
myq.Close
End Function
It can be easily altered to return as DAO.Recordset. DAO is still the "native" data access in MS Access.
它可以很容易地更改为作为 DAO.Recordset 返回。DAO 仍然是 MS Access 中的“本机”数据访问。
