Avatar of COACHMAN99
COACHMAN99
 asked on

ms access performance and SSD

Hi Folks.
I need to increase an MS Access database performance. (10 concurrent and 10 other users)
The BE (200Mb) is on a shared drive on a newish virtual server (sharing MS Exchange)
Assuming all the normal database optimizations are done, two questions:
1. will a dedicated server improve things much?
2. will an SSD (D: drive) on the new server (just for the Access BE) improve things (much) more (than a 10,000 rpm SATA drive) on this new box.

I realize the existing server specs are vague, but all expert comment and personal experiences are welcome.
thanks
Microsoft AccessServer Hardware

Avatar of undefined
Last Comment
PatHartman

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
PatHartman

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Jim Dettman (EE MVE)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
COACHMAN99

ASKER
Thanks Pat and Jim; valuable input.
Jim, 2 questions:
1. what does 'Modified all queries so they recost since the DB has grown?' mean?
2. Is the best way to implement 'a persistent connection to the BE' via a bound field on the main form to a 'dummy' BE table?

thanks
PatHartman

1. When you create a query and save it, Access calculates an execution plan for how to perform the query.  The best execution plan could change over time as table row counts change and indexes are added.  So, depending on how rapidly your BE grows or what design changes you make to the BE, you might want to consider compacting the FE periodically to force it to recalculate the execution plans.
2. I use my login form to do this.  The form is bound to the user table and when the user logs in, I open the menu but I don't close the login form, I just set its .visible property to False.
Your help has saved me hundreds of hours of internet surfing.
fblack61