469,290 Members | 1,949 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,290 developers. It's quick & easy.

Sql server stored procedure output parameter to ms access vba

374 256MB
Hi all,

I use the following function to execute a stored procedure which inserts records in to my sql database. I am trying to return the primary key value so that I can display it on my page however I keep getting the error that the "item is not found in the current collection".

My function is:
Expand|Select|Wrap|Line Numbers
  1. Function Execute_Stored_Procedure(Proc_Name As String, Proc_Values As String, Optional ByVal Output_Return As Boolean)
  2.  
  3.    On Error GoTo Execute_Stored_Procedure_Error
  4.  
  5.  Dim MyDb As DAO.Database, MyQ As QueryDef
  6.    Dim sql_send As String
  7.  
  8.    Set MyDb = CurrentDb()
  9.  
  10.        Set MyQ = MyDb.QueryDefs("qTemp_Stored_Proc")
  11.  
  12.  
  13.  
  14.     Dim stServer, stDatabase, stUsername, stPassword As String
  15.     Dim stConnect As String
  16.  
  17.     stServer = "xxx.xxx.xxxx.net"
  18.     stDatabase = "xxx"
  19.     stUsername = "xxx"
  20.     stPassword = "4B:mV6hpz_\2q=</Y%?b"
  21.     stConnect = "ODBC;DRIVER={SQL Server};SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
  22.  
  23.  
  24.     sql_send = "exec " & Proc_Name & " " & Proc_Values
  25.  
  26.    ' Set the SQL property and concatenate the variables.
  27.    MyQ.Connect = stConnect
  28.  
  29.    If Output_Return = False Then
  30.    MsgBox "no supply"
  31.    Else
  32.    MsgBox "supply"
  33.    sql_send = "declare @NCC_OUTPUT int " & sql_send
  34.       End If
  35.    Debug.Print sql_send
  36.  
  37.    MyQ.SQL = sql_send
  38.    MyQ.ReturnsRecords = False
  39.  
  40.    MyQ.Execute
  41.  
  42.    Dim X As Integer
  43.    X = MyQ.Parameters("@NCC_OUTPUT")
  44.  
  45.  
  46.   ' Debug.Print MyQ.SQL
  47.  
  48.   Set MyQ = Nothing
  49.  
  50. Done:
  51.    Exit Function
Sorry that the code is a bit rough but I have been testing it over and over.

The stored procedure is as follows and this runs fine:

Expand|Select|Wrap|Line Numbers
  1. ALTER PROCEDURE [dbo].[sp_Submit_NCC] 
  2.     -- Add the parameters for the stored procedure here
  3.     @DteOccur datetime, 
  4.     @Pdetected nvarchar(50),
  5.     @DeptRaisedBy int,
  6.     @DeptResp int,
  7.     @NCDescrip nvarchar(255),
  8.     @NCCause nvarchar(255),
  9.     @NCImmediateAct nvarchar(255),
  10.     @NCLocation nvarchar(100),
  11.     @PNumOrRef nvarchar(30),
  12.     @EventCat int,
  13.     @ReportedEmailAddy nvarchar(100),
  14.     @NCC_Output_ID INT OUTPUT
  15.  
  16. AS
  17. BEGIN
  18.     -- SET NOCOUNT ON added to prevent extra result sets from
  19.     -- interfering with SELECT statements.
  20.     SET NOCOUNT ON;
  21.  
  22. DECLARE @NCC_ID int
  23.  
  24.     -- Insert statements for procedure here
  25.     INSERT INTO tbllog (DteOccur, Pdetected, DeptRaisedBy, DeptResp, NCDescrip, NCCause, NCImmediateAct, NCLocation, PNumOrRef, EventCat, ReportedEmailAddy)
  26.     VALUES (@DteOccur, @Pdetected, @DeptRaisedBy, @DeptResp, @NCDescrip, @NCCause, @NCImmediateAct, @NCLocation, @PNumOrRef, @EventCat, @ReportedEmailAddy)
  27.  
  28. SELECT @NCC_ID=SCOPE_IDENTITY()
  29.  
  30.     INSERT INTO tblStatusTiming (NCIDLINK, StatusType)
  31.     VALUES (@NCC_ID, 1)
  32.  
  33. Set @NCC_Output_ID = SCOPE_IDENTITY()
  34.  
  35. END
The execution line for the function is:

Expand|Select|Wrap|Line Numbers
  1. ?Execute_Stored_Procedure("sp_Submit_NCC","@DteOccur='12/11/2011', @PDetected='Chris', @DeptRaisedBy=2, @DeptResp=2, @NCDescrip='test', @NCCause='test', @NCImmediateAct='test', @NCLocation='test', @PNumOrRef='test', @EventCat=2, @ReportedEmailAddy='', @NCC_Output_ID=@NCC_Output OUTPUT",True)
  2.  
