dont understand the ms access help file explaination on pass through q 3

[hengsin]

i have difficult to understand the Help file in MS Access 2000 talking about PAss Through Query. Can anyone here explain using plain english?

This is the text in the MS Access 2000 help file:

An SQL-specific query you use to send commands directly to an ODBC database server (such as Ms FoxPro). By using pass-through queries, you work directly with the tables on the server instead of having the Ms Jet database engine process the data.

 

[cheerio]

Suppose you have a table on a server database with customer information and you want to see all customers in the City of New York.

With a pass through query you send the query to the server which selects the relevant customers and sends just those across the network to you.

With a standard Access query using linked tables, Access will fetch every row in the customer table across the network, inspect it on the local machine and display those rows that you want to see. (If the City field were indexed it could just check the index but that is unlikely unless you have lots of City queries).

So a pass through query is far more efficient in terms of both network traffic and the work the server has to do. However, there are some limitations as to what you can use a pass through query for. eg forms cannot be attached to pass through queries.

In many ways a pass through query is similar to having a view created on the server.

 

[BillPower]

Hi cheerio,

I think you're misinforming hengsin, with a Pass Through query you can't specify a criteria. The result is ALL records.

I'm almost sure that Pass Through queries were designed for programmes where Access couldn't link to them permanently in the past.

Personally, I would advise hengsin to pretend Pass Through queries don't exist.

Bill

 

[jsteph]

No...
With pass-thru you *can* specify criteria, and they are extremely useful and powerful, and before ado, they were invalueable for things like running stored procedures.

You can use them as a command-line into the backend, run stored procedures, create tables, indexes, and all sorts of other actions.

A pass-thru query just sends raw sql to the backend, and returns the results, so a query with criteria will actually execute on the server and return only the rows specified.

I often base reports on pass-thrus, especially when a complex join is involved. Using JET on odbc-linked tables where a complex join is involved will cause full-table-scans on all tables involved and then do jet sql when all data arrives on the jet memory space (the client). Pass-thru is a poor-man's client server hack for Access, and it can be quite useful.
--jsteph

 

[BillPower]

Hi jsteph,

I'm all for learning and passing on my knowlege on TT and don't like giving duff information to other members.

Could you please post an example of a Pass-Through Query where you can specify criteria.

I have never been able to get the "Where" clause to work. If you can demonstrate this to me, I promise lots of thanks and a definite star for you and an apology to cheerio.

TIA

Bill

 

[jsteph]

Bill,
An example could be anything...

Select CusName From Customers Where CusID = '1000';

Note the single quotes...the crux of a pass-thru query is that it has to be written in the native language. Oracle, the backend I'm most familiar with, does not recognize the # character for dates, ie:

Select * from Customers where LastOrder = #5/8/2003#

...will fail, but:

SELECT * from Customers /*and yes, we can put comments in native sql!!!*/ WHERE LastOrder = TO_DATE('2003-05-08')

...will work.

Same for strings...the single quote is most often the text qualifier for backends, and the double quote is used as a field alias qualifier, so using double quotes (or double-double 'escaped' quotes) will fail.

Nor does Oracle use the JOIN clause--everything there is WHERE--careful use of parenthesis is needed to make sure the join is optimal with regard to the other WHERE criteria, tho the oracle query parser usually knows what you want and does the right thing.

Also

Begin;MyStoredProc('someparm','someotherparm');End;

will run that stored procedure. (set ReturnsRecords to No for these)

If you have a link to a backend you could test it, if not you could simulate one by linking another Access .mdb via odbc and run the passthru that way.

If you have a problem getting a where clause to work, post the sql and I might be able to see something overlooked--you know, another set of eyes often makes all the difference.
--jsteph

 

[BillPower]

Hi jsteph,

