We help IT Professionals succeed at work.

Output from stored procedure

76 Views
Last Modified: 2017-08-04
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

CERTIFIED EXPERT
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
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:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Vitor Montalv√£oIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

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

Author

Commented:
It works and there were no other satisfactory responses

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions