What techniques are key to Access performance with separate front-endand back-end Access implementation via a LAN?

[Bob Alston]

Most of my Access database implementations have been fairly small in
terms of data volume and number of concurrent users. So far I haven't
had performance issues to worry about. <knock on wood>

But I am curious about what techniques those of you who have done higher
volume access implementations use to ensure high performance of the
database in a multi-user 100mbps LAN implementation???

Thanks

Bob Alston

[Steve Jorgensen]

On Sun, 16 Oct 2005 13:10:17 -0500, Bob Alston <tu****************@cox.net>
wrote:

Most of my Access database implementations have been fairly small in
terms of data volume and number of concurrent users. So far I haven't
had performance issues to worry about. <knock on wood>

But I am curious about what techniques those of you who have done higher
volume access implementations use to ensure high performance of the
database in a multi-user 100mbps LAN implementation???

Thanks

Bob Alston

Honestly, if the back-end is still an MDB, there's not much different to worry
about with performance. The performance issues really change when you switch
to a server back-end, and the biggest problems you'll have with the MDB may be
reliability, not performance. Whatever you do, make sure each user runs their
own copy of the front-end MDB, and if it looks like the number of simultaneous
users is going to excede about 25, start your plan to switch to a database
Server back-end.

[Bob Alston]

Steve Jorgensen wrote:

On Sun, 16 Oct 2005 13:10:17 -0500, Bob Alston <tu****************@cox.net>
wrote:

Most of my Access database implementations have been fairly small in
terms of data volume and number of concurrent users. So far I haven't
had performance issues to worry about. <knock on wood>

But I am curious about what techniques those of you who have done higher
volume access implementations use to ensure high performance of the
database in a multi-user 100mbps LAN implementation???

Thanks

Bob Alston

Honestly, if the back-end is still an MDB, there's not much different to worry
about with performance. The performance issues really change when you switch
to a server back-end, and the biggest problems you'll have with the MDB may be
reliability, not performance. Whatever you do, make sure each user runs their
own copy of the front-end MDB, and if it looks like the number of simultaneous
users is going to excede about 25, start your plan to switch to a database
Server back-end.

Thanks Steve. Really approciate your experience.

Can you tell me what level of and frequency of reliability problems you
have experienced in "server back-end" Access only applications?

I have seen and tried to help trouble shoot some of these but I did not
develop the application and never got very far into exactly how the app
worked.

Bob

[Albert D. Kallal]

We often see posts here that a application is too slow with one user. If the
application is too slow with one user..then what can one expect when they
try and run 10 users. That is now 10 times the requirements..

A few things:

Having a table with 75k records is quite small. Lets assume you have 12
users. With a just a 100% file base system (jet), and no sql server, then
the performance of that system should really have screamed.

Before Microsoft started "really" selling sql server, they rated JET could
handle easily 50 users. We have credible reports here of people
running 100 users. however, in those cases everything must be
"perfect".

I have some applications out there with 50, or 60 HIGHLY related tables.
With 5 to 10 users on a network, response time is instant. I don't think any
form load takes more then one second. Many of those 60+ tables are highly
relational..and in the 50 to 75k records range.

So, with my 5 users..I see no reason why I can't scale to 15 users with
such small tables in the 75,000 record range.

If the application did not perform with such small tables of only 75k
records..then upsizing to sql server will do absolute nothing to fix
performance issues. In fact, in the sql server newsgroups you see weekly
posts by people who find that upgrading to sql actually slowed things down.
I even seem some very cool numbers showing that some queries where actually
MORE EFFICIENT in terms of network use by JET then sql server.
(this is rare, and not the normal case, but it does point out that ms-access
does NOT always drag the whole table to the client)

My point here is that technology will NOT solve performance problems.
However, good designs that make careful use of limited bandwidth resources
is the key here. So, if the application was not written with good
performance in mind..then you kind are stuck with a poor design!

I mean, when using a JET file share, you grab a invoice from the 75k record
table..only the one record is transferred down the network with a file share
(and, sql server will also only transfer one record). So, at this point, you
really will NOT notice any performance difference by upgrading to sql
server. There is no magic here.

Sql server is a robust and more scalable product then is JET. And, security,
backup and host of other reasons make sql server a good choice.
However, sql server will NOT solve a performance problem with dealing
with such small tables as 75k records

Of course, when efforts are made to utilize sql server, then
significant advances in performance can be realized.

I will give a few tips...these apply when using ms-access as a file
share (without a server), or even odbc to sql server:

** Ask the user what they need before you load a form!

The above is so simple, but so often I see the above concept ignored.
For example, when you walk up to a instant teller machine, does it
download every account number and THEN ASK YOU what you want to do? In
access, it is downright silly to open up form attached to a table WITHOUT
FIRST asking the user what they want! So, if it is a customer invoice, get
the invoice number, and then load up the form with the ONE record (how can
one record be slow!). When done editing the record...the form is closed, and
you are back to the prompt ready to do battle with the next customer. You
can read up on how this "flow" of a good user interface works here (and this
applies to both JET, or sql server appcltions):

