在Access中使用SQL Server存储过程的输出参数

我试图从Access 2013 VBA执行过程后从SQL Server 2008中的存储过程获取输出变量(新标识列).我并不完全了解所有的adodb内容,而且我已尝试过不同文章中的一些内容但无济于事.这就是我现在所处的地方......

存储过程:

PROCEDURE [proc_NewClient]
    @Type INT, 
    @PhoneIntakeID INT,
    @ClientID INT = NULL,
    @NewPSID INT = null OUTPUT (2 possible ways to call the proc, one does not produce an output variable)

   INSERT INTO tblClientDetails (ClientID, PhoneIntakeID, Client_Status) 
   VALUES (@ClientID, @PhoneIntakeID, 'Applicant')

   DECLARE @NewClientDetailID int
   SELECT @NewClientDetailID = SCOPE_IDENTITY()

   INSERT INTO tblPS_Psychosocial (ClientDetailID, Client) 
   VALUES (@NewClientDetailID, @ClientID)

   SELECT @NewPSID = SCOPE_IDENTITY()

   INSERT INTO tblPS_AbuseHistory (PsychosocialID) 
   VALUES (@NewPSID)

   INSERT INTO tblPS_FamilyHistory(PsychosocialID) VALUES (@NewPSID)
   INSERT INTO tblPS_FinancialHistory (PsychosocialID) VALUES (@NewPSID)
   INSERT INTO tblPS_LegalHistory (PsychosocialID) VALUES (@NewPSID)
   INSERT INTO tblPS_MedicalHistory (PsychosocialID) VALUES (@NewPSID)
   INSERT INTO tblPS_PsychiatricHistory (PsychosocialID) VALUES (@NewPSID)
   INSERT INTO tblPS_SocialHistory (PsychosocialID)  VALUES (@NewPSID)
   INSERT INTO tblPS_SpiritualHistory (PsychosocialID) VALUES (@NewPSID)
   INSERT INTO tblPS_SubstanceHistory (PsychosocialID) VALUES (@NewPSID)

   INSERT INTO tblVocAssessment(ClientDetailID) VALUES (@NewClientDetailID)

从我得到的形式:

    Dim cnn As ADODB.Connection
            Dim cmd As New ADODB.Command, rs As New ADODB.Recordset, param1 As New ADODB.Parameter, param2 As New ADODB.Parameter, param3 As New ADODB.Parameter, param4 As New ADODB.Parameter
            Set cnn = New ADODB.Connection
            cnn.ConnectionString = "DRIVER=SQL Server;SERVER=SRV-DB01;DATABASE=TWHClientMgmt;Trusted_Connection=Yes"

            cnn.Open cnn.ConnectionString

            Set cmd = New ADODB.Command
            cmd.ActiveConnection = cnn
            cmd.CommandType = adCmdStoredProc
            cmd.CommandText = "[THEWOMENSHOME\jboyd].proc_NewClient"


            Set param1 = cmd.CreateParameter("@Type", adinteger, adparamInput, , 2)
            cmd.Parameters.Append param1
            Set param2 = cmd.CreateParameter("@PhoneIntakeID", adinteger, adparamInput, , Me.PhoneIntakeID)
            cmd.Parameters.Append param2
            Set param3 = cmd.CreateParameter("@ClientID", adinteger, adparamInput, , intNewID)
            cmd.Parameters.Append param3
            Set param4 = cmd.CreateParameter("@NewPSID", adinteger, adParamOutput)
            cmd.Parameters.Append param4

rs.open cmd

到目前为止代码工作,生成新记录等.当我从SQL Server运行存储过程时,它返回正确的标识列号,但我尝试了多种方法来引用VBA中的输出并始终最终到来一个null.我如何正确引用它？

要检索存储过程的OUTPUT参数的值,只需.ExecuteADODB.Command,然后检索.Value相应的ADODB.Parameter,如下所示:

cmd.Execute
' retrieve and display the returned OUTPUT parameter value
Debug.Print cmd.Parameters("@NewPSID").Value