如何在MS Access VBA中调用存储过程并读取输出参数？

问题说明 #

我有一个接收1个输入参数、包含2个输出参数的SQL存储过程，希望在Access VBA中调用它并读取这两个输出参数用于后续逻辑。但找到的一段类似代码粘贴到Access VBA后，大量行标红提示语法错误。

存储过程代码 #

ALTER PROCEDURE [dbo].[spTest]
    @Input1 nvarchar(100),
    @Output1 int OUTPUT,
    @Output2 varchar(100) OUTPUT
AS
BEGIN
    SET @Output1 = 30
    SET @Output2 = 'OK'

    RETURN 0
END

报错的VBA代码（VB.NET语法，不适用于Access VBA） #

Using connection As New System.Data.SqlClient.SqlConnection(connectionstrng) 'Error here
    connection.Open() 'Error here
    Using command As New System.Data.SqlClient.SqlCommand("sp_Custom_InsertxRef", connection) 'Error here
        command.CommandType = CommandType.StoredProcedure
        command.Parameters.Add("@DocumentID", SqlDbType.Int, 4, ParameterDirection.Input).Value = epdmParDoc.ID
        command.Parameters.Add("@RevNr", SqlDbType.Int, 4, ParameterDirection.Input).Value = epdmParDoc.GetLocalVersionNo(parFolderId)
        command.Parameters.Add("@xRefDocument", SqlDbType.Int, 4, ParameterDirection.Input).Value = targetReplaceDoc.ID
        command.Parameters.Add("@xRefRevNr", SqlDbType.Int, 4, ParameterDirection.Input).Value = targetReplaceDoc.CurrentVersion
        command.Parameters.Add("@xRefProjectId", SqlDbType.Int, 4, ParameterDirection.Input).Value = parFolderId
        command.Parameters.Add("@RefCount", SqlDbType.Int, 4, ParameterDirection.Input).Value = count
        'command.Parameters.Add("@xRef", OleDbType.Integer, 4, ParameterDirection.InputOutput).Value = -1
        command.Parameters.Add("@xRef", SqlDbType.Int) 'Error here
        command.Parameters("@xRef").Direction = ParameterDirection.Output
        command.ExecuteReader() 'Error here
        xRefId = command.Parameters("@xRef").Value
    End Using 'Error here
    connection.Close() 'Error here
End Using

错误原因 #

这段代码是VB.NET语法，依赖.NET框架的System.Data.SqlClient类库，但Access VBA基于COM组件，使用的是ADODB对象模型，两者语法和对象完全不兼容，因此出现大量语法错误。

正确的Access VBA实现代码 #

方法1：前期绑定（需引用ADODB库） #

1. 打开Access VBA编辑器（按Alt+F11）
2. 点击菜单【工具】→【引用】，勾选Microsoft ActiveX Data Objects x.x Library（推荐选最新版本，比如6.1）
3. 粘贴以下代码：

Sub CallStoredProcedure()
    Dim conn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim inputParam As ADODB.Parameter
    Dim outputParam1 As ADODB.Parameter
    Dim outputParam2 As ADODB.Parameter
    Dim connStr As String
    Dim output1 As Integer
    Dim output2 As String
    
    ' 修改为你的SQL服务器连接信息
    connStr = "Provider=SQLOLEDB;Data Source=你的SQL服务器地址;Initial Catalog=你的数据库名;User ID=用户名;Password=密码;"
    
    ' 初始化对象
    Set conn = New ADODB.Connection
    Set cmd = New ADODB.Command
    
    ' 打开连接
    conn.Open connStr
    
    ' 配置命令对象
    With cmd
        .ActiveConnection = conn
        .CommandText = "spTest" ' 指定存储过程名称
        .CommandType = adCmdStoredProc
    End With
    
    ' 添加输入参数
    Set inputParam = cmd.CreateParameter("@Input1", adVarChar, adParamInput, 100)
    inputParam.Value = "测试输入值" ' 替换为实际输入值
    cmd.Parameters.Append inputParam
    
    ' 添加第一个输出参数
    Set outputParam1 = cmd.CreateParameter("@Output1", adInteger, adParamOutput)
    cmd.Parameters.Append outputParam1
    
    ' 添加第二个输出参数
    Set outputParam2 = cmd.CreateParameter("@Output2", adVarChar, adParamOutput, 100)
    cmd.Parameters.Append outputParam2
    
    ' 执行存储过程
    cmd.Execute
    
    ' 读取输出参数值
    output1 = cmd.Parameters("@Output1").Value
    output2 = cmd.Parameters("@Output2").Value
    
    ' 替换为你的业务逻辑
    MsgBox "输出参数1：" & output1 & vbCrLf & "输出参数2：" & output2
    
    ' 清理资源
    conn.Close
    Set cmd = Nothing
    Set conn = Nothing
End Sub

方法2：后期绑定（无需引用库，兼容性更强） #

如果不想手动引用ADODB库，可使用后期绑定：

Sub CallStoredProcedure_LateBinding()
    Dim conn As Object
    Dim cmd As Object
    Dim inputParam As Object
    Dim outputParam1 As Object
    Dim outputParam2 As Object
    Dim connStr As String
    Dim output1 As Integer
    Dim output2 As String
    
    ' 修改为你的SQL服务器连接信息
    connStr = "Provider=SQLOLEDB;Data Source=你的SQL服务器地址;Initial Catalog=你的数据库名;User ID=用户名;Password=密码;"
    
    ' 创建对象
    Set conn = CreateObject("ADODB.Connection")
    Set cmd = CreateObject("ADODB.Command")
    
    conn.Open connStr
    
    With cmd
        .ActiveConnection = conn
        .CommandText = "spTest"
        .CommandType = 4 ' adCmdStoredProc的数值常量
    End With
    
    ' 添加输入参数（adVarChar=200, adParamInput=1）
    Set inputParam = cmd.CreateParameter("@Input1", 200, 1, 100)
    inputParam.Value = "测试输入值"
    cmd.Parameters.Append inputParam
    
    ' 添加第一个输出参数（adInteger=3, adParamOutput=2）
    Set outputParam1 = cmd.CreateParameter("@Output1", 3, 2)
    cmd.Parameters.Append outputParam1
    
    ' 添加第二个输出参数（adVarChar=200, adParamOutput=2）
    Set outputParam2 = cmd.CreateParameter("@Output2", 200, 2, 100)
    cmd.Parameters.Append outputParam2
    
    ' 执行存储过程
    cmd.Execute
    
    ' 读取输出参数值
    output1 = cmd.Parameters("@Output1").Value
    output2 = cmd.Parameters("@Output2").Value
    
    ' 替换为你的业务逻辑
    MsgBox "输出参数1：" & output1 & vbCrLf & "输出参数2：" & output2
    
    ' 清理资源
    conn.Close
    Set cmd = Nothing
    Set conn = Nothing
End Sub

关键注意事项 #

• 连接字符串需根据你的SQL Server配置修改（身份验证方式、服务器地址、数据库名等）
• 输出参数的数据类型和长度必须与存储过程定义完全一致
• Access VBA中无需使用ExecuteReader，直接用cmd.Execute即可执行无结果集的存储过程

内容的提问来源于stack exchange，提问作者whatwhatwhat