Hi,

I'm current working on an Access ADP and fine tuning some of the forms and hence stumbled across a question on processing of queries in Access ADP.

In an Access ADP, where is a query processed?

Generally, for views and stored procedures, an SQL query is processed by the SQL Server. Similarly, for an ADO Command, the SQL Server will take over the processing.

How about the following case:
I have created a view called "myView" on the SQL Server.
In my form, the rowsource is set to :

SELECT * from myView WHERE x<10

In my situation, where is the processing done?
Am I right to say that the entire myView will travel to the Access ADP and Access will process the View with the WHERE Clause?

Please advise.

Thanks.

Scripts82

__________________
Scripts82

 

Nope. In a regular .mdb, the Jet Engine provides both local storage and a local query processor. With ADPs, there is no Jet engine, so there is neither local storage, nor local query processing, none, nada.

The View is processed entirely by SQL Server, and just the result set (a sort of "virtual table", a view of a subset of a table) is returned over your OLE DB connection to the ADO Cursor Engine, which creates and manages a client-side recordset that your form uses.

HTH,

Bob

Just to demonstrate the above, I created a form in an ADP and set it Row Source property to:

Select * FROM vwCategories WHERE CategoryID < 5

using the vwCategories object in the NorthwindCS SQL Server database. Then I started a trace using SQL Server Profiler an here are the partial results of just opening the form:

SQL:BatchStarting Select * FROM vwCategories WHERE CategoryID < 5
SQL:BatchCompleted Select * FROM vwCategories WHERE CategoryID < 5


The whole T-SQL statement the Row Source property is set to is processed on SQL Server, 4 records are fetched and returned to the client, where they reside in an ADO client-side recordset managed by the ADO Cursor Engine.

HTH,

Bob

Great! Thanks Bob!

Scripts82