471,354 Members | 1,471 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,354 software developers and data experts.

Jet pass through query

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
Feb 15 '07 #1
2 4370
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
Feb 15 '07 #2
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
Feb 15 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Robert | last post: by
7 posts views Thread by Zlatko Matić | last post: by
3 posts views Thread by Zlatko Matić | last post: by
1 post views Thread by Greg Strong | last post: by
9 posts views Thread by JRough | last post: by
reply views Thread by XIAOLAOHU | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.