[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 667
  • Last Modified:

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



0
vb_jonas
Asked:
vb_jonas
  • 6
  • 4
1 Solution
 
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now