How do I add a paramater to a passthrough query that executes a stored procedure?

[Scotbot]

Hi, I'm using a pass-through query in Access to trigger a stored procedure which is on my MSSQL server:

USE Raptor
DECLARE     @return_value int
DECLARE @ActivityNumber int
EXEC dbo.SWACCESSResults @ActivityNumber = '140040024'

As this runs on the SQL server (because it's a pass -through query) I can't add a parameter to it.

I think it's possible to use VBA to build the SQL dynamically and therefore add the parameter but I don't know what code to use, or how to reference the results of the dynamically generated query from another (local) query?

To clarify, I have a select query which retrives some of it's data from linked tables that point to MSSQL, and some of it from the pass-through query detailed above.

I'd greatly appreciate any pointers...

Thanks!

http://www.scottwylie.co.uk

 

[pbaldy]

I'm on an iPad right now, so can't get to my code templates. Search here on QueryDef, which is the method you would use to change the SQL of your pass through query. I'll be on a computer later if you get stuck.

 

[Scotbot]

Thanks, I've had a look at QueryDef but I can't figure out how to pass its results to another query without writing to a table first - is that neccessary?

 

[jdraw]

This link may offer some general guidance. It refers to Oracle rather than MsSql, but you may see something re concepts
http://www.techonthenet.com/access/queries/passthrough3.php

 

[pbaldy]

Thanks, I've had a look at QueryDef but I can't figure out how to pass its results to another query without writing to a table first - is that neccessary?

You can just refer to it:

SELECT Blah
FROM PassThroughQueryName

I do it all the time. My code updates the SQL of the pass through query and then opens the form/report based on the second query.

 

[Exodus]

Pbaldy

I am currently looking at needing to use pass thru queries and to have a dynamic parameter. Can you give me some examples on how you edit the SQL of them?

 

[pbaldy]

I'm on an iPad right now so no access to code. I use a DAO QueryDef to open the saved pass through query and save new SQL to it.

 

[Exodus]

Sounds exactly what I'm looking for. When you have a chance would you mind sharing an example?

 

[pbaldy]

If you search on QueryDef you'll probably find several, or get close enough to figure it out.

 

[Exodus]

I have searched on querydef but have not found anything for pass thru queries. Maybe I missing it.

 

[pbaldy]

Being a pass through is irrelevant to the basic task. After creating a QueryDef variable, lets call it qdf:

qdf.SQL = "Your SQL here"

 

[Exodus]

Can this be used with a query that is already built or do I have to write it in vba?

 

[pbaldy]

As mentioned, I use it on queries that are already built.

 

[Exodus]

thank you sir I will give a go and see what I can do

 

[Scotbot]

Hi, I'm still stuck on this after revisiting it today.

I belive I can use a line of VBA code that is triggered by clicking a button on a form, and the two dates that I need to pass to the stored procedure are from date text boxes on the same form.

The code that I found and have edited to relate to my system is:

CurrentProject.Connection.Execute "Exec Raptor.dbo.SWAutoPayrollV2PopulateWages '" & 2012-09-17 & "','" & 2012-09-22 & "'"

However it gives me an error message saying "cannot find the object CurrentProject"

So - is it possible to use a simple string like the one I have shown here to execute a line of SQL to run a stored procedure with parameters, or am I running down a dead end and need a different approach? I would like to keep everything as simple as possible.

Thanks, Scott.

Hi, I'm using a pass-through query in Access to trigger a stored procedure which is on my MSSQL server:

USE Raptor
DECLARE     @return_value int
DECLARE @ActivityNumber int
EXEC dbo.SWACCESSResults @ActivityNumber = '140040024'

As this runs on the SQL server (because it's a pass -through query) I can't add a parameter to it.

I think it's possible to use VBA to build the SQL dynamically and therefore add the parameter but I don't know what code to use, or how to reference the results of the dynamically generated query from another (local) query?

To clarify, I have a select query which retrives some of it's data from linked tables that point to MSSQL, and some of it from the pass-through query detailed above.

I'd greatly appreciate any pointers...

Thanks!

http://www.scottwylie.co.uk

 

[pbaldy]

What's the goal? Is this an action query or are you going to use the result in a later Access query like you mentioned earlier?

 

[Scotbot]

Re: How do I add a parameter to a passthrough query that executes a stored procedure?

Hi,

The stored procedure doesn't return any results to Access, it runs a series of insert and update queries on the MSSQL server.

So Access doesn't need to handle any results from the SP, it just needs to trigger it and pass two parameters which are the start and end dates.

I can probably figure out the replacement of the hard coded values with the values from the form fields.

However I cannot get the command button on the form to execute the SP in the first place, which is the problem - I can't figure out what VBA is required.

Thanks!

 

[pbaldy]

In that instance I'd be more likely to use an ADO Command, but with a pass through query you'd change the SQL of the query using a QueryDef as previously mentioned, and then DoCmd.OpenQuery will run it.

 

[Exodus]

Alright I have been searching and I still don't quite understand the querydef. Let me give a little more background on what I'm trying to accomplish.
Right now I have a db that uses passthru queries for pulling information into forms and reports. Also in the database are linked access tables used to store additional information about whats in the SQL side. What I need to do is change the parameter in the stored passthru query based on a form that is always open. This would then change the information being displayed in the forms and reports.

What we have is basic event planing which the event and attendies are in SQL. But the access side is basic workflow tracking based on each event. So I need to change the event ID in the passthru queries to so multiple events can be accessed and worked. I hope this all makes sense.
So I basically want to dynamically change the event ID based on what the user is working on. But I want to do this with the stored passthru and not write the recordsouces in vba for every form.

 

[pbaldy]

After creating a QueryDef object, let's call it qdf:

qdf.SQL = "EXEC ProcName " & Forms!FormName.ControlName

which would change to SQL to

EXEC ProcName 123

if ID 123 had been chosen.