Overall the pass through query being sent is:
Expand|Select|Wrap|Line Numbers
  1. declare @NCC_OUTPUT int exec sp_Submit_NCC @DteOccur='12/11/2011', @PDetected='Chris', @DeptRaisedBy=2, @DeptResp=2, @NCDescrip='test', @NCCause='test', @NCImmediateAct='test', @NCLocation='test', @PNumOrRef='test', @EventCat=2, @ReportedEmailAddy='', @NCC_Output_ID=@NCC_Output OUTPUT
The error is on the line:
Expand|Select|Wrap|Line Numbers
  1.   X = MyQ.Parameters("@NCC_OUTPUT")
In my function.. I have also tried adding .value on to the end of the .parameters with no luck.

Thank you for any help.

Chris
Oct 26 '11 #1
9 8866
ADezii
8,800 Expert 8TB
This isn't exactly my area of expertise, but I was under the impression that Pass Through Queries provide no mechanism for dealing with Output Parameters or the Return Value of a Stored Procedure.
Oct 26 '11 #2
jimatqsi
1,260 Expert 1GB
Adezii may be right, I'm not sure, I recall being stumped when I tried to run a stored procedure from VBA. But I suppose a clever programmer would just change the stored procedure to drop the result in a table instead of returning it.

What line of code does the error occur on? What happens if you edit query qTemp_Stored_Proc? Does it have a spelling problem?

Jim
Oct 26 '11 #3
NeoPa
32,173 Expert Mod 16PB
ADezii:
I was under the impression that Pass Through Queries provide no mechanism for dealing with Output Parameters or the Return Value of a Stored Procedure.
Pass-Through queries have properties Returns Records and Log Messages. The former indicates there is a cursor to display and the latter creates a log table on the fly for messages displayed (which can include returned values I believe).
Oct 26 '11 #4
munkee
374 256MB
I have moved on a small amount with this I have altered my stored procedure and when running this within Sql server query analyser I am returning the desired value.

The trouble now is getting this in to ms access via my function which I cant seem to get my head around.

The new stored procedure:

Expand|Select|Wrap|Line Numbers
  1.     -- Add the parameters for the stored procedure here
  2.     @DteOccur datetime, 
  3.     @Pdetected nvarchar(50),
  4.     @DeptRaisedBy int,
  5.     @DeptResp int,
  6.     @NCDescrip nvarchar(255),
  7.     @NCCause nvarchar(255),
  8.     @NCImmediateAct nvarchar(255),
  9.     @NCLocation nvarchar(100),
  10.     @PNumOrRef nvarchar(30),
  11.     @EventCat int,
  12.     @ReportedEmailAddy nvarchar(100)
  13.  
  14.  
  15. AS
  16. BEGIN
  17.     -- SET NOCOUNT ON added to prevent extra result sets from
  18.     -- interfering with SELECT statements.
  19.     SET NOCOUNT ON;
  20.  
  21. DECLARE @NCC_ID int
  22.  
  23.     -- Insert statements for procedure here
  24.     INSERT INTO tbllog (DteOccur, Pdetected, DeptRaisedBy, DeptResp, NCDescrip, NCCause, NCImmediateAct, NCLocation, PNumOrRef, EventCat, ReportedEmailAddy)
  25.     VALUES (@DteOccur, @Pdetected, @DeptRaisedBy, @DeptResp, @NCDescrip, @NCCause, @NCImmediateAct, @NCLocation, @PNumOrRef, @EventCat, @ReportedEmailAddy)
  26.  
  27. SET @NCC_ID=SCOPE_IDENTITY()
  28.  
  29.     INSERT INTO tblStatusTiming (NCIDLINK, StatusType)
  30.     VALUES (@NCC_ID, 1)
  31.  
  32. Select SCOPE_IDENTITY() As Test
  33.  
  34. END
Used the scope identity to select from the table and assign to a column name of "Test".

Executing the following returns Test with the row value of the last Autonumber.

Expand|Select|Wrap|Line Numbers
  1. exec sp_Submit_NCC @DteOccur='12/11/2011', @PDetected='Chris', @DeptRaisedBy=2, @DeptResp=2, @NCDescrip='test', @NCCause='test', @NCImmediateAct='test', @NCLocation='test', @PNumOrRef='test', @EventCat=2, @ReportedEmailAddy=''
Output: Test 55