http://www.members.shaw.ca/AlbertKal...rch/index.html
My only point here is restrict the form to only the ONE record the user
needs. Of course, sub-forms, and details records don't apply to this rule,
but I am always dismayed how often a developer builds a nice form, attaches
it to a large table, and then opens it..and the throws this form attached to
some huge table..and then tells the users to go have at and have fun. Don't
we have any kind of concern for those poor users? Often, the user will not
even know how to search for something ! (so, prompt, and asking the user
also makes a HUGE leap forward in usability. And, the big bonus is reduced
network traffic too!...Gosh...better and faster, and less network
traffic....what more do we want!).

** You can continue to use bound forms..but as mentioned..restrict the form
to the one record you need. You can safely open up to a single invoice, and
even continue to use the "where" clause of the openform. Bound forms are way
less work then un-bound forms...and performance is generally just is good
anyway when done right.

** Large loading of combo boxes. A combo box is good for about 100
entries. After that..you are torturing the user (what..they got to look
through 100's of entries). So, keep things like combo boxes down
to a min size. This is both faster..and MORE importantly it is
kinder to your users.

After all, at the end of the day..what we really want is to make
things easy for the users...and treat them well.. It seems that
treating the users well, and reducfing the bandwith
(amount of data) goes hand in hand. So, better applications
treat the usres well..and run faster! (this is good news!)

Last by not least, networks are a shared resource, and if you got two users
pulling data, you got 1/2 the perfoamcne. So, 10 users = 1/10 the speed.

I talk about the limited bandiwth issue here:

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal

[Steve Jorgensen]

On Sun, 16 Oct 2005 13:35:14 -0500, Bob Alston <tu****************@cox.net>
wrote:

Steve Jorgensen wrote:

On Sun, 16 Oct 2005 13:10:17 -0500, Bob Alston <tu****************@cox.net>
wrote:

Most of my Access database implementations have been fairly small in
terms of data volume and number of concurrent users. So far I haven't
had performance issues to worry about. <knock on wood>

But I am curious about what techniques those of you who have done higher
volume access implementations use to ensure high performance of the
database in a multi-user 100mbps LAN implementation???

Thanks

Bob Alston

Honestly, if the back-end is still an MDB, there's not much different to worry
about with performance. The performance issues really change when you switch
to a server back-end, and the biggest problems you'll have with the MDB may be
reliability, not performance. Whatever you do, make sure each user runs their
own copy of the front-end MDB, and if it looks like the number of simultaneous
users is going to excede about 25, start your plan to switch to a database
Server back-end.

Thanks Steve. Really approciate your experience.

Can you tell me what level of and frequency of reliability problems you
have experienced in "server back-end" Access only applications?

I have seen and tried to help trouble shoot some of these but I did not
develop the application and never got very far into exactly how the app
worked.

Bob

With a shared MDB, the worst reliability problems occur when the front-end
database is shared, so the first thing is - don't do that. Otherwise, here's
what happens as the number of users increases...

1. The odds that one or more users has a network connection problem that can
corrupt the database goes up. At a high enough number of users, that's almost
guaranteed to be an issue, and good luck trying to find out who it is.

2. It becomes harder and harder to take the database down for unscheduled
maintenance. Often, when MDB corruption happens, it's actually very minor,
and doesn't affect users who have the database open, just new users trying to
get in. Still, to fix the problem, you have to get everyone out. If that's
20 people, some of them are in meetings, and some of them are at lunch, that
can be no fun. If you're the IT admin, or if you have immediate access to the
IT admin, you can close the open file handles, but that risks losing some
users' unsaved work, and the users may then have to use task manager to get
out of Access, so they can restart it.

3. It becomes harder to take the database down for -scheduled- maintenance.
In order to prevent problems and keep performance snappy, you need to
periodically compact and repair the database. The more users, the more often
this should be done. At over 25 users, it should be done nightly. Now, with
25 users, try to find a time when no one is using the application, so you can
do the maintenace. You may think midnight would be a good time, and you might
be wrong.

[Steve Jorgensen]

On Sun, 16 Oct 2005 12:07:49 -0700, Steve Jorgensen <no****@nospam.nospam>
wrote:

....

With a shared MDB, the worst reliability problems occur when the front-end
database is shared, so the first thing is - don't do that. Otherwise, here's
what happens as the number of users increases...

I realized the above is unclear. What I meant by that is, "If you're already
doing that part right, here's what you still have to watch out for..."

[Bob Alston]

Albert D. Kallal wrote:

We often see posts here that a application is too slow with one user. If the
application is too slow with one user..then what can one expect when they
try and run 10 users. That is now 10 times the requirements..

A few things:

Having a table with 75k records is quite small. Lets assume you have 12
users. With a just a 100% file base system (jet), and no sql server, then
the performance of that system should really have screamed.

Before Microsoft started "really" selling sql server, they rated JET could
handle easily 50 users. We have credible reports here of people
running 100 users. however, in those cases everything must be
"perfect".

I have some applications out there with 50, or 60 HIGHLY related tables.
With 5 to 10 users on a network, response time is instant. I don't think any
form load takes more then one second. Many of those 60+ tables are highly
relational..and in the 50 to 75k records range.

So, with my 5 users..I see no reason why I can't scale to 15 users with
such small tables in the 75,000 record range.

If the application did not perform with such small tables of only 75k
records..then upsizing to sql server will do absolute nothing to fix
performance issues. In fact, in the sql server newsgroups you see weekly
posts by people who find that upgrading to sql actually slowed things down.
I even seem some very cool numbers showing that some queries where actually
MORE EFFICIENT in terms of network use by JET then sql server.
(this is rare, and not the normal case, but it does point out that ms-access
does NOT always drag the whole table to the client)

My point here is that technology will NOT solve performance problems.
However, good designs that make careful use of limited bandwidth resources
is the key here. So, if the application was not written with good
performance in mind..then you kind are stuck with a poor design!

I mean, when using a JET file share, you grab a invoice from the 75k record
table..only the one record is transferred down the network with a file share
(and, sql server will also only transfer one record). So, at this point, you
really will NOT notice any performance difference by upgrading to sql
server. There is no magic here.

Sql server is a robust and more scalable product then is JET. And, security,
backup and host of other reasons make sql server a good choice.
However, sql server will NOT solve a performance problem with dealing
with such small tables as 75k records

Of course, when efforts are made to utilize sql server, then
significant advances in performance can be realized.

I will give a few tips...these apply when using ms-access as a file
share (without a server), or even odbc to sql server:

** Ask the user what they need before you load a form!

The above is so simple, but so often I see the above concept ignored.
For example, when you walk up to a instant teller machine, does it
download every account number and THEN ASK YOU what you want to do? In
access, it is downright silly to open up form attached to a table WITHOUT
FIRST asking the user what they want! So, if it is a customer invoice, get
the invoice number, and then load up the form with the ONE record (how can
one record be slow!). When done editing the record...the form is closed, and
you are back to the prompt ready to do battle with the next customer. You
can read up on how this "flow" of a good user interface works here (and this
applies to both JET, or sql server appcltions):

http://www.members.shaw.ca/AlbertKal...rch/index.html
My only point here is restrict the form to only the ONE record the user
needs. Of course, sub-forms, and details records don't apply to this rule,
but I am always dismayed how often a developer builds a nice form, attaches
it to a large table, and then opens it..and the throws this form attached to
some huge table..and then tells the users to go have at and have fun. Don't
we have any kind of concern for those poor users? Often, the user will not
even know how to search for something ! (so, prompt, and asking the user
also makes a HUGE leap forward in usability. And, the big bonus is reduced
network traffic too!...Gosh...better and faster, and less network
traffic....what more do we want!).

** You can continue to use bound forms..but as mentioned..restrict the form
to the one record you need. You can safely open up to a single invoice, and
even continue to use the "where" clause of the openform. Bound forms are way
less work then un-bound forms...and performance is generally just is good
anyway when done right.

** Large loading of combo boxes. A combo box is good for about 100
entries. After that..you are torturing the user (what..they got to look
through 100's of entries). So, keep things like combo boxes down
to a min size. This is both faster..and MORE importantly it is
kinder to your users.

After all, at the end of the day..what we really want is to make
things easy for the users...and treat them well.. It seems that
treating the users well, and reducfing the bandwith
(amount of data) goes hand in hand. So, better applications
treat the usres well..and run faster! (this is good news!)

Last by not least, networks are a shared resource, and if you got two users
pulling data, you got 1/2 the perfoamcne. So, 10 users = 1/10 the speed.

I talk about the limited bandiwth issue here:

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

Thank you Steve and Albert! I really do appreciate it.

Bob

[Rick Brandt]

Bob Alston wrote:

Most of my Access database implementations have been fairly small in
terms of data volume and number of concurrent users. So far I haven't
had performance issues to worry about. <knock on wood>

But I am curious about what techniques those of you who have done
higher volume access implementations use to ensure high performance
of the database in a multi-user 100mbps LAN implementation???

Thanks

Bob Alston

As with a true Client/Server setup one of the main things is to minimize how
much data is pulled over the wire.

Never open a form exposing the full Recordset.

Proper use of indexes on all required fields.

Inhibit free-form use of the built in Find tools.

Provide searching tools that use the minimum amount of data to let the user then
choose the one (or very small number) of full records to view.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[David W. Fenton]

Steve Jorgensen <no****@nospam.nospam> wrote in
news:9k********************************@4ax.com:

3. It becomes harder to take the database down for -scheduled-
maintenance. In order to prevent problems and keep performance
snappy, you need to periodically compact and repair the database.
The more users, the more often this should be done. At over 25
users, it should be done nightly. Now, with 25 users, try to find
a time when no one is using the application, so you can do the
maintenace. You may think midnight would be a good time, and you
might be wrong.

Well, one solution to this is to implement a timer in an always-open
form that at, say 11pm informs any user who is logged on that they
have until 11:45 to finish their work. If the user doesn't dismiss
this dialog, then the code will execute at 11:45, and what it does
is walk the Forms collection, save all data in all dirty open bound
forms (including subforms) and close all the forms and then exit the
database. I've implemented this kind of code in several apps over
the years, and it works pretty well.

I've also toyed with using a message table on the server to initiate
the same process at an ad hoc time, in order to tell people in an
office that we need to take down the database, and then using the
same approach for any unattended PC as outlined above.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"Albert D. Kallal" <ka****@msn.com> wrote in
news:xjx4f.214547$tl2.80373@pd7tw3no:

I even seem some very cool numbers showing that some queries where
actually MORE EFFICIENT in terms of network use by JET then sql
server. (this is rare, and not the normal case, but it does point
out that ms-access does NOT always drag the whole table to the
client)

Access ALMOST NEVER drags the whole table across the network. It
only does so if you're sorting or selecting on an unindexed field,
but even then only if there are no criteria on indexed fields, or in
cases where you are sorting or selecting on an expression that has
to be processed client side AND have no criteria selecting on
indexed fields.

A little time with SHOWPLAN demonstrates that Jet is pretty
efficient about executing SQL.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"Albert D. Kallal" <ka****@msn.com> wrote in
news:xjx4f.214547$tl2.80373@pd7tw3no:

I mean, when using a JET file share, you grab a invoice from the
75k record table..only the one record is transferred down the
network with a file share (and, sql server will also only transfer
one record). So, at this point, you really will NOT notice any
performance difference by upgrading to sql server. There is no
magic here.

Well, it's not really true that only one record will be transferred.

Assuming you're selection on the PK, first Jet will retrieve the
data pages of the index. It may be smart enough to retrieve only a
few of the data pages (I don't know if there's B-tree type of
structure to the indexes and the way Jet retrieves them), then it
will have to retrieve the full data page where the record is stored.
That data page could contain dozens of records.

THe point is that after the index is retrieved, only as many data
pages as necessary to return the selected data will be pulled across
the wire.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Bob Alston]

David W. Fenton wrote:

"Albert D. Kallal" <ka****@msn.com> wrote in
news:xjx4f.214547$tl2.80373@pd7tw3no:

I even seem some very cool numbers showing that some queries where
actually MORE EFFICIENT in terms of network use by JET then sql
server. (this is rare, and not the normal case, but it does point
out that ms-access does NOT always drag the whole table to the
client)

Access ALMOST NEVER drags the whole table across the network. It
only does so if you're sorting or selecting on an unindexed field,
but even then only if there are no criteria on indexed fields, or in
cases where you are sorting or selecting on an expression that has
to be processed client side AND have no criteria selecting on
indexed fields.

A little time with SHOWPLAN demonstrates that Jet is pretty
efficient about executing SQL.

Would you please clarify a bit. Would the cases where Access/jet does
not bring across the entire table include the situation of a form bound
to a table, without any filter or selection, such that the form opens it
displays 1 record of xxx, that access does NOT bring the entire table
across? If correct, how does it work - when does Access bring more data
across???

Thank you!
Bob

[david epsom dot com dot au]

> Would you please clarify a bit. Would the cases where Access/jet

not bring across the entire table include the situation of a form to a
table, without any filter or selection, such that the form

Open a form in design view. Go to the "Data" tab.
Look at the "recordset type" value.

If it says "snapshot", then Access will download,
as a background task, all of the selected fields
from all of the selected records.

If it says "dynaset", then Access will download,
as a background task, a pointer to each record.

Actually, it is much more unpreditictable than that,
but you get the idea.

(david)

"Bob Alston" <tu****************@cox.net> wrote in message
news:t8D4f.17165$fE5.13109@fed1read06... David W. Fenton wrote:

"Albert D. Kallal" <ka****@msn.com> wrote in
news:xjx4f.214547$tl2.80373@pd7tw3no:

I even seem some very cool numbers showing that some queries where
actually MORE EFFICIENT in terms of network use by JET then sql
server. (this is rare, and not the normal case, but it does point
out that ms-access does NOT always drag the whole table to the
client)

Access ALMOST NEVER drags the whole table across the network. It
only does so if you're sorting or selecting on an unindexed field,
but even then only if there are no criteria on indexed fields, or in
cases where you are sorting or selecting on an expression that has
to be processed client side AND have no criteria selecting on
indexed fields. A little time with SHOWPLAN demonstrates that Jet is
pretty
efficient about executing SQL.

Would you please clarify a bit. Would the cases where Access/jet does not
bring across the entire table include the situation of a form bound to a
table, without any filter or selection, such that the form opens it
displays 1 record of xxx, that access does NOT bring the entire table
across? If correct, how does it work - when does Access bring more data
across???

Thank you!
Bob

[Keith]

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:9k********************************@4ax.com...

2. It becomes harder and harder to take the database down for unscheduled
maintenance.

3. It becomes harder to take the database down for -scheduled-
maintenance.

I've never had a problem getting (and keeping) all users out. If you design
your app for that eventuality then you should have no problem.

Keith.
www.keithwilby.com

[Steve Jorgensen]

On Mon, 17 Oct 2005 10:23:45 +0100, "Keith"
<ke*********@baeAWAYWITHITsystems.com> wrote:

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:9k********************************@4ax.com.. .

2. It becomes harder and harder to take the database down for unscheduled
maintenance.

3. It becomes harder to take the database down for -scheduled-
maintenance.

I've never had a problem getting (and keeping) all users out. If you design
your app for that eventuality then you should have no problem.

Keith.
www.keithwilby.com

It may have as much to do with the company as the application. I've worked at
at least one company where it was very hard. You had to watch like a hawk
when that last person got out, then quickly rename the back end, so no one
could get back into it. They had consultants in othe rtime zones working via
terminal server, data entry people who started at 4:30 am, and a manager who
tended to work in the middle of the night.

[Keith]

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:6i********************************@4ax.com...

I've never had a problem getting (and keeping) all users out. If you
design
your app for that eventuality then you should have no problem.

Keith.
www.keithwilby.com

It may have as much to do with the company as the application. I've
worked at
at least one company where it was very hard. You had to watch like a hawk
when that last person got out, then quickly rename the back end, so no one
could get back into it. They had consultants in othe rtime zones working
via
terminal server, data entry people who started at 4:30 am, and a manager
who
tended to work in the middle of the night.

But if your app is *designed* to allow you to boot users off the problem
never occurs. All you need is a shutdown table and some code in a hidden
form's timer event to check the table periodically for certain values and
docmd.quit if they are found. Brute force but it does work and reliably
too. No need to rename youe BE because the startup form will also test the
shutdown table and not allow users in further. OK you might get occasions
where a user is accessing your shutdown table but all other tables are
available for modification. You can also safely make a copy to compact it,
or open it exclusively.

Regards,
Keith.

[bobh]

IMO, an access application performance is based on many factors some
which you can control and others you can't, such as; pc hardware
configuration and setup, network bandwidth, server hardware
configuration and setup(files server vs data server). But, these things
can also play a roll in the 'performance' game.

I have several access97 application which have over 200+ authorized
users banging against a jet back-end which contains 450,000+ records
and to do a lookup of a contract number takes under 2 seconds and here
that's very good performance, of course, not all 200+ users have the
application open simultaneously but at any giving time 10,50,70,90
users could.

There's a word that's used allot "design" but what is 'design' well,
for me some of the things that includes are;
- split database(front-end(on users pc), back-end(all mine are 'jet' BE
sitting on the server))
- NO macros
- proper field indexing
- data normalization
- minimal to no bound forms
- maximize VBA and SQL coding
- auto backup/compacting(I use FMS's products)

even things like
- end user friendliness of your forms
- form navigation
- data entry validation
- storing derived data
can go a long way towards having a responsive, always functioning
application.
bobh.

[Albert D. Kallal]

Would you please clarify a bit. Would the cases where Access/jet does
not bring across the entire table include the situation of a form bound to
a table, without any filter or selection, such that the form opens it
displays 1 record of xxx, that access does NOT bring the entire table
across? If correct, how does it work - when does Access bring more data
across???

Access tries to do a good job. However, if you got a large table, ms-access
does NOT normally drag the whole table (but, why risk it?).
If the user hits ctrl-f to find in that large table, then often you will
start to drag the whole table. And, further, if you upgrade to sql server,
then MORE often, sql server will try and drag the whole table. The fact of
the matter is, there is NO REASON to open a form to a large table without a
"where" clause, or first finding some way to restrict the records.

again, NEVER EVER simply load up a large table to a form. Ms-access normally
does good job, and tries not to drag the whole table, but sometimes it
will, and worse, this just encourages users to use the ctrl-f to find
records. That form should only load up to ONE record, and to do otherwise is
just asking for trouble. I can ask my old grandmother if it is stupid for a
instant teller machines to download all records and THEN ask for the account
number. If my old grandmother can figure this out, then as developers we got
much less excuse here!!

Good bandwidth designs apply to sql sever, or ms-access. The reason why so
many companies and developers state that ms-access is a poor rotten tool is
because so many ms-access developers don't make any efforts to watch the
bandwidth used. The other issue is that VB, and most other environments
don't have bound forms, so they as a matter of course simply ask for the
data, and THEN load up a form. We can do much the same in ms-access, but
ms-access also makes it VERY easy to be lazy, and just load up a large form.

Bound forms in ms-access work just fine, but you should use a "where" clause
to restrict what they load....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal

[David W. Fenton]

Bob Alston <tu****************@cox.net> wrote in
news:t8D4f.17165$fE5.13109@fed1read06:

David W. Fenton wrote:

"Albert D. Kallal" <ka****@msn.com> wrote in
news:xjx4f.214547$tl2.80373@pd7tw3no:

I even seem some very cool numbers showing that some queries
where actually MORE EFFICIENT in terms of network use by JET then
sql server. (this is rare, and not the normal case, but it does
point out that ms-access does NOT always drag the whole table to
the client)

Access ALMOST NEVER drags the whole table across the network. It
only does so if you're sorting or selecting on an unindexed
field, but even then only if there are no criteria on indexed
fields, or in cases where you are sorting or selecting on an
expression that has to be processed client side AND have no
criteria selecting on indexed fields.

A little time with SHOWPLAN demonstrates that Jet is pretty
efficient about executing SQL.

Would you please clarify a bit. Would the cases where Access/jet
does not bring across the entire table include the situation of a
form bound to a table, without any filter or selection, such that
the form opens it
displays 1 record of xxx, that access does NOT bring the entire
table
across? If correct, how does it work - when does Access bring
more data across???

I'm not exactly sure how Rushmore interacts with indexes and data
pages. Rushmore is the technology that allows you to see the
beginning of a recordset before the end of the recordset has been
retrieved. I don't know if in a form bound to a large table the
first N records are retrieved and a count of the whole recordset
retrieved and then the other records not retrieved until requested.
My experience with forms bound to tables suggests to me that this is
not the case, as there's no lag in navigating to the end of the
table (viewing a datasheet is going to be basically the same thing).

I do believe that combo boxes work differently, however. Those *do*
retrieve additional data as it is requested, because with extremely
large data sets (which are a design mistake when used with a combo
box) you can feel the wait as you scroll down, and you can feel that
lag time even when retrieving the data from a local data file. But,
once the whole thing has been retrieved, it is cached, and remains
in memory, so you experience that lag only once.

Combo boxes, being non-editable (i.e., the rows you get to choose
from are non-editable) don't need to have any dynamic connection to
the data source, so it makes sense to do it this way. Forms bound to
tables need to (potentially) have all records available for editing,
and can be updated, so that works differently.

But I don't know the exact details.

Of course, I only bind to an entire table in relatively trivial
applications (i.e., small number of records), with small recordsets
and only 1-3 users.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[david epsom dot com dot au]

> pages. Rushmore is the technology that allows you to see

beginning of a recordset before the end of the recordset
????
My experience with forms bound to tables suggests to me that
not the case, as there's no lag in navigating to the end of
:~) Then you haven't done it 'right'.
retrieved. I don't know if in a form bound to a large table
first N records are retrieved and a count of the whole
Turn on 'navigation buttons' for the form. The lag between
when the first record number '1' and the total record count
'of 295' is the time taken to retrieve (as a background task)
the recordset count.

If for some reason you need to bring in the whole table, the
form won't finish painting, and the record number won't
be displayed, until the whole table is loaded, so the record
count will display at the same instant as the record number.

(david)


"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@216.196. 97.142... Bob Alston <tu****************@cox.net> wrote in
news:t8D4f.17165$fE5.13109@fed1read06:

David W. Fenton wrote:

"Albert D. Kallal" <ka****@msn.com> wrote in
news:xjx4f.214547$tl2.80373@pd7tw3no:
I even seem some very cool numbers showing that some queries
where actually MORE EFFICIENT in terms of network use by JET then
sql server. (this is rare, and not the normal case, but it does
point out that ms-access does NOT always drag the whole table to
the client)
Access ALMOST NEVER drags the whole table across the network. It
only does so if you're sorting or selecting on an unindexed
field, but even then only if there are no criteria on indexed
fields, or in cases where you are sorting or selecting on an
expression that has to be processed client side AND have no
criteria selecting on indexed fields.

A little time with SHOWPLAN demonstrates that Jet is pretty
efficient about executing SQL.

Would you please clarify a bit. Would the cases where Access/jet
does not bring across the entire table include the situation of a
form bound to a table, without any filter or selection, such that
the form opens it
displays 1 record of xxx, that access does NOT bring the entire
table
across? If correct, how does it work - when does Access bring
more data across???

I'm not exactly sure how Rushmore interacts with indexes and data
pages. Rushmore is the technology that allows you to see the
beginning of a recordset before the end of the recordset has been
retrieved. I don't know if in a form bound to a large table the
first N records are retrieved and a count of the whole recordset
retrieved and then the other records not retrieved until requested.
My experience with forms bound to tables suggests to me that this is
not the case, as there's no lag in navigating to the end of the
table (viewing a datasheet is going to be basically the same thing).

I do believe that combo boxes work differently, however. Those *do*
retrieve additional data as it is requested, because with extremely
large data sets (which are a design mistake when used with a combo
box) you can feel the wait as you scroll down, and you can feel that
lag time even when retrieving the data from a local data file. But,
once the whole thing has been retrieved, it is cached, and remains
in memory, so you experience that lag only once.

Combo boxes, being non-editable (i.e., the rows you get to choose
from are non-editable) don't need to have any dynamic connection to
the data source, so it makes sense to do it this way. Forms bound to
tables need to (potentially) have all records available for editing,
and can be updated, so that works differently.

But I don't know the exact details.

Of course, I only bind to an entire table in relatively trivial
applications (i.e., small number of records), with small recordsets
and only 1-3 users.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Bob Alston]

