Output from stored procedure

Nigel Keith-Walker
Nigel Keith-Walker used Ask the Experts™
on
How can I use output from a stored procedure in VBA coding?

The VBA coding calls a stored procedure to add a new customer by passing all of the relevant fields except the new customer ID.  The stored Proc has a transaction that determines the next ID and then inserts the customer using that ID before committing.

The ID is returned as a declared output parameter and the return code is set to 0 for success, 4 for failed insert.

I need to
1. determine if the insert was successful from the return code
2. Fill in the form with the new customer ID.

But I cannot find it in the querydef properties.


Private Sub SaveNewCustomer()
' use stored proc to save the newcustomer v9_5_2
' Transaction determines the next Cust Id number and saves before committing
  Dim parm As String
  Dim db As DAO.Database
  Dim qdf As DAO.QueryDef
  Dim qspt As String
  Dim msg As String
  Dim msg2 As String
  Dim rst As Recordset
  Dim ReturnCode As Integer
  
  On Error GoTo ErrHandle
  
' move the NewZealand street to the address field
  If Me.Country = "New Zealand" Then
    Me.Address = Me.NZ_Street
  End If
  
' build the parameters for the stored proc sp_new_customer
  parm = "NULL"                                   ' @ID
  parm = parm & ",'" & Me.FirstName & "'"        ' @FirstName
  parm = parm & ",'" & Me.LastName & "'"         ' @LastName
  parm = parm & "," & SQLdate(Me.DOB)            ' @DOB
  parm = parm & ",'" & Me.ID_Type & "'"          ' @ID_Type
  parm = parm & ",'" & Me.ID_Number & "'"        ' @ID_Number
  parm = parm & ",'" & Me.ID_Issuer & "'"        ' @ID_Issuer
  parm = parm & ",'" & Me.ID_Country & "'"       ' @ID_Country
  parm = parm & ",'" & Me.ID2_Type & "'"         ' @ID2_Type
  parm = parm & ",'" & Me.ID2_Number & "'"       ' @ID2_Number
  parm = parm & ",'" & Me.ID2_Issuer & "'"       ' @ID2_Issuer
  parm = parm & ",'" & Me.ID2_Country & "'"      ' @ID2_Country
  parm = parm & ",'" & Me.Address & "'"          ' @Address
  parm = parm & ",'" & Me.Suburb & "'"           ' @Suburb
  parm = parm & ",'" & Me.PCode & "'"            ' @Pcode
  parm = parm & ",'" & Me.State & "'"            ' @State
  parm = parm & ",'" & Me.Country & "'"          ' @Country
  parm = parm & ",'" & Me.Occupation & "'"       ' @Occupation
  parm = parm & ",'" & Me.Phone & "'"            ' @Phone
  parm = parm & ",'" & Me.EMail & "'"            ' @email
  parm = parm & ",'" & Me.SightedBy & "'"        ' @Sightedby
  parm = parm & ",'" & Me.Comment & "'"          ' @Comment
  parm = parm & ",'" & Me.Creator & "'"          ' @creator
  parm = parm & ",'" & Me.DateCreate & "'"       ' @datecreate
  parm = parm & ",'" & Me.Modifier & "'"         ' @modifier
  parm = parm & ",'" & Me.DateMod & "'"          ' @datemod
  parm = parm & ",'" & Me.Status & "'"           ' @status
  parm = parm & ",'" & Me.StreetNumber & "'"     ' @streetNumber
  parm = parm & ",'" & Me.ID_Doc_1.HyperlinkAddress & "'"     ' @ID_Doc1
  parm = parm & ",'" & Me.ID_Doc_2.HyperlinkAddress & "'"     ' @ID_Doc2
  
  
  Set db = CurrentDb()
  qspt = "DECLARE @RC int" & vbCrLf
  qspt = qspt & "DECLARE @Id int" & vbCrLf
  qspt = qspt & "-- Set parameter values" & vbCrLf
  qspt = qspt & "EXEC @RC = [sp_new_customer]" & parm
  
  
  Set db = CurrentDb()
  Set qdf = db.QueryDefs("qspt_New_customer")
  qdf.SQL = qspt
 
 ' test
   With qdf
      Debug.Print .Name
      Debug.Print "  " & .SQL
   End With
 
  Set qdf = Nothing
  Set db = Nothing

Exit_Sub:
    Exit Sub

End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Commented:
Have the sp return a recordset with the values you need.
Nigel Keith-WalkerContractor

Author

Commented:
Hi Pat
Within the MS access I can see that there is a qspt with the result I need.  But I just cannot find the right commands to make it available to the VBA code.
qspt_new_customer
Nigel Keith-WalkerContractor

Author

Commented:
viewing the properties of qdf, there is an object with the name @ID but the value is not available

qdf_@ID
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Nigel Keith-WalkerContractor

Author

Commented:
MSDN explanation
MSDN has an explanation, but I have not been able to work out how to use their explanation.  How embarrassing!
Contractor
Commented:
I have a workaround.  My attempts to use a recordset failed with this stored procedure.  It may be because the stored procedure has a begin transaction and begin commitment.  But I was able to determine the ID from the parameter.

The pertinent part is:
'  Debug.Print cmd.Parameters("@ID").Value
  ReturnCode = cmd.Parameters("@Return_Value").Value
  ID_new = cmd.Parameters("@ID").Value

The full subroutine is below:

   Private Sub SaveNewCustomer()
' use stored proc to save the newcustomer v9_5_2
' Transaction determines the next Cust Id number and saves before committing

  Dim msg As String
  Dim msg2 As String
  Dim rst As New ADODB.Recordset
  Dim cmd As New ADODB.Command
  Dim ReturnCode As Integer
  Dim t_Creator As String
  Dim t_DateCreate As Date
