Is it possible using Access VBA to call a stored procedure which you cannot modify and retrieve it's result set even if it does not have external paramters ?

Is it possible using Access VBA to call a stored procedure which you cannot modify and retrieve it's result set, even if it does not have external parameters in the stored procedure ?

For example, let's assume I have a stored procedure named procGetName that issues a SQL statement as follows:

SELECT first_name, middle_name, last_name from dbo.tblName:

I call the stored procedure using the following Access VBA code:
-----------------------------------------------
DoCmd.Hourglass True
    Set com = New ADODB.Command
    With com
       .CommandTimeout = 95
       .CommandType = adCmdStoredProc
       .CommandText = "dbo.procGetName"
       .ActiveConnection = CurrentProject.Connection
       Set rstQueryFS = .Execute
    End With

Is there a way to retrieve the fields in the select clause of the stored procedure without modifying the stored procedure ?
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.

David ToddSenior DBACommented:
Hi,

I'm not exactly sure what you are after.

a)
If a procedure returns a result set, it is possible to give execute rights to an account or role that doesn't have the rights to alter the procedure.

Then this procedure can be called as above and retrieve the results set.

b)
sp_helptext procedureName will retrieve the definition of the procedure. You don't have to alter anything to get that.

HTH
  David
0
zimmer9Author Commented:
I have a stored procedure as follows:
*********************************************************************************************************
CREATE PROCEDURE getAccountRefData
(
    @application  varchar(15),
    @accountNumber char(9),
    @accountSystem int
)
AS
BEGIN
/*
***********************************************************************************
Dynamic SQL for MDA
***********************************************************************************
*/
DECLARE @execCmd varchar(250),@fixParam varchar(2000),@param1 varchar(8000)
select @fixParam = '[UniqueExecutionID ='+newid(1)+'][ServerUserID='+convert(varchar,suser_id())+'][HostName='+host_name()+']'
select @execCmd=  ' exec getAccountRefData '
select @param1 = "@application="+case when @application is null then "NULL" else "'"+rtrim(@application)+"'" end +"," +
"@accountNumber="+case when @accountNumber is null then "NULL" else "'"+rtrim(@accountNumber)+"'" end +"," +
"@accountSystem="+case when @accountSystem is null then "NULL" else rtrim(convert(varchar,@accountSystem)) end
exec('--'+@fixParam+@execCmd+@param1)

SELECT DISTINCT TA.accountNumber, TA.accountSystemRN, TA.accountStatus, TA.accountCategoryRN,countryOfCitizenship, countryOfResidence,
       TGA.fullname1, TGA.address1, TGA.city, TGA.stateCode, RN.refName, TGA.postalCode
  from rdeTAPSAccount TA,
       TAPSGeneralAddress TGA,
       TAPSCustomerDesiStreetAccount TCDSA,
       refName RN
where TA.accountNumber = TCDSA.accountNumber
  and TA.accountSystemRN = TCDSA.accountSystemRN
  and TCDSA.accountNumber = TGA.accountNumber
  and TCDSA.accountSystemRN = TGA.accountSystemRN
  and TGA.mainAddressInd = 'Y'
  and TGA.countryCode = RN.refCode
  and TA.accountNumber = @accountNumber
  and TA.accountSystemRN = @accountSystem
*********************************************************************************************************
I pass in values for 4 params as follows:
0  result  -  integer
1  @application
2 - @accountNumber
3 - @accountSystem
*********************************************************************************************************


The question is how do I pull out the values for the following 4 fields after executing
this stored procedure, which I cannot modify because I don't own this stored procedure ?

fullname1
address1
state code
postal code
0
zimmer9Author Commented:
Or how do I retrieve the entire result set of the        select ... from statement:
after executing the stored procedure ?

SELECT DISTINCT TA.accountNumber, TA.accountSystemRN, TA.accountStatus, TA.accountCategoryRN,countryOfCitizenship, countryOfResidence,
TGA.fullname1, TGA.address1, TGA.city, TGA.stateCode, RN.refName, TGA.postalCode
0
David ToddSenior DBACommented:
Hi,

I noted that you used this line
Set rstQueryFS = .Execute

Aren't the results in that recordset?

Cheers
  David
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
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.