Output stored procdure in datasheet-view without using a form
Filips Benoit
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
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
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 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" <lylefa...@aim.com> wrote in message
news:1148588871.3...@i39g2000cwa.googlegroups.com...
Filips Benoit
in the forms controls.
"Rich P" <rpn...@aol.com> wrote in message
news:5zndg.26$me1....@news.uswest.net...
Lyle Fairfield
> 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
(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" <lylefa...@aim.com> wrote in message
news:1148633015....@i39g2000cwa.googlegroups.com...
Lyle Fairfield
think on that ...
Filips Benoit
Thanks for following me !
Filip
"Lyle Fairfield" <lylefa...@aim.com> wrote in message
news:1148647494.7...@j73g2000cwa.googlegroups.com...
Lyle Fairfield
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).Collect(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
DoCmd.RunCommand acCmdViewStoredProcedures
trigger error Runtime error 2046 = "The command or action
ViewStoredProcedures is not available now!"
"Lyle Fairfield" <lylefa...@aim.com> wrote in message
news:1148650906.1...@i40g2000cwc.googlegroups.com...
Lyle Fairfield
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
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" <lylefa...@aim.com> wrote in message
news:1148671656.0...@i39g2000cwa.googlegroups.com...
Lyle Fairfield
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.