Please confirm my understanding for minimizing BE to FE data transfer

[Bob Alston]

I have been trying to make sure my transfer of data from BE to FE is the
lowest possible.

I found this info in a web posting by Pat Partman, a MVP, here

http://72.14.203.104/search?q=cache:...lnk&cd=4&gl=us

Please verify that I have it correct:

1) A Form filter set in a form that has a record source as a stored
query that does not itself restrict the records will NOT reduce the data
transferred from BE to FE. Basically the query will run and transfer
all the data that meets the query criteria. Then, the filter specified
on the form will be used to restrict what is shown on the form.

2) The where argument of the open form method is combined with the
recordsource to provide a complete query..... so in this case the where
argument does limit the number of rows returned by applying the where
clause.
So it seems I should use the open form with where clause when starting a
form with a specific selection criteria or when dealing with an already
open form, dynamically change the record source with a sql statement
having a where clause included.

correct?

Bob

[Tom van Stiphout]

On Thu, 15 Feb 2007 18:41:21 -0600, Bob Alston <bo********@yahoo.com>
wrote:

You correctly paraphrased his arguments.

The way I understand the WHERE argument is a bit different: I think
the entire INDEX (if one exists) travels to the client, then the
client selects some records, and those records are then requested from
the server.

If you truly have a situation where network traffic has to be as low
as possible, you must use a server-based database such as the free SQL
Server 2005 Express Edition, and use proper techniques to benefit the
most from its ability to process requests server-side and only give
you the resulting rows.

-Tom.

>I have been trying to make sure my transfer of data from BE to FE is the
lowest possible.

I found this info in a web posting by Pat Partman, a MVP, here

http://72.14.203.104/search?q=cache:...lnk&cd=4&gl=us

Please verify that I have it correct:

1) A Form filter set in a form that has a record source as a stored
query that does not itself restrict the records will NOT reduce the data
transferred from BE to FE. Basically the query will run and transfer
all the data that meets the query criteria. Then, the filter specified
on the form will be used to restrict what is shown on the form.

2) The where argument of the open form method is combined with the
recordsource to provide a complete query..... so in this case the where
argument does limit the number of rows returned by applying the where
clause.
So it seems I should use the open form with where clause when starting a
form with a specific selection criteria or when dealing with an already
open form, dynamically change the record source with a sql statement
having a where clause included.

correct?

Bob

[Bob Alston]

Tom van Stiphout wrote:

On Thu, 15 Feb 2007 18:41:21 -0600, Bob Alston <bo********@yahoo.com>
wrote:

You correctly paraphrased his arguments.

The way I understand the WHERE argument is a bit different: I think
the entire INDEX (if one exists) travels to the client, then the
client selects some records, and those records are then requested from
the server.

If you truly have a situation where network traffic has to be as low
as possible, you must use a server-based database such as the free SQL
Server 2005 Express Edition, and use proper techniques to benefit the
most from its ability to process requests server-side and only give
you the resulting rows.

-Tom.

>I have been trying to make sure my transfer of data from BE to FE is the
lowest possible.

I found this info in a web posting by Pat Partman, a MVP, here

http://72.14.203.104/search?q=cache:...lnk&cd=4&gl=us

Please verify that I have it correct:

1) A Form filter set in a form that has a record source as a stored
query that does not itself restrict the records will NOT reduce the data
transferred from BE to FE. Basically the query will run and transfer
all the data that meets the query criteria. Then, the filter specified
on the form will be used to restrict what is shown on the form.

2) The where argument of the open form method is combined with the
recordsource to provide a complete query..... so in this case the where
argument does limit the number of rows returned by applying the where
clause.
So it seems I should use the open form with where clause when starting a
form with a specific selection criteria or when dealing with an already
open form, dynamically change the record source with a sql statement
having a where clause included.

correct?

Bob

Thanks. What you say makes a lot of sense to me as to how the process
works. I think it really is different from what I found in the article
I quoted, but makes more sense for situations involving open form and
use of filters in a form.

I did some further searching on forms and index and found several other
posting that is very similar to what you have said.

Again, thank you. I now think I understand the essence of what happens
and how I can maximize performance in a FE/BE situation.

Bob

[paii, Ron]

I may be wrong, but I always understood that only the records displayed by
the form are transmitted from BE to FE. Now if you open the form, scroll to
the last record, then set a filter; you will have transmitted all the data.

"Bob Alston" <bo********@yahoo.comwrote in message
news:H4************@newsfe06.lga...

I have been trying to make sure my transfer of data from BE to FE is the
lowest possible.

I found this info in a web posting by Pat Partman, a MVP, here

http://72.14.203.104/search?q=cache:...lnk&cd=4&gl=us

>
Please verify that I have it correct:

1) A Form filter set in a form that has a record source as a stored
query that does not itself restrict the records will NOT reduce the data
transferred from BE to FE. Basically the query will run and transfer
all the data that meets the query criteria. Then, the filter specified
on the form will be used to restrict what is shown on the form.

2) The where argument of the open form method is combined with the
recordsource to provide a complete query..... so in this case the where
argument does limit the number of rows returned by applying the where
clause.
So it seems I should use the open form with where clause when starting a
form with a specific selection criteria or when dealing with an already
open form, dynamically change the record source with a sql statement
having a where clause included.

correct?

Bob

