How to use output parameters in SQL Server using Access VBA ?

I am developing an Access application using Access as the front end and SQL Server as the back end database. I use the following statements to call a Stored Procedure:

DoCmd.Hourglass True
    Set com = New ADODB.Command
    With com
       .CommandTimeout = 95
       .CommandType = adCmdStoredProc
       .CommandText = "dbo.procUDLLNMod"
       '.Parameters.Append .CreateParameter("RptYear", adInteger, adParamInput, 4, intYearSP)
       .ActiveConnection = CurrentProject.Connection
       Set rstQueryFS = .Execute
    End With

The commented out line above shows an example of how I would use an Input parameter when calling the stored procedure.

How would I call this stored procedure using an output parameter named @REC_NUMBER ?
What statement would I use to execute the stored procedure using SQL Sever Enterprise ?

I know the following doesn't work:

DECLARE @REC_NUMBER int
exec dbo.procUDLLNMod @REC_NUMBER
-------------------------------------------------------
(56152 row(s) affected)

Server: Msg 208, Level 16, State 1, Procedure procUDLLNMod, Line 17
Invalid object name 'dbo.tblUDLLN'.
----------------------------------------------------------------------------------------------------------------------
The following is the Stored Procedure which would store the record count in the output parameter
@REC_NUMBER .

CREATE PROCEDURE dbo.procUDLLNMod
@REC_NUMBER INT OUTPUT
AS
If  Exists(SELECT * FROM dbo.SYSOBJECTS WHERE NAME = 'tblUDLLN' AND TYPE = 'U')
DROP TABLE tblUDLLN
SELECT DISTINCT
CASE WHEN C.TaxIDInd = '1' THEN ' ' ELSE UPPER(C.FirstName) END AS [1st First Name],
UPPER (C.MiddleInitial) AS [1st Mid],              
CASE WHEN C.TaxIDInd = '1' THEN C.FirstName ELSE UPPER(C.LastName) END AS [1st Last Name],
UPPER(C.SecondNameFirst) As [2nd First Name], UPPER(C.SecondNameMid) AS [2nd Mid], UPPER(C.SecondNameLast) AS [2nd Last Name],
UPPER(C.StreetAddr1) AS [Address 1], UPPER (C.StreetAddr2) AS [Address 2],
UPPER(C.City) AS City,
UPPER(C.ResStateCode) AS [State], C.Zip AS Zip, CASE WHEN LEN(C.SSN) = 0 THEN C.TaxID ELSE C.SSN END AS [SSN/Tax ID],  
Right(C.OfficeNumber,3) + ' ' + C.CustomerNumber AS [Account Number], C.DateOfBirth AS [Date Of Birth], C.AcctExec AS [FA Number]
INTO tblUDLLN
FROM dbo.tblCustomersNew As C  INNER JOIN dbo.tblProductsNew As P ON C.CustomerNumber = P.CustomerNumber AND C.OfficeNumber = P.OfficeNumber

SELECT @REC_NUMBER = COUNT(*) FROM dbo.tblUDLLN

GO
zimmer9Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

grendel777Commented:
Hi zimer9, I think you're close enough to feel it - I think the code below accomplishes what you need.  You just need to declare the output parameter in the command object.  It also shows how to get the output param for use in your code.

HTH!
With com
    .CommandTimeout = 95
    .CommandType = adCmdStoredProc
    .CommandText = "dbo.procUDLLNMod"
  'Input Parameters:
    .Parameters.Append .CreateParameter("RptYear", adInteger, adParamInput, 4, intYearSP)
  'Output Parameters:
    .Parameters.Append .CreateParameter("@REC_NUMBER", adInteger, adParamOutput)
    .ActiveConnection = CurrentProject.Connection
End With
 
Set rstQueryFS = com.Execute
Dim intRecNo As Integer
intRecNo = com.Parameters("@REC_NUMBER").Value

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
grendel777Commented:
Oops, one more thing - you might need to process the entire recordset before you can access the output parameter's value because it is returned at the end of the RS.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.