Jet pass through query

[Bob Alston]

If you have an access form with record source being a straightforward
query and where clause in the form definition, will the query be sent to
the back end jet/Access database and executed there, withonly the
record(s) meeting the criteria being returned to the front end?

Is JetShowPlan a good tool to see that this is working?

Bob

[Tim Marshall]

Bob Alston wrote:

If you have an access form with record source being a straightforward
query and where clause in the form definition,

That is *not* a pass through query. 8) It's a Jet SQL statement in the
form recordsource instead of a saved query (which is, itself, a Jet SQL
statement).

will the query be sent to
the back end jet/Access database and executed there, withonly the
record(s) meeting the criteria being returned to the front end?

Yes.

BTW a pass through query is a query written in a database native SQL.
When you use the query builder in Access to create a query, what MS
provides you with is a (very excellent) tool with which to write Jet
SQL. I write Access applications against Oracle databases. You can
link Oracle tables to an Access mdb and write queries against them the
same way you would Jet. In this case, Access sort of translates Oracle
into Jet so you can use SQL. However, SQL is very much like English -
there are different dialects everywhere you go! The SQL that someone
would write for Oracle differs from the way you'd write it for Jet. A
"Pass through query" uses the SQL statements for the database you're
working with (in my case, Oracle) and sends it to the database without
using Jet. Therefore, while I write the following in Access Jet:

Select
WO_Number, Room_Number
From
F_WORKORDER INNER JOIN TMA_F_AREAS ON TMA_F_WORKORDER.Room_FK =
TMA_F_AREAS.Room_PK
Where
WO_DATE = #2/15/2006#

The above would work if I wrote it against linked Oracle tables.
However, if I wanted to write a pass through query, the above would be:

Select
WO_Number, Room_Number
From
F_WORKORDER, TMA_F_AREAS
where
WO_DATE = to_date(02/15/2006, 'dd/mm/yyyy') and
Room_FK = Room_PK

This would give the exact same results as the previous statement, but as
you can see its structure is different.

Where I've used "Oracle", one could be talking about other database
systems, MS SQL, revelation, mySql, etc.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

[Bob Alston]

Tim Marshall wrote:

Bob Alston wrote:

>If you have an access form with record source being a straightforward
query and where clause in the form definition,

That is *not* a pass through query. 8) It's a Jet SQL statement in the
form recordsource instead of a saved query (which is, itself, a Jet SQL
statement).

will the query be sent to
the back end jet/Access database and executed there, withonly the
record(s) meeting the criteria being returned to the front end?

Yes.

BTW a pass through query is a query written in a database native SQL.
When you use the query builder in Access to create a query, what MS
provides you with is a (very excellent) tool with which to write Jet
SQL. I write Access applications against Oracle databases. You can
link Oracle tables to an Access mdb and write queries against them the
same way you would Jet. In this case, Access sort of translates Oracle
into Jet so you can use SQL. However, SQL is very much like English -
there are different dialects everywhere you go! The SQL that someone
would write for Oracle differs from the way you'd write it for Jet. A
"Pass through query" uses the SQL statements for the database you're
working with (in my case, Oracle) and sends it to the database without
using Jet. Therefore, while I write the following in Access Jet:

Select
WO_Number, Room_Number
From
F_WORKORDER INNER JOIN TMA_F_AREAS ON TMA_F_WORKORDER.Room_FK =
TMA_F_AREAS.Room_PK
Where
WO_DATE = #2/15/2006#

The above would work if I wrote it against linked Oracle tables.
However, if I wanted to write a pass through query, the above would be:

Select
WO_Number, Room_Number
From
F_WORKORDER, TMA_F_AREAS
where
WO_DATE = to_date(02/15/2006, 'dd/mm/yyyy') and
Room_FK = Room_PK

This would give the exact same results as the previous statement, but as
you can see its structure is different.

Where I've used "Oracle", one could be talking about other database
systems, MS SQL, revelation, mySql, etc.

Thanks for setting me straight.

One final question, if I base a form on a query, and then set a where
clause in the form as it opens, will the entire query get executed on my
back end MDB and only the selected record(s) returned to the front end?

thanks