Open a form with SourceObject as a stored procedure with parameters

Hi!

How can I create a subform where the RecordSource should be a stored procedure with parameters, I don't want Access to ask the user for values for the parameters, initially the subform data should be blank. I want to specify the parameters and show the result by code.

Thank you for your advice



LVL 12
vb_jonasAsked:
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.

adraughnCommented:
There are a few steps to this.

1. You need to create a pass thru query to execute your stored procedure.
2. Add VBA code to the form that will dynamically change your sql string in the pass thru query and pass the parameters fromt he form to the pass thru query.

Where are you pulling your parameters from if the user is not going to provide them? The main form?

If you need help with the two items above let me know and I wills end you syntax.

adria
0
adraughnCommented:
by the way, your record source would be the pass thru query, not the stored procedure itself.
0
vb_jonasAuthor Commented:
Hi, thanx, yes I will let the user provide them from the main form.

I am not sure how to specify the parameters - the only way I have done it successfully is to create a recordset from a adodb.command with parameters. That way works - but when opening the form Access asks the user for the parameter values. I have set the subform RecordSource to sp_MyStoredProcedure.

In your suggestion : do you mean I format the querystring something like this strSQL="sp_MyStoredProcedure " & paramvalue1 & "," & paramvalue2 ?

Also I have problems with creating a pass through query in my adp. Could it be because I have Access 2003 + SQL Server 2005?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

adraughnCommented:
I am headed home. I will be online this evening and will provide some additional instruction. This is a fairly detailed process. I'll post tonight.

adria
0
vb_jonasAuthor Commented:
Ok, many thanks adria. My current way is to create a recordset after the user clicks "Show data"


Sub cmdShowData_Click
 
 ' not the actual code, 
 
 Dim rs as adodb.recordset
 dim cmd as adodb.command
 dim p as adodb.parameter
 
 cmd.commandtype=storedprocedure
 cmd.commandtext="sp_MyStoredProcedure"
 cmd.activeconnection = currentproject.connection 
 
 p=new adodb.parameter
 p.direction=adparaminput
 p.type=
 p.value=txtFirstValue
 p.name="FirstParameter" '  it seems that it's the parameter order that is important, not the names
 cmd.parameters.append p
 
 p=new adodb.parameter
 p.direction=adparaminput
 p.type=
 p.value=txtSecondValue
 p.name="SecondParameter"
 cmd.parameters.append p
 
 rs.open cmd,,adopenkeyset,adlockoptimistic
 
 set me.subform.form.recordset = rs
 
End Sub

Open in new window

0
adraughnCommented:
1. Create a pass thru query in Access. To do this, create a new query in design view, then from the 'Query' menu, choose 'SQL Specific', 'Pass Through'

2. In the SQL window, type: Execute YourStoredProcedureName

3. From the 'View' menu, select 'Properties' and put this in the 'ODBC Connect Str' property: (Be sure to change database name and ip address)

ODBC;DRIVER={sql server};DATABASE=YourDatabaseName;SERVER=YourServerIPAddress;Trusted_Connection=Yes;

4. Change: Execute YourStoredProcedureName to: Execute YourStoredProcedureName'Parameter1'
So if the parameter you need to pass to SQL is January, it would be:
YourStoredProcedureName'January'
I know you want it to be dynamic, but just hard code a parameter to test it.

5. Run the query to ensure that your connection is good. Test the results.

6. Change the record source of the subform to be the pass through query. Test it with the current parameters hard coded.

7. Now we need to dynamically change the parameters of the Pass Through query using VBA. You will need to add this function to your form:

Function ChangeSQL(strQry As String, strSQL As String) As String

    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Set db = CurrentDb
    Set qd = db.QueryDefs(strQry)
    qd.SQL = strSQL
    ChangeSQL = qd.SQL
    Set qd = Nothing
    Set db = Nothing
End Function

8. Then you can use this code to change your pass through query:

strOldSQL = ChangeSQL("YourPassThroughQueryName", "Execute YourStoredProcedureName" & "'" & _
                    Forms![YourFormName]![FieldContainingParameterYouWantToPass] & "'")



Make sense? If you need more help let me know. You will probably want to refresh your subform as needed. Not sure where your parameter is being updated or when, so you'll have to judge when to refresh and what event to run this code off of - or provide additional information and I will help you out.


-a
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
vb_jonasAuthor Commented:
Hi, thank you very much. I think I understand, will try in a couple of days.
0
adraughnCommented:
ok. let me know if you have any questions.
0
vb_jonasAuthor Commented:
Hi, thank you, that method will be useful for me.
0
adraughnCommented:
you're welcome.

-adria
0
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 Access

From novice to tech pro — start learning today.