Is It Possible To Pass Parameters to A Pass Through Query

[mcl]

My assumption is that's it's not. At work I use ODBC to connect to our
oracle database with Access 2003. There are cases where using a pass through
query runs much much faster and I then use it in a make table query to make
a local table. In access you can use brackets [] to have it ask for input.
May I assume there is no way to do anything like that in a pass through
query?

 

[Guest]

Create a PassThrough query and use code to assign the SQL to this query
filtering it with a parameter, and then run it

e.g
Dim MyVariable As Integer
MyVariable = InputBox("Please select a Number")
CurrentDb.QueryDefs("QueryName").SQL="Select * From TableName Where
FieldName =" & MyVariable

 

[Rick Brandt]

My assumption is that's it's not. At work I use ODBC to connect to our
oracle database with Access 2003. There are cases where using a pass
through query runs much much faster and I then use it in a make table
query to make a local table. In access you can use brackets [] to
have it ask for input. May I assume there is no way to do anything
like that in a pass through query?

Not in the same way no. You would have to prompt for the parameters, store them
in variables and then use them to re-write the SQL of the pass-through query.
Sounds harder than it is.

 

[mcl]

Ok, how do I do all that?
Here's the pass through query which I named sfc_obs_pass_through

Select BLKSTN,LATITUDE ,LONGITUDE ,CALLLETTER, OBSERVATIONTIME,WINDDIRECTION
,WINDSPEED ,CLOUDCEILING ,CLOUDCAVOK ,VISIBILITY ,AIRTEMPERATURE
,DEWPOINTTEMPERATURE ,SEALEVELPRESSURE ,PRECIPAMOUNT1, OBSERVATIONPERIODPP1,
PRECIPAMOUNT2, OBSERVATIONPERIODPP2 ,PASTMANUAL1 ,PASTMANUAL2
,WXPASTPERIOD1, PASTAUTOMATED1, PRESENTMANUAL1, PRESENTMANUAL2, CLOUDCOVER
,ALTIMETERSETTING ,STATIONPRESSURE, WINDGUSTSPEED from SFC_OBS where BLKSTN
= 723150 order by OBSERVATIONTIME;

I want to prompt for "BLKSTN" (Block Station Number) which is a six digit
code identifying weather stations around the world. BTW, the one hard coded
in above "723150" is for Asheville NC.

Create a PassThrough query and use code to assign the SQL to this query
filtering it with a parameter, and then run it

e.g
Dim MyVariable As Integer
MyVariable = InputBox("Please select a Number")
CurrentDb.QueryDefs("QueryName").SQL="Select * From TableName Where
FieldName =" & MyVariable


--
Good Luck
BS"D

My assumption is that's it's not. At work I use ODBC to connect to our
oracle database with Access 2003. There are cases where using a pass
through
query runs much much faster and I then use it in a make table query to
make
a local table. In access you can use brackets [] to have it ask for
input.
May I assume there is no way to do anything like that in a pass through
query?