Passing Parameters to Pass Through Query

OK - I'm still working on this pass through query. I have a stored procedure on a SQL Server and I have several parameters associated with the stored procedure. I can get the pass through query to work great when I send the parameters directly to the query. For instance, when I say ...

EXECUTE spBuildLocalDataBudgetDet_EMEA_CL '17', '3', '2007', 'strang', '2'

I get all the records that I expect to get - it's great.

But I save that execution as a query so that I can refer to it and use it to build temporary tables for reporting, etc... So I don't want to "hard code" the parameters in there. I have a function called GetGvar that allows me to set variables throughout the application and then call them whenever I need them. I was hoping to use them in this case - like so ...

EXECUTE spBuildLocalDataBudgetDet_EMEA_CL @CountryID = getgvar("country"), @QuarterID = getgvar("qtrnum"), @nYear = getgvar("year"), @rusername = getgvar("username"), @Geo = getgvar("geo")

But it's not working. How do I dynamically send the parameters to this saved pass through query? Those parameters will be different based on whoever has logged on to the application, etc...

HELP! :confused:

Thanks,

PamelaDV

Hi PamelaDV,
What I do is write that pass through query each time, just before I execute it. So, rather than try to send parameters to the Access query (pass through), I write the SQL that I want Access to pass through to SQL Server to execute, save the querydef, then execute it. This only works if you have the Access front end database on each user's PC. If everyone is sharing one copy from a server, I would create a separate querydef for each user. Therefore, this query if I were going to execute it would be named spBuildLocalDataBudgetDet_EMEA_CL_vic and the one you would execute would be spBuildLocalDataBudgetDet_EMEA_CL_pamela.
I believe that Access does NOTHING with a pass through query except pass it through just the way the SQL is written. So, the way you have written yours is just what SQL Server is getting, and SQL Server has no idea what to do with your getgvar function.
If you don't know how to deal with writing out the SQL to a querydef, repost and I'll go through that.
Hope this helps,
Vic

Each user does have their own front end - so I can just write it as I go.

Vic - if you could help me get started. I see where you are going and I understand but for some reason I am having trouble getting started here. Let me see if this is in the right direction:

Dim strsql as string

strsql = "EXECUTE spBuildLocalDataBudgetDet_EMEA_CL '" & getgvar("country") & "', '" & getgvar("qtrnum") & "', '" & getgvar("year") & "', '" & getgvar("username") & "', '" & getgvar("geo") & "' "

db.execute strsql

Is that right? If I want to then take that strsql and assign it to a recordset so that I can manipulate it - where would I go from there? Can I say ...
Dim rstSQL = recordset

rstSQL = db.openrecordset(strsql)

Would that work? Do I have to do the db.execute? Or can I just assign it as an openrecordset? Or should I do it as a querydef first and then assign it as a recordset. If so - can you give me some direction there?

Your help is wonderfully appreciated.

Thanks,

PamelaDV

Code:

---

Dim qdef As DAO.QueryDef
  Dim rs as DAO.Recordset
  Set qdef = CurrentDb.QueryDefs("spAnySP")
  ...
'Build your strSQL here
  ...
  strSQL = "exec " & ThisSPName & strSQL
  qdef.sql = strSQL
  qdef.ReturnsRecords = True
  Set rs = CurrentDB().OpenRecordset("spAnySP", dbOpenDynaset)
  ...
  ...
  ...
  Set qdef = Nothing
  Set rs = Nothing

---

The variable "ThisSPName" will hold the name of the stored procedure you want to run on SQL Server. The name you are using to run any SP from Access is "spAnySP". The other thing I have done, is queries that I already have in Access, that I have converted to SQL Server, I continue to actually change those pass through queries in Access, rather than using this generic, re-usable query. For example, I have a query (now SP) that will return the records for all labor activity for a certain job. The Stored Procedure is Named "spTotalLaborActivityByJob", and the Access query name is "qryTotalLaborActivityByJob". The Access query used to look to a job number on a hidden form, so what I do now (reference the code above) is to re-build the SQL for query "qryTotalLaborActivityByJob". So the code will look like this:

Code:

---

  qdef.SQL = "exec spTotalLaborActivityByJob " _ & Forms!frmUtility!JobID

---

Within the form that is going to display the total labor activity for a job, I then use "qryTotalLaborActivityByJob" as the Record Source for that form.
I think this has all the correct pieces in it. If not, post again and we'll fix it.
Vic

Vic - thank you so much for your patience in walking me through this. What you are saying is making a lot of sense but I am still tripping up over setting the recordset part. Here is what I have so far - please show me where I am missing the mark.

Set qdef = CurrentDb.QueryDefs("spBuildTmpSelectedCountries")

strsql = "'" & Country & "', '" & Qtr & "', '" & fy & "', '" & User & "', '" & strgeo & "'"

strsql = "EXECUTE spBuildTmpSelectedCountries " & strsql

qdef.SQL = strsql

qdef.ReturnsRecords = True

Set rsTmpTblSelectedCountries = CurrentDb().OpenRecordset("spBuildTmpSelectedCount ries", dbOpenDynaset)


I set the variables for country and qtr and such earlier in the code just because I am referring to them repeatedly throughout this one particular function.

Everything works great until I get to setting the recordset. I know I have it wrong. I tried setting it to strsql and that didn't work either. Can i set it to the qdef?? I feel like I'm missing a piece of the puzzle somewhere in here - like I'm not doing something elementary - skipping a step somewhere.

I didn't quite understand your use of "spAnySP" in your previous example - I apologize if my blonde roots are showing through. As you can see - this is my first time working with stored procedures. I've done a lot of work with SQL tables, just not with stored procedures. I'm anxious to see if I can get some good performance increases from doing it this way.

Thanks again for your patience in running through this with me. If I can just figure out how to assign that recordset now - I'll be on my way.

Thanks,

PamelaDV

I think it might be your single quotes that you have put into the pass through query. So, if your initial setting of strsql was:

Code:

---

strsql = Country & ", " & Qtr & ", " & fy & ", " & User & ", " & strgeo

---

I think it would work.
I created this little test routine and it worked. I set a breakpoint at RetVal = 5. (RetVal is a global work variable for all my databases.)

Code:

---

Sub TestingSPCode()
  Dim rs As DAO.Recordset
  Set rs = CurrentDb().OpenRecordset("spTotalCostsByJob", dbOpenDynaset)
  RetVal = 5
  Set rs = Nothing
End Sub

---

When the routine stopped, I then tested (using the Immediate window) to make sure I had values in the fields of my first record of the recordset. I did have values, so I know it was working.
Please let me know if this helps.
Vic

VIC - after playing a little bit with it - I think I have it!

Thank you so much for your help. I have a ton of this stuff to do! So I might be back for more suggestions. You have been a life saver.

Thanks,

PamelaDV

You are welcome. This is a big step from Access to SQL Server, and I'm not very far ahead of you in this processes, but it has been a great learning experience!
Vic