Access Performance and SQL server
Posted on 2014-01-25
I developed an app in Access 2003 for a client several years ago.
Over the years the company has grown and the number of concurrent users increased to above 20 and sometimes more.
The backend database is an MDB. The size of the MDB is only about 250MB so nowhere near the Access limit. I have many clients with backend MDB's larger that this.
System response is becoming an issue, especially with certain forms. The client has very state of the art hardware and networking.
The client is asking me to make it better from the application side. My first thought is to move to a SQL Server. I have heard and read many times that the MDB is not designed to support concurrent usage by this many users. I have also read that SQL Server is a much better solution as the number of users with concurrent access grow.
I'm sure the client is open to this solution but before proposing it I want to make sure my recommendation is valid.
The last thing I want, is to propose the solution, have them go thru the time and expense to implement it and end up with no improvement.
So I'm just looking for experiences of any EEer's that have gone thru a similar transition and hearing what kind of performance improvements were realized, or not.
We also plan to transition the application to Access 2010 at some point. However, I don't see that providing any performance benefit. Am I correct about that.