473,382 Members | 1,290 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,382 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 4510
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: Tcs | last post by:
I've been stumped on this for quite a while. I don't know if it's so simple that I just can't see it, or it's really possible. (Obviously, I HOPE it IS possible.) I'm trying to get my queries...
2
by: Robert | last post by:
when using the following function to create a pass through query is there a way to set the query property, "Returns Rows" to no. The default is yes. Since we are planning to create the pass...
7
by: Zlatko Matić | last post by:
Let's assume that we have a database on some SQL server (let it be MS SQL Server) and that we want to execute some parameterized query as a pass.through query. How can we pass parameters to the...
3
by: Zlatko Matić | last post by:
Hello. I'm wondernig what is happennig whith saved pass-through queries nested in regular JET query if regular JET query just filtrates result by start/end date...Does pass-through query first...
0
by: Zlatko Matić | last post by:
Hi everybody! Recently I was struggling with client/server issues in MS Access/PostgreSQL combination. Although Access is intuitive and easy to use desktop database solution, many problems...
3
by: ILCSP | last post by:
Hello, I'm fairly new to the concept of running action pass through queries (insert, update, etc.) from Access 2000. I have a SQL Server 2000 database and I'm using a Access 2K database as my...
1
by: Greg Strong | last post by:
Hello All, Why would brackets be added to the SQL of a pass through query to Oracle? If I paste the debug print of the SQL statement into SQLPlus of Oracle's XE edition it works, and does NOT...
13
by: magickarle | last post by:
Hi, I got a pass-through query (that takes about 15 mins to process) I would like to integrate variables to it. IE: something simple: Select EmplID from empl_Lst where empl_lst.timestamp between...
9
by: JRough | last post by:
I tried to pass the $result from a mysql_query in a url like this line Header("Location:clm_historyXL.php?_result=".$result); but on the redirect location clm_history.php page I get an error on...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

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.