如何在MS Access VBA中调用SQL Server存储过程 [英] How to call SQL Server Stored Procedure from within MS Access VBA
本文介绍了如何在MS Access VBA中调用SQL Server存储过程的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个带参数的存储过程,但不返回任何东西.如何在MS Access中调用它?
I have a Stored Procedure that takes parameters, but does not return anything. How do I call it from within MS Access?
编辑-在雷姆(Remou)的协助下,我开始工作了:
EDIT - With Remou's assistance, I've got it working:
Set cnn = New adodb.Connection
cnn.ConnectionString =
"driver=SQL Server;server=SERVER\SERVER;database=a_db;Trusted_Connection=Yes"
cnn.Open cnn.ConnectionString
Set cmd = New adodb.Command
cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "stprMoveDataSet"
Set param = cmd.CreateParameter
("DataSetID", adInteger, adParamInput, 6, stDataSet)
cmd.Parameters.Append param
Set param = cmd.CreateParameter
("Destination", adChar, adParamInput, 2, stDestination)
cmd.Parameters.Append param
rs.CursorType = adOpenStatic
rs.CursorLocation = adUseClient
rs.LockType = adLockOptimistic
rs.Open cmd
推荐答案
以下是可能有帮助的概述.稍后我可能会再详细介绍.它不是特定于您的情况,但可以使您继续前进:)
Here is an outline that may help. I may be able to get back with more detail later. It is not specific to your case, but it may keep you going :)
Public dbconn As ADODb.Connection
Dim cmd As New ADODb.command
Dim rs As New ADODb.Recordset
Dim param As New ADODb.Parameter
Set dbconn = New ADODb.Connection
dbconn.ConnectionString = GetServerCon
dbconn.Open dbconn.ConnectionString
Set cmd = New ADODb.command
cmd.ActiveConnection = dbconn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "insert_user"
Set param = cmd.CreateParameter(_
"username", adVarChar, adParamInput, 50, Environ("UserDomain") & "dddddddd\" _
& Environ("Username"))
cmd.Parameters.Append param
rs.CursorType = adOpenStatic
rs.CursorLocation = adUseClient
rs.LockType = adLockOptimistic
rs.Open cmd
这篇关于如何在MS Access VBA中调用SQL Server存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文