SQL PassThrough Question

[lukethegooner]

My quesion is, can I use a look up, of sorts, in an SQL Pass Through
query?

My problem is I have built an application that uses pass through
queires to retrieve information from an Oracle source DB; rather than
build long SQL statements I have several simple pass through's that I
use to populate local tables then run the queries off those tables...

A typical and simplified SQL might be like ...

SELECT field1, field2, field3 from TABLE1 where Condition1 = 1234 and
Condition2 = 5678 and Condition3 = 91011;

Condition1, 2 and 3 will differentiate between which area of the
database is being interrogated. When the project asks for a different
area I seed a master copy of the application and change Condition1, 2
and 3 to suit.

There are a few different pass throughs on separate tables that differ
in which fields are being requested, but Condition1, 2 and 3 would
always be the same, is there anyway I can have the Pass Through query
'look up' Condition1, 2 and 3 without changing it for each pass
through every time I seed a new area? This would save time, as then I
would only have to change the 'look up' source once ...?

Hope that makes sense and thanks in advance ...

Luke

[Tom van Stiphout]

On Mon, 8 Sep 2008 03:37:48 -0700 (PDT), lukethegooner
<lu***********@gmail.comwrote:

No, or at least not in the way one would with MDB's:
select * from sometable where somefield=[Give filter value:]
will not work, nor will:
select * from sometable where somefield=Forms!SomeForm!SomeControl
Reason is that a passthrough query is passed through, without
interpretation.
What WILL work is to create a querydef on the fly, with the right
where-clause. See CreateQueryDef in the help file.

-Tom.
Microsoft Access MVP

>My quesion is, can I use a look up, of sorts, in an SQL Pass Through
query?

My problem is I have built an application that uses pass through
queires to retrieve information from an Oracle source DB; rather than
build long SQL statements I have several simple pass through's that I
use to populate local tables then run the queries off those tables...

A typical and simplified SQL might be like ...

SELECT field1, field2, field3 from TABLE1 where Condition1 = 1234 and
Condition2 = 5678 and Condition3 = 91011;

Condition1, 2 and 3 will differentiate between which area of the
database is being interrogated. When the project asks for a different
area I seed a master copy of the application and change Condition1, 2
and 3 to suit.

There are a few different pass throughs on separate tables that differ
in which fields are being requested, but Condition1, 2 and 3 would
always be the same, is there anyway I can have the Pass Through query
'look up' Condition1, 2 and 3 without changing it for each pass
through every time I seed a new area? This would save time, as then I
would only have to change the 'look up' source once ...?

Hope that makes sense and thanks in advance ...

Luke

[lukethegooner]

On 8 Sep, 14:34, Tom van Stiphout <[e-mail]:

No, or at least not in the way one would with MDB's:

What WILL work is to create a querydef on the fly, with the right
where-clause. See CreateQueryDef in the help file.

Thanks for your input Tom, greatly appreciated, will look into your
suggestion ...

Luke

[Albert D. Kallal]

You can most certainly change the sql of the pass through query.

eg:
Dim qdfPass As DAO.QueryDef
Dim rst As DAO.Recordset
Set qdfPass = CurrentDb.QueryDefs("MyPass")
qdfPass.SQL = "exec sp_myProc"
qdfPass.Execute

The above is used to send "raw" commands to sql server.

And, if you want to return data, simply go:

qdfPass.SQL = "SELECT field1, field2, field3 " & _
"FROM TABLE1 " & _
"WHERE Condition1 = 1234 " & _
"AND Condition2 = 5678 " & _
"AND Condition3 = 91011;"
Set rst = qdfPass.OpenRecordset

Now, if you looking to do the above for a report, or form, I would actually
create a linked table to the stored view on the server side, and simply use
the "where" clause to open the report.

eg:
dim strWhere as string

strWhere = "Condition1 = 1234 " & _
"AND Condition2 = 5678 " & _
"AND Condition3 = 91011"

docmd.OpenReport "ReportName",acViewPreview,,strWhere

I see little if any benefit to use/build a sql pass-through query here. I
think using a simple where clause is the least amount of work (and I find
performance to that linked view works VERY well, as good as a pass-though
anyway). So, simply bind the report to that linked view on the server. Just
thinking about this, binding the report to the pass-though should also
accept the where clause also

So, the give the "where" clause idea if this is a report. And, if it is
reocrdset processing code, then the above querydef example is just fine
also...
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com