david epsom dot com dot au wrote:

pages. Rushmore is the technology that allows you to see
beginning of a recordset before the end of the recordset

????

My experience with forms bound to tables suggests to me that
not the case, as there's no lag in navigating to the end of

:~) Then you haven't done it 'right'.

retrieved. I don't know if in a form bound to a large table
first N records are retrieved and a count of the whole

Turn on 'navigation buttons' for the form. The lag between
when the first record number '1' and the total record count
'of 295' is the time taken to retrieve (as a background task)
the recordset count.

If for some reason you need to bring in the whole table, the
form won't finish painting, and the record number won't
be displayed, until the whole table is loaded, so the record
count will display at the same instant as the record number.

(david)


"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@216.196. 97.142...

Bob Alston <tu****************@cox.net> wrote in
news:t8D4f.17165$fE5.13109@fed1read06:

David W. Fenton wrote:

"Albert D. Kallal" <ka****@msn.com> wrote in
news:xjx4f.214547$tl2.80373@pd7tw3no:

>I even seem some very cool numbers showing that some queries
>where actually MORE EFFICIENT in terms of network use by JET then
>sql server. (this is rare, and not the normal case, but it does
>point out that ms-access does NOT always drag the whole table to
>the client)
Access ALMOST NEVER drags the whole table across the network. It
only does so if you're sorting or selecting on an unindexed
field, but even then only if there are no criteria on indexed
fields, or in cases where you are sorting or selecting on an
expression that has to be processed client side AND have no
criteria selecting on indexed fields.

A little time with SHOWPLAN demonstrates that Jet is pretty
efficient about executing SQL.
Would you please clarify a bit. Would the cases where Access/jet
does not bring across the entire table include the situation of a
form bound to a table, without any filter or selection, such that
the form opens it
displays 1 record of xxx, that access does NOT bring the entire
table
across? If correct, how does it work - when does Access bring
more data across???

I'm not exactly sure how Rushmore interacts with indexes and data
pages. Rushmore is the technology that allows you to see the
beginning of a recordset before the end of the recordset has been
retrieved. I don't know if in a form bound to a large table the
first N records are retrieved and a count of the whole recordset
retrieved and then the other records not retrieved until requested.
My experience with forms bound to tables suggests to me that this is
not the case, as there's no lag in navigating to the end of the
table (viewing a datasheet is going to be basically the same thing).

I do believe that combo boxes work differently, however. Those *do*
retrieve additional data as it is requested, because with extremely
large data sets (which are a design mistake when used with a combo
box) you can feel the wait as you scroll down, and you can feel that
lag time even when retrieving the data from a local data file. But,
once the whole thing has been retrieved, it is cached, and remains
in memory, so you experience that lag only once.

Combo boxes, being non-editable (i.e., the rows you get to choose
from are non-editable) don't need to have any dynamic connection to
the data source, so it makes sense to do it this way. Forms bound to
tables need to (potentially) have all records available for editing,
and can be updated, so that works differently.

But I don't know the exact details.

Of course, I only bind to an entire table in relatively trivial
applications (i.e., small number of records), with small recordsets
and only 1-3 users.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

Thank you to all who have responded to this thread. I really do
appreciate all the great information.

Bob

[David W. Fenton]

"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in
news:43***********************@lon-reader.news.telstra.net:

pages. Rushmore is the technology that allows you to see
beginning of a recordset before the end of the recordset

????

If you have a question, ask it. It is, indeed, Rushmore that allows
the display and editing of the beginning of a recordset before the
whole recordset has finished loading. It was introduced in Access
version 2, if I'm not mistaken, borrowed from FoxPro, which
Microsoft purchased about then.

My experience with forms bound to tables suggests to me that
not the case, as there's no lag in navigating to the end of

:~) Then you haven't done it 'right'.

Well, I've seen a lag with forms bound to SQL with joins and
criteria, but not with forms bound to tables, even extremely large
ones (100s of thousands of records).

retrieved. I don't know if in a form bound to a large table
first N records are retrieved and a count of the whole

Turn on 'navigation buttons' for the form. The lag between
when the first record number '1' and the total record count
'of 295' is the time taken to retrieve (as a background task)
the recordset count.

If for some reason you need to bring in the whole table, the
form won't finish painting, and the record number won't
be displayed, until the whole table is loaded, so the record
count will display at the same instant as the record number.

Eh?

This is only the case if you're doing something in the form's OnOpen
or OnLoad that requires the whole recordset to be loaded before it
can display the form.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[david epsom dot com dot au]

Rushmore optimises the use of multiple indexed fields using
criteria expressions including the logical AND or OR operators.

For example, Rushmore optimises use of the ID and OrderNum
indexes in the following criteria expression:

([ID]=5889) AND ([OrderNum] < 1000)
There is only the most tenuous relationship between this
and whether you can SEE the first record found before the
last record has been identified.

is only the case if you're doing something in the form's OnOpen
or OnLoad that requires the whole recordset to be loaded before
it can display the form.

There are many things which change the behaviour of Access in
complex and unexpected ways. For example, using combo boxes on
a datasheet, or re-setting a subform recordsource.

(david)
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@216.196. 97.142... "david epsom dot com dot au" <david@epsomdotcomdotau> wrote in
news:43***********************@lon-reader.news.telstra.net:

pages. Rushmore is the technology that allows you to see
beginning of a recordset before the end of the recordset

????

If you have a question, ask it. It is, indeed, Rushmore that allows
the display and editing of the beginning of a recordset before the
whole recordset has finished loading. It was introduced in Access
version 2, if I'm not mistaken, borrowed from FoxPro, which
Microsoft purchased about then.

My experience with forms bound to tables suggests to me that
not the case, as there's no lag in navigating to the end of

:~) Then you haven't done it 'right'.

Well, I've seen a lag with forms bound to SQL with joins and
criteria, but not with forms bound to tables, even extremely large
ones (100s of thousands of records).

retrieved. I don't know if in a form bound to a large table
first N records are retrieved and a count of the whole

Turn on 'navigation buttons' for the form. The lag between
when the first record number '1' and the total record count
'of 295' is the time taken to retrieve (as a background task)
the recordset count.

If for some reason you need to bring in the whole table, the
form won't finish painting, and the record number won't
be displayed, until the whole table is loaded, so the record
count will display at the same instant as the record number.

Eh?

This is only the case if you're doing something in the form's OnOpen
or OnLoad that requires the whole recordset to be loaded before it
can display the form.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[corey lawson]

David W. Fenton wrote:

I'm not exactly sure how Rushmore interacts with indexes and data
pages. Rushmore is the technology that allows you to see the
beginning of a recordset before the end of the recordset has been
retrieved. I don't know if in a form bound to a large table the
first N records are retrieved and a count of the whole recordset
retrieved and then the other records not retrieved until requested.
My experience with forms bound to tables suggests to me that this is
not the case, as there's no lag in navigating to the end of the
table (viewing a datasheet is going to be basically the same thing).
If the Jet table has a primary key (or the query includes the PK
field/s), then Rushmore grabs the data in the Primary Key index, and
uses this to build its record reference list, and grab sets of records
as needed by Access. Which only makes sense.

Not sure what it does for a large non-indexed/non-PK table. It can suck
badly with ODBC (especially with large, linked text files).

The N of M record count thing for tables only works for Jet tables,
unless you go to the last record and go back to the beginning of the
dataset (common to do with Recordset objects in VBA that are based off
of linked tables or passthrough queries, which do not return recordcount
information).

I also think that some of its tricks (i.e., quick "select count(*) from
a_table") have to do with secretly just hitting table statistics, which
is fair enough.

I do believe that combo boxes work differently, however. Those *do*
retrieve additional data as it is requested, because with extremely
large data sets (which are a design mistake when used with a combo
box) you can feel the wait as you scroll down, and you can feel that
lag time even when retrieving the data from a local data file. But,
once the whole thing has been retrieved, it is cached, and remains
in memory, so you experience that lag only once.

Combo boxes, being non-editable (i.e., the rows you get to choose
from are non-editable) don't need to have any dynamic connection to
the data source, so it makes sense to do it this way. Forms bound to
tables need to (potentially) have all records available for editing,
and can be updated, so that works differently.
Mmm...they're not very static if you have LimitToList = No

But I don't know the exact details.

Of course, I only bind to an entire table in relatively trivial
applications (i.e., small number of records), with small recordsets
and only 1-3 users.

Access makes simple things very simple, but anyone who has read a lot of
the "Access Developer's Handbook" series over time, some other things,
like programmatically stuffing data into Combo/List boxes, scraping data
from recordsets into unbound forms (and pushing changes back to the DB),
etc. are a big PITA.

[David W. Fenton]

corey lawson <co**********@ayeteatea.net> wrote in
news:11*************@corp.supernews.com:

David W. Fenton wrote:

I do believe that combo boxes work differently, however. Those
*do* retrieve additional data as it is requested, because with
extremely large data sets (which are a design mistake when used
with a combo box) you can feel the wait as you scroll down, and
you can feel that lag time even when retrieving the data from a
local data file. But, once the whole thing has been retrieved, it
is cached, and remains in memory, so you experience that lag only
once.

Combo boxes, being non-editable (i.e., the rows you get to choose
from are non-editable) don't need to have any dynamic connection
to the data source, so it makes sense to do it this way. Forms
bound to tables need to (potentially) have all records available
for editing, and can be updated, so that works differently.

Mmm...they're not very static if you have LimitToList = No

Sure they are -- here's only one hit on the back end per
initialization of the form bearing the combo box, because the data
in the combo box can't be edited.

Of course, that also means you don't see records added to the combo
box's source tables after you've opened the form unless you requery
it, but that obviously is beyond the scope of the original question,
since if you're requerying, you obviously *don't* want the contents
of any local cache of the data.

But I don't know the exact details.

Of course, I only bind to an entire table in relatively trivial
applications (i.e., small number of records), with small
recordsets and only 1-3 users.

Access makes simple things very simple, but anyone who has read a
lot of the "Access Developer's Handbook" series over time, some
other things, like programmatically stuffing data into Combo/List
boxes, scraping data from recordsets into unbound forms (and
pushing changes back to the DB), etc. are a big PITA.

I don't often use unbound forms for data editing. I think it's much
easier to just retrieve one record into a bound form, and unless
you're needing to do some tricky management of the updates of the
data, or have severe concurrency problems, an unbound form just buys
you a helluva lot of work without much in the way of benefit.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc