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.OpenStoredProcedure "SpGetFFDBAAccounts"

There are two optional parameters:

AcView -> acViewNormal || acViewPivotChart || acViewPivotTable ||
acViewPreview
and
DataMode -> acEdit || acAdd || AcReadOnly.

I have not used these. Datamode may be useful.

[Filips Benoit]

DoCmd.OpenStoredProcedure "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.googlegr oups.com...

DoCmd.OpenStoredProcedure "SpGetFFDBAAccounts"

There are two optional parameters:

AcView -> acViewNormal || acViewPivotChart || acViewPivotTable ||
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.com> wrote in message
news:5z***************@news.uswest.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.OpenStoredProcedure "Spxxxxx" asks the parameters again.

Sub OpenStoredProcedure(ProcedureName, [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.OpenStoredProcedure "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.OpenStoredProcedure).
I want to input the SP-parameters in my form and check the datatype befor
opening the SP.
BUT with DoCmd.OpenStoredProcedure "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_QUERY_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*********************@i39g2000cwa.googlegro ups.com...

Filips Benoit wrote:

DoCmd.OpenStoredProcedure "Spxxxxx" asks the parameters again.

Sub OpenStoredProcedure(ProcedureName, [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.OpenStoredProcedure "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.googlegr oups.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.

SpGetLoanInterest 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.Execute "DROP Procedure TempProcedure"
On Error GoTo 0
TSQL = GetSQLStringFromSP("SpGetLoanInterest")
TSQL = Replace(TSQL, "@AnnualRate", "1.125")
CurrentProject.Connection.Execute "CREATE PROCEDURE TempProcedure
AS " & TSQL
DoCmd.RunCommand acCmdViewStoredProcedures
Application.RefreshDatabaseWindow
DoCmd.OpenStoredProcedure "TempProcedure"
CurrentProject.Connection.Execute "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(CurrentProject.Connection.Execute(TSQL).Colle ct(0))
End Function

Public Function GetSQLStringFromSP(ByVal SpName As String) As String
Dim spTSQL As String
Dim Position As String
spTSQL = GetSpTSQL(SpName)
Position = InStr(spTSQL, "AS") + 2
GetSQLStringFromSP = Mid$(spTSQL, Position)
GetSQLStringFromSP = Replace(GetSQLStringFromSP, "RETURN", "")
GetSQLStringFromSP = Trim(GetSQLStringFromSP)
End Function
**** end code ****

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

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

EXECUTE spLoanInterest

SET @DaysInYear = 365
SET @Period = 1
SET @DaysinYearReciprocal=(@Period/@DaysInYear)
SET @DailyRate = POWER(@AnnualRate,@DaysInYearReciprocal)
SELECT @Date = Max([Date]) FROM Loan
SET @Days = DATEDIFF(dayofyear, @Date, GETDATE())
SELECT Top 1 @Balance = Balance FROM Loan Order By [Date] desc, ID
desc
SET @Interest = POWER(@DailyRate, @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.

[Filips Benoit]

Codeline
DoCmd.RunCommand acCmdViewStoredProcedures
trigger error Runtime error 2046 = "The command or action
ViewStoredProcedures is not available now!"

"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@i40g2000cwc.googlegr oups.com...
This works here but it is TRÈS ugly! I do not offer it as anything
exemplary.

SpGetLoanInterest 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.Execute "DROP Procedure TempProcedure"
On Error GoTo 0
TSQL = GetSQLStringFromSP("SpGetLoanInterest")
TSQL = Replace(TSQL, "@AnnualRate", "1.125")
CurrentProject.Connection.Execute "CREATE PROCEDURE TempProcedure
AS " & TSQL
DoCmd.RunCommand acCmdViewStoredProcedures
Application.RefreshDatabaseWindow
DoCmd.OpenStoredProcedure "TempProcedure"
CurrentProject.Connection.Execute "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(CurrentProject.Connection.Execute(TSQL).Colle ct(0))
End Function

Public Function GetSQLStringFromSP(ByVal SpName As String) As String
Dim spTSQL As String
Dim Position As String
spTSQL = GetSpTSQL(SpName)
Position = InStr(spTSQL, "AS") + 2
GetSQLStringFromSP = Mid$(spTSQL, Position)
GetSQLStringFromSP = Replace(GetSQLStringFromSP, "RETURN", "")
GetSQLStringFromSP = Trim(GetSQLStringFromSP)
End Function
**** end code ****

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

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

EXECUTE spLoanInterest

SET @DaysInYear = 365
SET @Period = 1
SET @DaysinYearReciprocal=(@Period/@DaysInYear)
SET @DailyRate = POWER(@AnnualRate,@DaysInYearReciprocal)
SELECT @Date = Max([Date]) FROM Loan
SET @Days = DATEDIFF(dayofyear, @Date, GETDATE())
SELECT Top 1 @Balance = Balance FROM Loan Order By [Date] desc, ID
desc
SET @Interest = POWER(@DailyRate, @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.

[Lyle Fairfield]

Well Filips all this is doing is being extra careful to make sure the
list of SPROCs in the Database Window is refreshed. (Access looks in
the Database Window to find the names of Objects, even when it's
hidden; actually it's never hidden but just deactivated and all its
components made the same color as the background.)

Regardless

Try removing that line:

DoCmd.RunCommand acCmdViewStoredProcedures

entirely; it's probably redundant.

[Filips Benoit]

It didn't work but I found another solution thanks to your code.
The SP has no parameters but a tempstring as placeholder: param1, param2,
param3 etc.
I ALTER the SP 2 times: 1 to set the users parametervalues and after showing
the results I Alter the SP back to its original SP-TSQL with the tempstring
as placeholders.

? Is this a good solution ?

Thanks³,

Filip

'Alter SP-TSQL by replacing Param1 > parametervalue1 etc. (Related
parameters are set visible while selecting a SP)
TSQL = GetSQLStringFromSP(Me.CmbQuery.Column(2))
For iLoop = 1 To 9
If Me("TxtParValue" & CStr(iLoop)).Visible Then
TSQL = Replace(TSQL, "Param" & CStr(iLoop), Me("TxtParValue" &
CStr(iLoop)).value)
End If
Next iLoop
CurrentProject.Connection.Execute "ALTER PROCEDURE " &
Me.CmbQuery.Column(2) & " AS " & TSQL

'Show results
DoCmd.OpenStoredProcedure Me.CmbQuery.Column(2)

'Restore SP-TSQL = Alter to original SP-TSQL
For iLoop = 1 To 9
If Me("TxtParValue" & CStr(iLoop)).Visible Then
TSQL = Replace(TSQL, Me("TxtParValue" & CStr(iLoop)).value,
"Param" & CStr(iLoop))
End If
Next iLoop
CurrentProject.Connection.Execute "ALTER PROCEDURE " &
Me.CmbQuery.Column(2) & " AS " & TSQL


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

Well Filips all this is doing is being extra careful to make sure the
list of SPROCs in the Database Window is refreshed. (Access looks in
the Database Window to find the names of Objects, even when it's
hidden; actually it's never hidden but just deactivated and all its
components made the same color as the background.)

Regardless

Try removing that line:

DoCmd.RunCommand acCmdViewStoredProcedures

entirely; it's probably redundant.

[Lyle Fairfield]

Only you can know if it's a good solution.
Does it do what you want?
Is it fast enough?
Does it cause a mess?
If it's yes, yes, no then it's probably an OK solution.