[David W. Fenton]

Tom van Stiphout <no*************@cox.netwrote in
news:7a********************************@4ax.com:

The way I understand the WHERE argument is a bit different: I
think the entire INDEX (if one exists) travels to the client, then
the client selects some records, and those records are then
requested from the server.

I think it's only as many index pages as are needed if it's the PK
index, but probably the whole index otherwise.

But I could be completely wrong on that.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Bob Alston]

David W. Fenton wrote:

Tom van Stiphout <no*************@cox.netwrote in
news:7a********************************@4ax.com:

>The way I understand the WHERE argument is a bit different: I
think the entire INDEX (if one exists) travels to the client, then
the client selects some records, and those records are then
requested from the server.

I think it's only as many index pages as are needed if it's the PK
index, but probably the whole index otherwise.

But I could be completely wrong on that.

I just ordered:

Microsoft Jet Database Engine Programmer's Guide (Microsoft Professional
Editions) - Dan Haught

Maybe I will have a definitive answer in a week or two.

Bob

[Larry Linson]

"paii, Ron" wrote

I may be wrong, but I always understood that only
the records displayed by the form are transmitted
from BE to FE. Now if you open the form, scroll to
the last record, then set a filter; you will have
transmitted all the data.

No, that's not so, with a file-server database. Index information, at the
very least, will be transmittted in order to allow Jet to request those
specific records. If selection is not on an Index, then all the records will
have to be examined, and that examination, by definition, is on the user's
machine.

It's non-trivial, as far as I know, there's no detailed specification
published by Microsoft, and I don't think there's been an updated
publication since Dan Haught's book, mentioned earlier. IIRC, there have
been a number of updates to Jet since Dan wrote that book.

Larry Linson
Microsoft Access MVP

[paii, Ron]

"Larry Linson" <bo*****@localhost.notwrote in message
news:cnnBh.68$h8.33@trnddc05...

"paii, Ron" wrote

I may be wrong, but I always understood that only
the records displayed by the form are transmitted
from BE to FE. Now if you open the form, scroll to
the last record, then set a filter; you will have
transmitted all the data.

No, that's not so, with a file-server database. Index information, at the
very least, will be transmittted in order to allow Jet to request those
specific records. If selection is not on an Index, then all the records

will

have to be examined, and that examination, by definition, is on the user's
machine.

It's non-trivial, as far as I know, there's no detailed specification
published by Microsoft, and I don't think there's been an updated
publication since Dan Haught's book, mentioned earlier. IIRC, there have
been a number of updates to Jet since Dan wrote that book.

Larry Linson
Microsoft Access MVP

Thanks for the input.

If I open a form displaying whatever is the 1st record (Jet). Access would
only download that record from the BE or all?

[Larry Linson]

What's the RecordSource?

Please understand: I don't receive divine revelations on the subject, don't
have access to the code, and don't have a specification for Jet.

I have been told that Jet is "smarter", sometimes, than I had assumed (that
was in connection with Filters). If you have single-form View, and the
RecordSource was "SELECT * FROM YourTable", it may well display the first
Record before retrieving all the Records, and I don't know when it retrieves
the others, or how it may buffer them.

The key items are "all the data extraction and manipulation is done on the
user's machine; no Access or Jet code runs on the server, and a remote
server is used in the same manner as if the backend database were on the
local hard drive."

Larry Linson
Microsoft Access MVP
"paii, Ron" <pa**@packairinc.comwrote in message
news:le******************************@athenet.net. ..

>
"Larry Linson" <bo*****@localhost.notwrote in message
news:cnnBh.68$h8.33@trnddc05...

>"paii, Ron" wrote

> I may be wrong, but I always understood that only
the records displayed by the form are transmitted
from BE to FE. Now if you open the form, scroll to
the last record, then set a filter; you will have
transmitted all the data.

No, that's not so, with a file-server database. Index information, at the
very least, will be transmittted in order to allow Jet to request those
specific records. If selection is not on an Index, then all the records

will

>have to be examined, and that examination, by definition, is on the
user's
machine.

It's non-trivial, as far as I know, there's no detailed specification
published by Microsoft, and I don't think there's been an updated
publication since Dan Haught's book, mentioned earlier. IIRC, there have
been a number of updates to Jet since Dan wrote that book.

Larry Linson
Microsoft Access MVP

Thanks for the input.

If I open a form displaying whatever is the 1st record (Jet). Access would
only download that record from the BE or all?

[David W. Fenton]

Bob Alston <bo********@yahoo.comwrote in
news:4F************@newsfe04.lga:

David W. Fenton wrote:

>Tom van Stiphout <no*************@cox.netwrote in
news:7a********************************@4ax.com :

>>The way I understand the WHERE argument is a bit different: I
think the entire INDEX (if one exists) travels to the client,
then the client selects some records, and those records are then
requested from the server.

I think it's only as many index pages as are needed if it's the
PK index, but probably the whole index otherwise.

But I could be completely wrong on that.

I just ordered:

Microsoft Jet Database Engine Programmer's Guide (Microsoft
Professional Editions) - Dan Haught

Maybe I will have a definitive answer in a week or two.

I have the Jet 3.5 version of that and I don't believe it actually
answers the question. From the shocking things MichKa used to say,
it wouldn't surprise me if nobody knows any longer how Jet actually
works internally.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/