title: Using output parameter from SQL Server stored procedure in Access
body: <p>I am attempting to get an output variable (the new identity column) from a stored procedure in SQL Server 2008 after executing the procedure from Access 2013 VBA. I don't fully understand all the adodb stuff, and I've tried a number of things from different articles to no avail. Here's where I am with it now...</p>
  
  <p>Stored procedure:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>And from the form I've got:</p>
  
  <pre><code>    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
  </code></pre>
  
  <p>To this point the code works, the new records are generated, etc. When I run the stored procedure from SQL Server it returns the correct identity column number, but I've tried multiple ways to reference the output in VBA and always end up coming up with a null. How do I reference it here correctly?</p>