Output stored procdure in datasheet-view without using a form

[Filips Benoit]

Dear All,

How can I show the resultrecords of a SP.
I can be done by doubleclick the SPname?
But how to do it by code.

I want the following interface
In my form the user
1 selects a SP (combobox showing a userfrinly name)
2 adds the related parameters
3 and then click the show result-button

but the .execute command doen't show teh records.

I want the same output as you have doublclicking the SPname in the
objectwindow.

Thanks,

Filip

[Rich P]

Hi Filip,

I will assume you are using an Access ADP. In the ADP you can create a
form and base the Form's RecordSource on the SP. Just add the columns
that the sp returns to the Form from the field list in the Form
designer. Place the form in datasheet view. You can make it a subform
and show it from a mainform.

If you are not using an ADP but an mdb, then you use ADO to generate a
recordset. Write that recordset to a table and base a form on that
table.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Lyle Fairfield]

DoCmd.OpenStore dProcedure "SpGetFFDBAAcco unts"

There are two optional parameters:

AcView -> acViewNormal || acViewPivotChar t || acViewPivotTabl e ||
acViewPreview
and
DataMode -> acEdit || acAdd || AcReadOnly.

I have not used these. Datamode may be useful.

[Filips Benoit]

DoCmd.OpenStore dProcedure "Spxxxxx" asks the parameters again.
I would like to open the SP without asking the parametervalues again, just
pick them from the formcontrols.
In a table I store the parameters related to each SP with a usersfrindly
question.
I was hoping to be able to add new SPs without changing the interface,
without adding a form for each new SP

"Lyle Fairfield" <ly***********@ aim.com> wrote in message
news:11******** **************@ i39g2000cwa.goo glegroups.com.. .

DoCmd.OpenStore dProcedure "SpGetFFDBAAcco unts"

There are two optional parameters:

AcView -> acViewNormal || acViewPivotChar t || acViewPivotTabl e ||
acViewPreview
and
DataMode -> acEdit || acAdd || AcReadOnly.

I have not used these. Datamode may be useful.

[Filips Benoit]

I was hoping to be able to add new SPs without changing the interface,
without adding a form for each new SP and still provide the parametervaleus
in the forms controls.

"Rich P" <rp*****@aol.co m> wrote in message
news:5z******** *******@news.us west.net...

Hi Filip,

I will assume you are using an Access ADP. In the ADP you can create a
form and base the Form's RecordSource on the SP. Just add the columns
that the sp returns to the Form from the field list in the Form
designer. Place the form in datasheet view. You can make it a subform
and show it from a mainform.

If you are not using an ADP but an mdb, then you use ADO to generate a
recordset. Write that recordset to a table and base a form on that
table.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Lyle Fairfield]

Filips Benoit wrote:

DoCmd.OpenStore dProcedure "Spxxxxx" asks the parameters again.

Sub OpenStoredProce dure(ProcedureN ame, [View As AcView = acViewNormal],
[DataMode As AcOpenDataMode = acEdit])
Member of Access.DoCmd

View and DataMode are optional parameters. A Call to the Sub should not
ask for these parameters, and will not in Access 2003. Look at the
Intellisense; if the parameters are in [square brackets] they are
optional and not required.

Regardless, you should be able to use

DoCmd.OpenStore dProcedure "Spxxxxx", acViewNormal, acEdit, or whataver
parameters you want.

If you are still having problems tell us the version of Access. I've
assumed you are using an ADP.

[Filips Benoit]

Its about the SP-parameters not the parameters of the Sub
(DoCmd.OpenStor edProcedure).
I want to input the SP-parameters in my form and check the datatype befor
opening the SP.
BUT with DoCmd.OpenStore dProcedure "Spxxxxx" I can't provide the
SP-parameters to the SP and it ask them again !

I want the following interface
A form where the user
1 selects a SP (combobox showing a userfrinly SP-names stored in a table
CUSTOM_QUERY)
2 adds the related parameters ( stored in another
tableCUSTOM_QUE RY_PARAMETERS)
3 Checking the parametervalues befor opening the SP
4 Opening the SP as table and providing the SP-parameters by code.

1,2 and 3 is no problem but I want to do 4 only with code without creating a
new form for each SP so the access-inteface should not be edited for each
new SP !!!

Thanks,

Filip
"Lyle Fairfield" <ly***********@ aim.com> wrote in message
news:11******** *************@i 39g2000cwa.goog legroups.com...

Filips Benoit wrote:

DoCmd.OpenStore dProcedure "Spxxxxx" asks the parameters again.

