When Multi-User dbs Slows Down

[Denise]

Any suggestions to speed up my dbs? When single user, it is fast.

The database has front-end (saved in folder A), back-end (saved in folder
B), and look-up tables (saved in folder C).

The back-end table fields are indexed.

A dummy table was created and is opened with a hidden form whenever the dbs
is opened.

Back-end tables have saved relationship. Should look-up tables be linked
and included in the relationship?

What can I do to improve the speed?

I'm in the middle of developing another database and would like it to use
same look-up tables. It's also a multi-user dbs.

Thanks for your advice.

 

[John Spencer MVP]

Try including the lookup tables in the backend.

If the tables are not in the same database then you CANNOT set up
relationships between them. Beyond that take a look at Tony Toews' site

Quote from Tony Toews
The three most common performance problems in Access 2000 are:
- LDB locking which a persistent recordset connection fixes
- sub datasheet Name property set to [Auto] should be [None]
- Track name AutoCorrect should be off
(Tools: Options: General Uncheck all name AutoCorrect features)
Other reasons are
- New format of Access 2000 MDB
- Place backend MDB on the root of the network share rather than several
folders down
- Shorten the name of the backend MDB
- Miscellaneous Performance Suggestions
End quote

Check out the following URLs for a more complete discussion.

http://www.granite.ab.ca/access/performancefaq.htm

http://support.microsoft.com/?id=209126


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Denise]

Thank you. I'm going to give the suggestions a try and see what happens.

Try including the lookup tables in the backend.

If the tables are not in the same database then you CANNOT set up
relationships between them. Beyond that take a look at Tony Toews' site

Quote from Tony Toews
The three most common performance problems in Access 2000 are:
- LDB locking which a persistent recordset connection fixes
- sub datasheet Name property set to [Auto] should be [None]
- Track name AutoCorrect should be off
(Tools: Options: General Uncheck all name AutoCorrect features)
Other reasons are
- New format of Access 2000 MDB
- Place backend MDB on the root of the network share rather than several
folders down
- Shorten the name of the backend MDB
- Miscellaneous Performance Suggestions
End quote

Check out the following URLs for a more complete discussion.

http://www.granite.ab.ca/access/performancefaq.htm

http://support.microsoft.com/?id=209126


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Any suggestions to speed up my dbs? When single user, it is fast.

The database has front-end (saved in folder A), back-end (saved in folder
B), and look-up tables (saved in folder C).

The back-end table fields are indexed.

A dummy table was created and is opened with a hidden form whenever the dbs
is opened.

Back-end tables have saved relationship. Should look-up tables be linked
and included in the relationship?

What can I do to improve the speed?

I'm in the middle of developing another database and would like it to use
same look-up tables. It's also a multi-user dbs.

Thanks for your advice.

 

[Denise]

I assume the same applies when using Access 2007?

Try including the lookup tables in the backend.

If the tables are not in the same database then you CANNOT set up
relationships between them. Beyond that take a look at Tony Toews' site

Quote from Tony Toews
The three most common performance problems in Access 2000 are:
- LDB locking which a persistent recordset connection fixes
- sub datasheet Name property set to [Auto] should be [None]
- Track name AutoCorrect should be off
(Tools: Options: General Uncheck all name AutoCorrect features)
Other reasons are
- New format of Access 2000 MDB
- Place backend MDB on the root of the network share rather than several
folders down
- Shorten the name of the backend MDB
- Miscellaneous Performance Suggestions
End quote

Check out the following URLs for a more complete discussion.

http://www.granite.ab.ca/access/performancefaq.htm

http://support.microsoft.com/?id=209126


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Any suggestions to speed up my dbs? When single user, it is fast.

The database has front-end (saved in folder A), back-end (saved in folder
B), and look-up tables (saved in folder C).

The back-end table fields are indexed.

A dummy table was created and is opened with a hidden form whenever the dbs
is opened.

Back-end tables have saved relationship. Should look-up tables be linked
and included in the relationship?

What can I do to improve the speed?

I'm in the middle of developing another database and would like it to use
same look-up tables. It's also a multi-user dbs.

Thanks for your advice.

 

[Tony Toews [MVP]]

I assume the same applies when using Access 2007?

Yes.

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's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

Any suggestions to speed up my dbs? When single user, it is fast.

A dummy table was created and is opened with a hidden form whenever the dbs
is opened.

This puzzles me. The standard performance improvement when single
user is fast and second user in is slow is to create a hidden form
linked to a table on the backend. It doesn't need to be a dummy
table but a table with only a few records. I always have a global
options table with only one record so that works for me.

Stupid question. Is that dummy table in the backend and there is a
linked table in the FE?

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's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Denise]

Thank you for looking at my problem, Tony.

The dummy table with 1 record is in back end and is linked in front end.

--This database is used to manage a functional area in human resources
office. The main entry form has with several subforms tabs on main form.
--There are a fair number of lookup tables to ensure data entry consistency
and quality.
--Files are in different folders to safeguard backend and look-up tables.

 

[Tony Toews [MVP]]

The dummy table with 1 record is in back end and is linked in front end.

Good. I had to ask. <smile>

Still not sure why performance would be slow for the second user in
though.

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's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Denise]

I am not using modules or VBA, if that helps.

This puzzles me. The standard performance improvement when single
user is fast and second user in is slow is to create a hidden form
linked to a table on the backend. It doesn't need to be a dummy
table but a table with only a few records. I always have a global
options table with only one record so that works for me.

Stupid question. Is that dummy table in the backend and there is a
linked table in the FE?

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's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Denise]

I implemented your 3 tips: dummy table; subdatasheet name set to none; and
track name autocorrect to off. I'll find out if it makes a difference in the
morning when everyone shows up to work. Thank you once again.

 

[Tony Toews [MVP]]

I am not using modules or VBA, if that helps.

No, that shouldn't matter.

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's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Denise]

Should these 3 changes be made in FE, BE and look-up ACCDB files? Does it
make a difference?
Thanks.

Try including the lookup tables in the backend.

If the tables are not in the same database then you CANNOT set up
relationships between them. Beyond that take a look at Tony Toews' site

Quote from Tony Toews
The three most common performance problems in Access 2000 are:
- LDB locking which a persistent recordset connection fixes
- sub datasheet Name property set to [Auto] should be [None]
- Track name AutoCorrect should be off
(Tools: Options: General Uncheck all name AutoCorrect features)
Other reasons are
- New format of Access 2000 MDB
- Place backend MDB on the root of the network share rather than several
folders down
- Shorten the name of the backend MDB
- Miscellaneous Performance Suggestions
End quote

Check out the following URLs for a more complete discussion.

http://www.granite.ab.ca/access/performancefaq.htm

http://support.microsoft.com/?id=209126


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Any suggestions to speed up my dbs? When single user, it is fast.

The database has front-end (saved in folder A), back-end (saved in folder
B), and look-up tables (saved in folder C).

The back-end table fields are indexed.

A dummy table was created and is opened with a hidden form whenever the dbs
is opened.

Back-end tables have saved relationship. Should look-up tables be linked
and included in the relationship?

What can I do to improve the speed?

I'm in the middle of developing another database and would like it to use
same look-up tables. It's also a multi-user dbs.

Thanks for your advice.

 

[Tony Toews [MVP]]

Should these 3 changes be made in FE, BE and look-up ACCDB files? Does it
make a difference?

The LDB locking must be done in the FE. The Track name AutoCorrect
should be off must be done in the BE.

Why do you have lookup tables in a different accdb file?

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's Microsoft Access Blog - http://msmvps.com/blogs/access/