Run stored procedure providing variables from form (1 Viewer)

[Anakardian]

Good morning

Following concerns about someone accidentally deleting the access database we have been using to crunch performance numbers, I have successfully moved the data on to an SQL server

While the database works as it is, several of the queries are running extremely slow. I therefore decided to see if a stored procedure could run the number crunching on the server instead of passing the data back and forth all the time.
I have taken the series of queries and converted them into a stored procedure that runs too fast for me to blink while giving the same results as before.

The problem I face is that I can trigger the stored procedure from the server management studio manually while supplying the variables needed thus providing the data I need to export to excel in a table for this purpose.

What I want to do is to have a form in access supply the chosen variables (like I could before) and run the stored procedure at the click of a button as part of a series of other queries.


I have looked at pass-through queries but apparently they do not take kindly to variables unless they are hardcoded.
The other solution would be to trigger it from VBA but I have not been able to find a solution I could get to work, much less understand.

Does anyone know how to run a stored procedure on an SQL server from access while also giving it the variables it needs?

Stored procedure name: spNearMissCalculation
Variables:

• @SelectedDate (date format) (taken from a form field)
• @SelectedVessel (nvarchar(max) format) (taken from a form field)
• @SelectedVesselGroup (nvarchar(max) format) (taken from a form field)

 

[Minty]

Have a look here http://www.access-programmers.co.uk/forums/showpost.php?p=1398244&postcount=6

For an example.
Best practice would be to construct the string to pass to the EXEC in a variable first.

 

[Anakardian]

Thank you Minty.

With a slight change to the way the stored procedure worked, mainly related to converting the string values coming from Access to date and such, the whole thing works brilliantly.

I ended up with this result:

Set Cn = New ADODB.Connection
Cn.Open = "Performance leadership" 'the machine data source of the SQL database. make a pass-through query to see the name to use under teh connection property.
            
spSQL = "EXEC dbo.spNearMissCalculation '"
spSQL = spSQL & [Forms]![frmSelectVessel]![cboSelectedDate] & "' , '"
spSQL = spSQL & [Forms]![frmSelectVessel]![cboSelectedVessel] & "' , '"
spSQL = spSQL & [Forms]![frmSelectVessel]![cboSelectedVesselGroup] & "' "
            
Cn.Execute (spSQL)

Cn.Close
            
Set Cn = Nothing

 

[Galaxiom]

Another alternative is to run the Stored Procedure with an ADODB Command. These support a Parameters Collection.

 

[Anakardian]

I am not sure what you refer to Galaxiom.

I found another way that was supposed to work with inputting parameters however I could not get it to work and the thread where I found it ended without a working solution.

If you could post a simple example I would appreciate it.
It is always good to know more than one way of achieving the same goal as well as why one or the other should be used.