Passing parameter to SQL Server stored procedure

[MisawaPlayer]

I have a SQL Server database along with all of its associated stored procedures. I am trying to use Access (2003) as a project, to create a front end to the database. If I simply execute one of the shown stored procedures that requires parameters, it asks me to input them.

I want to create a form where the parameters will be entered (a range of dates for example), then click a button to execute the stored procedure, passing the parameters without asking me to enter them.

I added a button to my form, and using the wizard, selected the stored procedure as the action. This runs it, and asks me for the parameters.

How do I change the VBA command that executes the procedure so that it passes parameters that I build on the fly.

For example, in SQL Server, the command is:

exec FuelMileage '4/1/2011', '4/30/2011'

I want to build this string and have it executed. How do I do this please? I know enough about VBA to get the data from the form, just not how to call the stored procedure.

Thanks for any help on this. I've been searching all over. I have seen similar questions, but none of them seem to be right for my situation.

 

[LPurvis]

Hi... Welcome to AWF.

It'll likely have been hard to interpret previous questions on this subject with your scenario. They key concept here is that you're using an Access Data Project (ADP).
Therefore executing stored procedures are pretty simple.
The connection of the current project points to the SQL database to which you're connected.

If you're running code from the form in which you have those controls to hold the parameters then it's even more simple.

CurrentProject.Connection.Execute "EXEC FuelMileage '" & Format(Me.txtDate1, "mm\/dd\/yy") & "','" & Format(Me.txtDate2, "mm\/dd\/yy") & "'"

This assumes you're performing an action in this procedure. (As it's a code only implementation - there is no UI visibility to this.)
If you're wanting to return a set of records then you really need a better medium through which to do so that the datasheet which Access opens for showing table and query (view/proc/function) results. It's both an unprofessional result and a very uncontrollable one.
If you create a form which is bound to the SP in question (i.e. set its recordsource to the stored procedure's name) then you can use the InputParameters property of that same form to refer to the controls in question.
i.e.
Forms!FormName!txtDate1;Forms!FormName!txtDate2

To be honest - I'm not wild about the functionality. It's cool and simple - but doesn't exist for List controls and so feels kinda isolated to me.
You could always open a recordset and bind that to a form.
Or dynamically set the recordsource property of the form in question.
e.g.

Forms!frmResultsForm.RecordSource = "EXEC FuelMileage '" & Format(Me.txtDate1, "mm\/dd\/yy") & "','" & Format(Me.txtDate2, "mm\/dd\/yy") & "'"

Cheers.

 

[MisawaPlayer]

Thanks for the reply. I need to read parts of it a little more carefully. I understand the basics of it though. Part of it I had already figured out since making this post. Part of it might make what I have tried a little better.

Unfortunately I have to wait till Monday to try any more. I can't afford SQL Server on my computer, so all my work has to be done at work.

Thanks again.

 

[LPurvis]

SQL Server Express is pretty good value! ;-)
(Though free isn't always good - it is in this case.)

 

[DrHardy]

Hi... Welcome to AWF.

It'll likely have been hard to interpret previous questions on this subject with your scenario. They key concept here is that you're using an Access Data Project (ADP).
Therefore executing stored procedures are pretty simple.
The connection of the current project points to the SQL database to which you're connected.

If you're running code from the form in which you have those controls to hold the parameters then it's even more simple.

CurrentProject.Connection.Execute "EXEC FuelMileage '" & Format(Me.txtDate1, "mm\/dd\/yy") & "','" & Format(Me.txtDate2, "mm\/dd\/yy") & "'"

This assumes you're performing an action in this procedure. (As it's a code only implementation - there is no UI visibility to this.)
If you're wanting to return a set of records then you really need a better medium through which to do so that the datasheet which Access opens for showing table and query (view/proc/function) results. It's both an unprofessional result and a very uncontrollable one.
If you create a form which is bound to the SP in question (i.e. set its recordsource to the stored procedure's name) then you can use the InputParameters property of that same form to refer to the controls in question.
i.e.
Forms!FormName!txtDate1;Forms!FormName!txtDate2

To be honest - I'm not wild about the functionality. It's cool and simple - but doesn't exist for List controls and so feels kinda isolated to me.
You could always open a recordset and bind that to a form.
Or dynamically set the recordsource property of the form in question.
e.g.

Forms!frmResultsForm.RecordSource = "EXEC FuelMileage '" & Format(Me.txtDate1, "mm\/dd\/yy") & "','" & Format(Me.txtDate2, "mm\/dd\/yy") & "'"

Cheers.

I have an Access 2003 MDB database that I am converting to an adp with a SQL Server 2008 R2 back end. Within the project is a form with two date entry fields and a drop down list of some dozen SQL queries based on these dates. I have converted all the queries to SQL functions and am now faced with the problem of passing the parameters. I had already tried using a form displayed as a dataset and changing the record source as suggested in this solution but the problem is that the fields returned by the functions are not the same. The common fields display correctly but missing fields show #Name and extra fields are not displayed. Is there an easy way to correct this or must I build a form for every function?

 

[LPurvis]

Hi

Rather than specifics, some general comments:

I have an Access 2003 MDB database that I am converting to an adp

Ummm did you say "to" an ADP??
The first thing to mention is that the latest release of Access (2013) no longer supports ADPs. I appreciate you say you're using 2003, but can you be certain that it won't be opened by a user in Access 2013 at any point in the application's lifetime?

Standard Access applications (MDBs and ACCDBs) can work with SQL Server data very well still. Just not with the same functionality convenience and immediacy that ADPs can.


Secondly regarding displaying returning data.
To be honest, it's not usually a huge ask that you make customised forms intended for a specific set of returning fields. That's pretty standard.
Occasionally you don't want to - in which case you can set the control source of the form controls to match the incoming data set, remove the control source from any "spare" controls - and hide them. (In a datasheet form, you'd set the control's column width to zero - or the hidden property to true, which amounts to the same thing.)

Cheers.

 

[DavidAtWork]

"I have converted all the queries to SQL functions"
have you tried using the 'isnull' sql function to cope with the blank fields, i.e.
SELECT okField, isnull(notOKfield, 'Blank') as nowOKfield .... FROM ...
You can replace 'Blank' with some default value suitable for that datatype if it's a potentially null field

David

 

[LPurvis]

I don't think Nulls are the problem here. As I understand it, it's the lack of a field being included in the resultset at all. (Hence the #Name). And perhaps extra fields not included.
Null values would be happily returned and displyed in controls with are bound to the field returning them.

Cheers.

 

[mdlueck]

and am now faced with the problem of passing the parameters.

Please see my post:

Client/Server Architecture
http://www.access-programmers.co.uk/forums/showpost.php?p=1110794&postcount=5

which I hope will be of assistance to you. In it I describe both ADO and DAO execution of Stored Procedures, and how I am utilizing both styles within Access. There is examples of passing parameters in both coding styles.