Pass through Queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Pass through Queries

Author

Topic

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2003-02-04 : 04:16:00

I need to run a series of pass through queries in Access 97 (remember that!). They are calls to SQL server 7 stored procedures. My problem is that they need one (or more) parameter which have to be entered by the user. If I was just using a normal Access query I would just put the question in [] as normal but this doesn't work.

Does anyone have any ideas how to do this?

thanks

steve
elwoosAThotmail.com

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2003-02-04 : 09:00:44

Just make sure you select SQL Specific from the query drop-down menu and then type in. Obviously you want to pass in the params from code or whatever but initially you must hard code as it were any values that fit them.........

EXECUTE <spname> <param1>, <param2>, etc

EXECUTE usp_IndustrySectorAnalysts_sel TST1999, 4

here i have an SP called usp_IndustrySectorAnalysts_sel
it excepts 2 params which i have hard coded TST1999 and 4

this does not matter as i pass the param values in via code so they will change anyway.

You can save this pass through query in Access as eg. qry_MySQLPass

soooo from code you would pass in the necessary params like..........

This will populate a combo based on the values you pass to your pass through query.....................

'Populate the drop-down Fmg list combo on the details form
Dim qdfList As QueryDef
Dim strSQL As String

Set db = CurrentDb

Set qdfList = db.QueryDefs("qry_MySQLPass") 'passthrough query
strSQL = "EXECUTE usp_IndustrySectorAnalysts_sel " & gsUID & gintANumber 'passing in 2 global variables
qdfList.sql = strSQL

Me.cbo_FMGList.RowSource = "qry_MySQLPass"
Set qdfList = Nothing

===========
Paul

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2003-02-06 : 05:23:07

That's great

thanks paul

steve

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2003-02-10 : 06:01:55

Not sure i got this line right, you may need to separate the global variables with a comma

strSQL = "EXECUTE usp_IndustrySectorAnalysts_sel " & gsUID & gintANumber 'passing in 2 global variables

becomes

strSQL = "EXECUTE usp_IndustrySectorAnalysts_sel " & gsUID & ", " & gintANumber 'passing in 2 global variables

===========
Paul

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources