I recently split my DB, back end on server on front end on each machine. However when more than one person starts updating the performance all but stops. system says "updating tables" and takes forever. If only one person is on everything works fine. I put both the front end and the back end on the server and set up each machine with shortcuts the front end on the server and know everybody can access fine. I know this set up insn't best but its the only way i can get it to run. Any help would much appreciated.
Slow to No performance on split DB
- Thread starter kjh
- Start date
RuralGuy
AWF VIP
- Local time
- Today, 12:54
- Joined
- Jul 2, 2005
- Messages
- 13,825
Have you already examined the elements mentioned in this link: http://www.granite.ab.ca/access/performancefaq.htm
shadow9449
Registered User.
- Local time
- Today, 14:54
- Joined
- Mar 5, 2004
- Messages
- 1,037
Some people think that the reason for splitting a database is to help the speed. The true reason is for performance and avoiding corruption to the data and serious damage to the application in a multi-user environment, but the speed actually can be reduced by splitting.
There are a few places to start looking when the database is slow:
- Particularly in a split environment, you need a persistent connection to the back end. What I do for this is to simply create a table with just one record in the database and create a form that is bound to that table, and then open the form in hidden mode when the database launches. You'll be amazed what an improvement this will give you if you haven't done so yet.
There are other ways to do this simple approach works for me.
- are you sure that name autocorrect is switched off? It's a total of one click (check the documentation for your version of Access if you're not sure how to disable it) and again, you will see a vast improvement.
- The next thing that may improve speed is by setting datasubsheets in your tables to None. Particularly in Access 2007 having it set at Auto (which is the default) you can see degradation in speed.
Why does Microsoft release Access with the defaults giving you the most poor choices and then requiring you to go through the frustration to discover these secrets?
I wish I knew...
If these have been done already and you still don't have a decent speed, then you're going to have to do some work. You need to review your table structure (including proper normalization and relationships), indexing, form design (i.e. not to load more data than necessary. Example, subforms on tab controls that load all the data will really slow you down) and possibly a different LAN settings that may need to be tweaked and a lot of trial and error before you're happy.
Please let us know if this helps or if you need more guidance.
SHADOW
There are a few places to start looking when the database is slow:
- Particularly in a split environment, you need a persistent connection to the back end. What I do for this is to simply create a table with just one record in the database and create a form that is bound to that table, and then open the form in hidden mode when the database launches. You'll be amazed what an improvement this will give you if you haven't done so yet.
There are other ways to do this simple approach works for me.
- are you sure that name autocorrect is switched off? It's a total of one click (check the documentation for your version of Access if you're not sure how to disable it) and again, you will see a vast improvement.
- The next thing that may improve speed is by setting datasubsheets in your tables to None. Particularly in Access 2007 having it set at Auto (which is the default) you can see degradation in speed.
Why does Microsoft release Access with the defaults giving you the most poor choices and then requiring you to go through the frustration to discover these secrets?
I wish I knew...
If these have been done already and you still don't have a decent speed, then you're going to have to do some work. You need to review your table structure (including proper normalization and relationships), indexing, form design (i.e. not to load more data than necessary. Example, subforms on tab controls that load all the data will really slow you down) and possibly a different LAN settings that may need to be tweaked and a lot of trial and error before you're happy.
Please let us know if this helps or if you need more guidance.
SHADOW
shadow9449
Registered User.
- Local time
- Today, 14:54
- Joined
- Mar 5, 2004
- Messages
- 1,037
Similar threads