Sub OpenStoredProce dure(ProcedureN ame, [View As AcView = acViewNormal],
[DataMode As AcOpenDataMode = acEdit])
Member of Access.DoCmd

View and DataMode are optional parameters. A Call to the Sub should not
ask for these parameters, and will not in Access 2003. Look at the
Intellisense; if the parameters are in [square brackets] they are
optional and not required.

Regardless, you should be able to use

DoCmd.OpenStore dProcedure "Spxxxxx", acViewNormal, acEdit, or whataver
parameters you want.

If you are still having problems tell us the version of Access. I've
assumed you are using an ADP.

[Lyle Fairfield]

I think I understand ... Spxxxx requires input parameters? right? I'll
think on that ...

[Filips Benoit]

right
Thanks for following me !

Filip

"Lyle Fairfield" <ly***********@ aim.com> wrote in message
news:11******** **************@ j73g2000cwa.goo glegroups.com.. .

I think I understand ... Spxxxx requires input parameters? right? I'll
think on that ...

[Lyle Fairfield]

This works here but it is TRÈS ugly! I do not offer it as anything
exemplary.

SpGetLoanIntere st has one input parameter; @AnnualInterest defaulting
to 1.08 and this is changed to 1.125 for the "running". It does not
matter if the Paramaters have default values. The entire script of the
Procedure is appended in hopes that it may help you or anyone
understand.

**** begin code ****
Option Compare Database
Option Explicit

Sub temp()
Dim TSQL As String
On Error Resume Next
CurrentProject. Connection.Exec ute "DROP Procedure TempProcedure"
On Error GoTo 0
TSQL = GetSQLStringFro mSP("SpGetLoanI nterest")
TSQL = Replace(TSQL, "@AnnualRat e", "1.125")
CurrentProject. Connection.Exec ute "CREATE PROCEDURE TempProcedure
AS " & TSQL
DoCmd.RunComman d acCmdViewStored Procedures
Application.Ref reshDatabaseWin dow
DoCmd.OpenStore dProcedure "TempProced ure"
CurrentProject. Connection.Exec ute "DROP Procedure TempProcedure"
End Sub

Public Function GetSpTSQL(ByVal SpName As String) As String
Dim TSQL As String
TSQL = "SELECT text from SysComments c JOIN SysObjects o ON c.ID =
o.ID WHERE o.Name = '" & SpName & "'"
GetSpTSQL =
Trim(CurrentPro ject.Connection .Execute(TSQL). Collect(0))
End Function

Public Function GetSQLStringFro mSP(ByVal SpName As String) As String
Dim spTSQL As String
Dim Position As String
spTSQL = GetSpTSQL(SpNam e)
Position = InStr(spTSQL, "AS") + 2
GetSQLStringFro mSP = Mid$(spTSQL, Position)
GetSQLStringFro mSP = Replace(GetSQLS tringFromSP, "RETURN", "")
GetSQLStringFro mSP = Trim(GetSQLStri ngFromSP)
End Function
**** end code ****

**** T-SQL in spGetLoanIntere st ****
ALTER PROCEDURE spGetLoanIntere st
@AnnualRate float=1.08
AS

DECLARE @Balance smallmoney
DECLARE @DailyRate float
DECLARE @Date smalldatetime
DECLARE @Days int
DECLARE @DaysInYear float
DECLARE @DaysInYearReci procal float
DECLARE @Interest smallmoney
DECLARE @Period float
DECLARE @PreviousDate smalldatetime

EXECUTE spLoanInterest

SET @DaysInYear = 365
SET @Period = 1
SET @DaysinYearReci procal=(@Period/@DaysInYear)
SET @DailyRate = POWER(@AnnualRa te,@DaysInYearR eciprocal)
SELECT @Date = Max([Date]) FROM Loan
SET @Days = DATEDIFF(dayofy ear, @Date, GETDATE())
SELECT Top 1 @Balance = Balance FROM Loan Order By [Date] desc, ID
desc
SET @Interest = POWER(@DailyRat e, @Days) * @Balance - @Balance
SET @Balance = @Balance + @Interest

SELECT [Date], [Amount], [Note], [Interest], [Balance] FROM [Loan]

UNION

SELECT
GETDATE() as DATE ,
0 AS Amount,
'Current Balance' AS Note,
@Interest AS Interest,
@Balance AS Balance

ORDER BY Date

RETURN
**** end T-SQL ****

I can't remember what this Sproc actually does; I just chose it as
something to work on.