We have had a consultant install a database for a client that is based on Access 2007 with a front end (on each workstation) and I guess the database back end (on the file server, it is a server 2003 domain controller with very current hardware, memory etc…).
When we launch the database from the workstation we get our typical log in screen, when a user logs in and there is only one user on the database interface the whole thing flies really quickly, keystrokes are very very quick etc.
As soon as another user logs into the database interface or program, everything crawls to a slow latent halt. Keystrokes in field boxes take forever to appear, for example if you type in the word "find" the f would appear then about 10 seconds later the "i" would appear, then 10 seconds later "n" then "d".
The current company that sold the database to us is suggesting a trial and error approach and I am not interested in hacking the registry on the server just to see if it makes a difference.
I am in desperate need of help as I don't have time or interest in databases, please all suggestions are welcome at this point.
Just to note, the network has no issues from cabling to switches to other connections. Using any of the workstations for file server browsing and opening files, email, internet etc all work extremely quickly. The only thing that stops working right is the database.
Best Answer
Welcome to Microsoft Access-based databases, and shared-file database "technology" in general. Thankfully, the problems today mainly relate to speed (whereas, with prior Windows OS versions out-right data corruption could occur).
You're seeing the immense slowdown because the server has to be a lot more careful about granting locks to clients when more than one client accesses the same database file. When only a single client is accessing a file the server can allow the client to obtain "opportunistic locks" and cache more of the data locally. Multiple clients makes the situation much more stringent for the server computer because consistency amongst the clients with respect to the apparent contents of the file must be maintained.
You may be able to gain some incremental performance by playing around with settings in the "Server Service" (LanManServer) on the server computer, but you're never going to see the same performance with multiple users as you would with a single-user.
If you really want to see this perform well I'd recommend evaluating the possibility of "upsizing" the back-end database into SQL Express or some other true client/server database engine. If the "back end" is just a collection of tables that the "front end" is using "link tables" functionality to access then it's a pretty good candidate for moving to an SQL Express instance. The consultant wouldn't have to do much more than use the SQL Server Migration Asstant for Microsoft Access.
Besides being client / server and, thus, much faster, SQL Express is also going to get you a nice way to take online backups (while users are actively using the database). Users can "forget" to close the application and you'll still get backups, whereas, with Access, the back-end file will be "locked" and unavailable for backup.
The list of advantages you'd get from using SQL Server / SQL Express on the back-end are too long to lay out here, though I suspect that your consultant isn't familiar with them since they didn't bother to use SQL Server / SQL Express to begin with. You may want to consider getting another consultant if you decide to expand / enhance the application later on.