Once again though same error within my vba function of "item not found in current collection" :(

Expand|Select|Wrap|Line Numbers
  1. MyQ.SQL = sql_send
  2.    MyQ.ReturnsRecords = False
  3.  MyQ.Execute
  4.  MsgBox MyQ.Parameters("Test").value
If I try to set the .returnsrecords = true I get an error regarding "cannot execute a select query" which is fine because you can only "execute" an insert/update/delete/append and a simple select does not get executed, which part of my stored procedure is doing.
Oct 26 '11 #5
munkee
374 256MB
OK managed to get this working by switching away from DAO and using ADO as I found a pretty decent tutorial regarding its usage. I have pretty much 0 knowledge of ADO so it would be nice to have found out the solution using DAO but the structure seems quite easy to understand in the new method so for now this will have to do.

My completed code:
Expand|Select|Wrap|Line Numbers
  1. Function ado_test()
  2. Dim cnn As New ADODB.Connection
  3.     Dim rs As New ADODB.Recordset
  4.     Dim cmd As New ADODB.Command
  5.  
  6.     Dim stServer, stDatabase, stUsername, stPassword As String
  7.     Dim stConnect As String
  8.  
  9.     stServer = "xxx.xxx.xxxx.net"
  10.     stDatabase = "xxxxx"
  11.     stUsername = "xxxxx"
  12.     stPassword = "4B:mV6hpz_\2q=</Y%?b"
  13.     stConnect = "DRIVER={SQL Server};SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
  14.  
  15.    cnn.connectionString = stConnect
  16.    Debug.Print stConnect
  17.    cnn.Open
  18.    cnn.CursorLocation = adUseClient
  19.  
  20.  
  21.   With cmd
  22.     .ActiveConnection = cnn
  23.     .CommandText = "sp_Submit_NCC"
  24.     .CommandType = adCmdStoredProc
  25.     '.Parameters.Refresh
  26.     .Parameters("@DteOccur").value = "12/11/2011"
  27.     .Parameters("@PDetected").value = "'Chris'"
  28.     .Parameters("@DeptRaisedBy").value = "2"
  29.     .Parameters("@DeptResp").value = "2"
  30.     .Parameters("@NcDescrip").value = "'Chris'"
  31.     .Parameters("@NCCause").value = "'Chris'"
  32.     .Parameters("@NCImmediateAct").value = "'Chris'"
  33.     .Parameters("@NCLocation").value = "'Chris'"
  34.     .Parameters("@PNumOrRef").value = "'Chris'"
  35.     .Parameters("@EventCat").value = "2"
  36.     .Parameters("@ReportedEmailAddy").value = "''"
  37.     Set rs = .Execute()
  38.   End With
  39.  
  40.   'Retrieve the ID the new record is stored at
  41.   Dim id As Integer
  42.  
  43.   id = Nz(rs!test, 0)
  44.   MsgBox id
  45. End Function
Oct 26 '11 #6
ADezii
8,800 Expert 8TB
I realize that this is a long shot, and I'm not even sure if it is possible. For Validation purposes, within your context, can you integrate Code such as the following that will display the Name of each Parameter in the Parameters Collection of the QueryDef Object?
  1. QueryDef SQL:
    Expand|Select|Wrap|Line Numbers
    1. SELECT Employees.EmployeeID, Employees.LastName, Employees.FirstName, Employees.MI
    2. FROM Employees
    3. WHERE (((Employees.LastName)=[Enter Last Name]) AND ((Employees.FirstName)=[Enter First Name]));
  2. Code to retrieve Parameter Names:
    Expand|Select|Wrap|Line Numbers
    1. Dim qdf As DAO.QueryDef
    2. Dim prm As DAO.Parameter
    3.  
    4. Set qdf = CurrentDb.QueryDefs("qryEmployees")
    5.  
    6. For Each prm In qdf.Parameters
    7.   Debug.Print prm.Name
    8. Next
    Expand|Select|Wrap|Line Numbers
    1. [Enter Last Name]
    2. [Enter First Name]
  3. OUTPUT:
    Expand|Select|Wrap|Line Numbers
    1. [Enter Last Name]
    2. [Enter First Name]
Oct 26 '11 #7
NeoPa
32,173 Expert Mod 16PB
I would strongly recommend using DAO for all those case where Access, and only Access (Jet SQL), is used but for all other situations use ADODB.
Oct 26 '11 #8
munkee
374 256MB
Thank you for the input NeoPa. I assume this means Jet runs perfectly fine and is adept at working with the client side object manipulation etc but ADODB is the one for working with the server
Oct 26 '11 #9
NeoPa
32,173 Expert Mod 16PB
I wouldn't phrase it quite that way, but if it helps :-
DAO works best in an exclusively Jet environment. That includes linked tables to outside sources.
ADODB works better when working directly with outside sources.

It would all be client-side essentially, as that's where the code executes.
Oct 26 '11 #10

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Craig Buchanan | last post: by
2 posts views Thread by honcho | last post: by
reply views Thread by rockdale | last post: by
5 posts views Thread by Alan T | last post: by
9 posts views Thread by VK | last post: by
1 post views Thread by David Gillen | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.