pass through parameter

H

hpmsnell

I think this may be answered in other questions, but I'm unable to tease it
out.

I've a pass-through query

select mem.empi,
mem.MEME_FIRST_NAME||' '||mem.MEME_LAST_NAME Member,
mem.MEME_LAST_NAME LastName,
trunc(mem.MEME_BIRTH_DT) dob,
min(addr.SBAD_ADDR1) add1,
min(addr.SBAD_ADDR2) add2,
min(addr.SBAD_CITY) city,
min(addr.SBAD_STATE) state,
substr(min(addr.SBAD_ZIP),1,5) zipcode
from hprpt.cmc_meme_member mem
inner join hprpt.cmc_sbad_addr addr
on mem.SBSB_CK = addr.SBSB_CK
group by mem.empi,
mem.MEME_FIRST_NAME||' '||mem.MEME_LAST_NAME,
mem.meme_last_name,
trunc(mem.MEME_BIRTH_DT)
;

Can I add a prompt criterion or a reference to a Forms!myform!myobject.value
field to this pass though so that the processing takes place on the dbms side
(Oracle)? Right now, my application acts like it's bringing the queried
tables (very large) local.

thanks,
hpmsnell
 
J

John W. Vinson

Can I add a prompt criterion or a reference to a Forms!myform!myobject.value
field to this pass though so that the processing takes place on the dbms side
(Oracle)? Right now, my application acts like it's bringing the queried
tables (very large) local.
Click to expand...

No. Oracle has no way to peek into your computer and see what the paramter
value should be!

For a passthrough query you'll need to construct the SQL string in code,
incorporating the parameter, and run that query.
 
H

hpmsnell

Thanks

Is there any code that you can suggest?

The application is based on several odbc-linked tables. I pass parameters
via prompts or fields in forms. The application works great most of the
time, but is subject to odbc nightmares whenever the background tables are
updated. Right now, I can enter a crude value into the criteria row of my
query. When I try to do the same through a form field, the query hangs.

Any suggestions?
 
H

hpmsnell

Sorry, dude. Let me clarify. A few months back someone wrote a question
similar to mine (I think). But I'm uncertain how to apply the answer which
sounds like it worked to my passthrough query.

Here's the past conversation:
tacos said:
I created a form where the user will type in a ID number. I want to
pass this ID number into my pass through query to bring back records
only associated with this ID. I tried the following:

ID = Forms!Form1!txtID.value

or

ID = '" & Forms!Form1!txtID.value & "'

I can't get anything to work. Is it even possible to do this?

Thanks
Click to expand...

You have to re-write the SQL for the passthrough query and have the new SQL
include the value (not a reference).

CurrentDB.QueryDefs("QueryName").sql = "Some New SQL " &
Forms!Form1!txtID.value & " Some More SQL"

thanks
hpmsnell
 
M

Michael Gramelspacher

Sorry, dude. Let me clarify. A few months back someone wrote a question
similar to mine (I think). But I'm uncertain how to apply the answer which
sounds like it worked to my passthrough query.

Here's the past conversation:


You have to re-write the SQL for the passthrough query and have the new SQL
include the value (not a reference).

CurrentDB.QueryDefs("QueryName").sql = "Some New SQL " &
Forms!Form1!txtID.value & " Some More SQL"

thanks
hpmsnell
Click to expand...

First off, I am rather a novice using SQL Server with Access. I would think
that your query would be a View on the server. Then you create a link to the
view and the view appears as a table in Access. At that point you can just
write queries against it like you would against any Access table.

I recently moved a database to SQL Server using the Migration Assistant. Now I
have 50+ views appearing as tables in Access, 13 of which are updateable. I have
only one Procedure which I run using the RunPassThruQuery function from
MGFoster. I have yet to discover the usefulness of pass through queries for
anything other than running stored procedures on the server. Admittedly, I
still have a lot to learn.
 
Top