'  Dim newcust As String
  Dim ID_new As Long
    
  On Error GoTo ErrHandle
  
' move the NewZealand street to the address field
  If Me.Country = "New Zealand" Then
    Me.Address = Me.NZ_Street
  End If
  
' set temp values for creator and date of creation
  t_Creator = [TempVars]![Logon_UserID].[Value]
  t_DateCreate = Now()

  dcnn  ' Check the connection
  
  Set cmd = New ADODB.Command
  Set rst = New ADODB.Recordset
  Set rst.ActiveConnection = gcnn
  rst.CursorLocation = adUseClient
  
  
' build the parameters for the stored proc sp_new_customer
  With cmd
    .ActiveConnection = gcnn
    .CommandText = "sp_new_customer"
    .CommandType = adCmdStoredProc
    .Parameters.Append .CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue, 0)
    .Parameters.Append .CreateParameter("@ID", adInteger, adParamReturnValue, , ID_new)
    .Parameters.Append .CreateParameter("@FirstName", adVarChar, adParamInput, 50, Me.FirstName)
    .Parameters.Append .CreateParameter("@LastName", adVarChar, adParamInput, 50, Me.LastName)
    .Parameters.Append .CreateParameter("@DOB", adDate, adParamInput, 50, mmmDate(Me.DOB))
    .Parameters.Append .CreateParameter("@ID_Type", adVarChar, adParamInput, 4, Me.ID_Type)
    .Parameters.Append .CreateParameter("@ID_Number", adVarChar, adParamInput, 20, Me.ID_Number)
    .Parameters.Append .CreateParameter("@ID_Issuer", adVarChar, adParamInput, 50, Me.ID_Issuer)
    .Parameters.Append .CreateParameter("@ID_Country", adVarChar, adParamInput, 50, Me.ID_Country)
    .Parameters.Append .CreateParameter("@ID2_Type", adVarChar, adParamInput, 4, Me.ID2_Type)
    .Parameters.Append .CreateParameter("@ID2_Number", adVarChar, adParamInput, 20, Me.ID2_Number)
    .Parameters.Append .CreateParameter("@ID2_Issuer", adVarChar, adParamInput, 50, Me.ID2_Issuer)
    .Parameters.Append .CreateParameter("@ID2_Country", adVarChar, adParamInput, 50, Me.ID2_Country)
    .Parameters.Append .CreateParameter("@Address", adVarChar, adParamInput, 100, Me.Address)
    .Parameters.Append .CreateParameter("@Suburb", adVarChar, adParamInput, 50, Me.Suburb)
    .Parameters.Append .CreateParameter("@Pcode", adVarChar, adParamInput, 15, Me.PCode)
    .Parameters.Append .CreateParameter("@State", adVarChar, adParamInput, 35, Me.State)
    .Parameters.Append .CreateParameter("@Country", adVarChar, adParamInput, 35, Me.Country)
    .Parameters.Append .CreateParameter("@Occupation", adVarChar, adParamInput, 36, Me.Occupation)
    .Parameters.Append .CreateParameter("@Phone", adVarChar, adParamInput, 20, Me.Phone)
    .Parameters.Append .CreateParameter("@Email", adVarChar, adParamInput, 70, Me.EMail)
    .Parameters.Append .CreateParameter("@SightedBy", adVarChar, adParamInput, 50, Me.SightedBy)
    .Parameters.Append .CreateParameter("@Comment", adVarChar, adParamInput, 36, Me.Comment)
    .Parameters.Append .CreateParameter("@Creator", adVarChar, adParamInput, 50, t_Creator)
    .Parameters.Append .CreateParameter("@DateCreate", adDate, adParamInput, 50, t_DateCreate)
    .Parameters.Append .CreateParameter("@Modifier", adVarChar, adParamInput, 50, Null)
    .Parameters.Append .CreateParameter("@DateMod", adDate, adParamInput, 50, "01-Jan-1980")
    .Parameters.Append .CreateParameter("@Status", adVarChar, adParamInput, 20, Me.Status)
    .Parameters.Append .CreateParameter("@StreetNumber", adVarChar, adParamInput, 10, Me.StreetNumber)
    .Parameters.Append .CreateParameter("@ID_Doc_1", adVarChar, adParamInput, 255, Me.ID_Doc_1.HyperlinkAddress)
    .Parameters.Append .CreateParameter("@ID_Doc_2", adVarChar, adParamInput, 255, Me.ID_Doc_2.HyperlinkAddress)
  End With
    
  Set rst = cmd.Execute

'  Debug.Print cmd.Parameters("@ID").Value
  ReturnCode = cmd.Parameters("@Return_Value").Value
  ID_new = cmd.Parameters("@ID").Value
  
  Me.ID = ID_new
  Me.Creator = t_Creator
  Me.DateCreate = t_DateCreate
  OptionAction = 3
  FormatCustForm Me.Form, "View"
  
  msg = "New Customer saved as:" & vbCrLf & ID_new & " : " & FirstName & " " & LastName
  MsgBox msg, vbInformation, "Form Customer"
  
Exit_Sub:
    Exit Sub

ErrHandle:
    msg = "Problem saving new customer" & vbCrLf & msg2 & vbCrLf & Err.Description
    MsgBox msg, vbExclamation, "Form Customer"
End Sub 

Open in new window

Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Nigel, is this issue solved?
Nigel Keith-WalkerContractor

Author

Commented:
It works and there were no other satisfactory responses

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial