Slow connections

[Ken]

I deal with an application running MS Access 2000. The dB
that this program connects to i sover 200MB. We have a
good 17 users in at a time. The program drags. it is
normally slow (unless you come in before anyone else gets
here) and I know most of it has to do with pushing access
too far. Are there any suggestions that someone could
provide that will help speed this up a bit? That is
besides switching to SQL? I am a networking person and
don't know much about dBs.

Ken

 

[Gina Whipp]

Right away I would start with Compact and Repair.
Make sure query joins and sorting are done on indexed fields.
Make sure the tables subdatasheets are set to [NONE]
In forms make sure you're using queries as opposed to opening the entire
table (only show what the form needs)
Check your indexes and PK's
nInstead of linking tables, in your queries use the source property.
And when I have more then 10 users I use the timer event to detect
inactivity to close the form thereby releasing the data. (Be careful with
this, give folks enough time to read!

Hope that helps!
GIna

 

[Tony Toews]

I deal with an application running MS Access 2000. The dB
that this program connects to i sover 200MB. We have a
good 17 users in at a time. The program drags. it is
normally slow (unless you come in before anyone else gets
here) and I know most of it has to do with pushing access
too far.

Nope. Pushing it too far is not the problem. The key statement is
"unless you come in before anyone else gets here." So when one user
is in the performance is fine. Thus the answer is very likely in the
first option in my standard blurb.

The three most common performance problems in Access 2000 are:
- LDB locking which a persistent recordset connection or an always
open bound form corrects (multiple users)
- sub datasheet Name property set to [Auto] should be [None]
- Track name AutoCorrect should be off

If the problem is for everyone when starting up the MDB then it likely
needs a decompile.

For more information on these, less likely causes, other tips and
links to MS KB articles visit my Access Performance FAQ page at
http://www.granite.ab.ca/access/performancefaq.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Tony Toews]

nInstead of linking tables, in your queries use the source property.

By this you mean the source database name? If so this means you've
hard coded the BE path and name in your queries. I much prefer the
flexibility which linked tables give you.

And when I have more then 10 users I use the timer event to detect
inactivity to close the form thereby releasing the data. (Be careful with
this, give folks enough time to read!

However if they are inactive then by definition they aren't access the
BE thus they aren't slowing things down.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Gina Whipp]

Tony,

I have found that when they hold a form open even if they are inactive it
tends to slow down performance. I realize they are doing nothing but it
happens so I just shut down the form. It's worked so I don't argue with it.

I got the using the source property form a 10 ways to speed up Access
seminar (really slow class) anyway, placing it as the source property in the
query properties.... not sure how this would ruin ones flexibility. Please
explain...

Thanks,
Gina

 

[Guest]

I think that Tony hit it. The problem is that I have told
the company that sent us this POS program that creating
the linked dBs might work. But instead of listening to me
they did a test on thier end. They got 25 people in the
dB without problems. Well that was on a test server, and
yes they were accessing the dB and all, but there was
nothing else running on thier server and it worked. Now
they are not listening to the fact that 20 users is too
much for access. SO, they want me to update all the
computers here to Win 2000 or XP. Thanks for your help,
but I don't think they are ever going to fix it.

Ken