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?
 
David ToddConnect With a Mentor Senior DBACommented:
Hi,

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

Aren't the results in that recordset?

Cheers
  David
0
 
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
All Courses

From novice to tech pro — start learning today.