SELECT Customers.CustomerID
FROM Customers
WHERE (((Customers.CustomerID)="ALFKI&quot);

My connection string is:

ODBC;Description=TestSQL;DRIVER=SQL Server;SERVER=oemcomputer;APP=????????sC;WSID=OEMCOMPUTER;DATABASE=ADO_TestSQL;Network=DBMSSHRN

The message I have always got is similar to this:

ODBC -- call failed
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'ALFKI'.(#207)

Thanks for spending your time on this, I do appreciate it.

Bill

 

[jsteph]

Bill,
Remember what I said about the double-quotes for field alias--it thinks alfki is an aliased column name. Try:

SELECT Customers.CustomerID
FROM Customers
WHERE Customers.CustomerID='ALFKI';

--Jsteph

 

[hengsin]

thanks for the information. Just back from Work after few days of small break. BillPower, can your Pass Through Query working by specifing the "Where" clause?

 

[cheerio]

Just to recap:

Access transmits a pass through query to the server.
The server acts on that query.

So the syntax of the query must use the variant of SQL that applies to the server.

Access SQL is not relevant and attempts to use it will generally fail. Most servers expect single quotes rather than double quotes around text strings. Most servers expect % rather than * as a wild card - LIKE 'A%' instead of the Access LIKE "A*". The formatting of dates is another example - the # used by Access is not generally recognised.

Provided you use the relevant server variant of SQL you can send anything that the server can recognise. This can certainly include a WHERE clause. It can also include action queries that do not return data such as an UPDATE to add 5% to every salary. It can include the execution of Stored Procedures including the passing of parameters.

The main practical difficulty is that if a query is rejected because the syntax is incorrect it can be difficult to resolve. Generally I would develop a query directly on a test server using server tools such as Query Analyzer (for MS SQL Server) and then embed the working code in an Access database to be used by the ordinary users.

 

[BillPower]

Hi jsteph and cheerio.

jsteph that example worked. Many thanks.

My apologies to cheerio for the "misinforming" statement.

Thanks to both of you for your excellent explanations and showing me that I was wrong in emphatically stating that criteria cannot be set.

Do either of you know how to take my example one step further to set the criteria to a control(s) on a form:

SELECT CustomerID
FROM Customers
WHERE CustomerID = Forms!Form1!Text1;

This was my original problem some years ago, my query went like this:

AccountNo And Between StartDate and EndDate (3 Form controls)

It was never resolved after many hours of contact between myself Microsoft and Sage.

Anyway, a well deserved star for both of you.

Bill

 

[jsteph]

Bill,
Your example will work in a JET query, (but I think each element needs to be bracketed: [Forms]![Form1]![Text1] )

But for passthru, you'd need to generate the sql in code, ie:

Sub PassThruTest()
dim db as database, qd as querydef, sq as string
dim rst as recordset
set db = currentdb
set qd = db.querydefs("SomePassThru&quot

sq = " SELECT CustomerID "
sq = sq & " FROM Customers "
sq = sq & " WHERE CustomerID = " & Forms!Form1!Text1
qd.sql = sq
'Now, depending on how you want to work with the result...
docmd.openquery qd.name
'OR
set rst = qd.openrecordset
end sub

Adding the Between clause is just another line, the trick is getting into the format of the native db, with sql2000 I think the below would work
"WHERE somedate = '" & Format(forms!form1!somedate, "mm/dd/yyyy&quot & "'"

--jsteph

 

[hengsin]

maybe the relevant person can turn this to become FAQ for the benefits for other people.........

 

[cheerio]

Building queries from forms is a topic in itself as the issues arise with JET queries and server queries!

Generally text boxes are going to deliver text. You need to be clear what a field like CustomerID is in the table. If it is a number you can lift it directly into the query although you might want to validate that it is a number. If it is text you will need delimiters around it which may be " or ' depending on the SQL variant. If its a date you need suitable delimiters which are likely to be # or ' depending on SQL. If a system might be used in an international context I would avoid date formats that have different meanings in different regions. SQL server is happy to receive '1 Dec 2002' but there is ambiguity about the meaning of '1/12/2002'

When I develop, what I would normally do is use a message box or a watch variable to pick up the SQL text after it has been composed and before it has been executed. I would then attempt to execute that text directly. This eliminates SQL syntax as a cause of problems. There are other things that can go wrong such as lack of permissions on the server so it is worth trying to separate syntax from other problems.

Thanks for the stars. The key to pass through queries is to get the server to do the work rather than the local PC. If my thoughts help you to achieve that then it is very worthwhile.

 

[hengsin]

now i'm understanding what is pass through query. Thanks for the inputs and other issues arise or maybe need to take care.

 

[hengsin]

thanks for the help in Pass Through Query. Now i'm able to help the company to increase the productivity. They used the table to link to ODBC datasource then only use the local machine to excute the query to filter whethever they want. THis process usually will take up to three hours. With Pass through query, i'm able to get everything done in just 20 minutes.........

 

[cheerio]

Congratulations.